El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for 24 febrero 2011

10 Useful Google Spreadsheet Formulas You Must Know

Posted by kiquenet en 24 febrero 2011

Google Spreadsheet is a powerful and free tool to manage complex spreadsheets. This tutorial illustrates ten useful formulas you must know to help you simplify the way you work in Google Spreadsheet.

Vertical Look Up

This formula searches for value in the left-most column of array and returns the value in the same row based on the index.

=vlookup(search_criterion, array, index, sort_order)

For example, if you want to find the population of the value contained in the cell C2, from all values contained in the range A2:B4, copy and paste the following formula into the cell D2:
=vlookup(C2;A2:B4;2;false)

Filter

This formula returns a filtered version of the given source array.

=filter(range; condition1, condition2,...)

In this example I filtered all values contained in the column A (range A2:A12) greater than 37.

Copy and paste the following formula into the cell B2:
=filter(A2:A12;A2:A12>37)

You can also use multiple conditions to filter a source array:
=filter(A2:A12;A2:A12>37; A2:A12<60)

In this case the formula returns all values contained in the range A2:A12 greater than 37 AND less than 60.

Concatenate

This function concatenates several text strings, contained in different cells, into one string.

=concatenate(text1, text2, text3,...)

If you want to concatenate all values contained in the cells A2, A3, A4 into one string, copy and paste this formula into the cell B2:
=concatenate(A2,A3,A4)

If you want to separate each single value with a space (cell B3), use this formula:
=concatenate(A2," ", A3," ", A4)

Find

This formula looks for a string of text within another string and returns an integer.

=find(find_text, text, position)

For example, if you want to find the "space" within the string contained in the cell C2 (which contains this text: Jack Bauer), copy and paste the following formula into the cell B2:
=find(" ", A2, 1)

This formula returns "5" that means the "space" is found within the source string after 4 characters.

Left and Right

Left extracts a substring from a string, starting from the left-most character.

=left(text, number)

Right extracts a substring from a string, starting from the right-most character.

=right(text, number)

If you want to extract the name "Jack" from the cell A2, copy and paste the following formula into the cell B2:
=left(A2;4)

If you want to extract the surname "Bauer" from the cell A3, copy and paste the following formula into the cell B3:
=right(A3;5)

You can also use the following formula to extract dynamically the name from an array of multiple values (Jack Bauer, Gregory House, Christian Troy, …):
=left(A2; find(" ", A2, 1)-1)

Split

This formula splits text based on the given delimiter, putting each section into a separate column in the row.

=split(string, delimiter)

For example, if you want to split the values contained in the column A, using the delimiter " " (space), copy the following formula into the column B (the column C will be populated automatically):
=split(A2," ")

Substitute

This formula substitutes new text for old text in a string.

=substitute(text, search_text, new text, occurrence)

If you want to substitute "Jack" with "Mike" from the text contained in the cell A2, copy and paste this formula into the cell B2:
=substitute(A2, "Jack", "Mike",1)

Occurrence (optional, in this case "1") indicates how many occurrences of the search text are to be replaced.

Unique

This formula returns only the unique values in the source array, discarding duplicates.

=unique(array)

If you want to obtain an array of unique values from the values contained in the column A, copy and paste the following formula into the cell B2:
=unique(A2;A9)

As you can see, this formula returns only unique values in the column A, discarding all duplicates.

Google Finance

This function returns market information from Google Finance related to a specific company.

=GoogleFinance(symbol, attribute)

For example, if you want to know the Google stock price, copy and paste the following formula into the cell B2: =GoogleFinance(A2,"price")

You can use the same formula for the other companies in this example (NVidia, Intel, Cisco,…). Other attributes are volume, earning per share, opening price and so on (take a look at the official documentation for a full list).

GoogleLookup

This formula attempts to find the values for straightforward facts about specific things.

=GoogleLookup(entity, attribute)

For example if you want to find the countries of cities listed in the column A, copy and paste the following formula into the column B:

=googleLookup(A2;"country")

You can access some types of entities such as countries and territories (population, largest city), rivers (origin, length), actors and musicians (date of birth, nationality), planets and so on. For a full list of popular entities, take a look at the official documentation.

Antonio Luppetti
http://woorkup.com/2010/02/19/10-useful-google-spreadsheet-formulas-you-must-know/

Posted in Google | Etiquetado: , | Leave a Comment »

FUSLOGVW, Assembly Binding Log Viewer

Posted by kiquenet en 16 febrero 2011

ASSEMBLY PROBING, FUSION AND FUSLOGVW IN 5 MINUTES

Introduction

A couple of days ago I received the following mail:

Hi Bart,
I’m trying to add an install functionally that will just basically copy the whole CD content to a local C drive folder. Executing the launcher.exe in that local folder works fine but if I create a desktop shortcut that targets the launcher.exe produced a file not found error for cassinilight.dll. I was wondering if you have an idea in what location was the application looking for cassinilight.dll.  Any help is highly appreciated.

The short answer is really short: probing. Probing is the technique employed by the CLR’s assembly loader to find a dependent assembly based on searching for it in various folders. Strongly named assemblies (those you signed using an sn.exe generated public/private key pair) are being searched for in the GAC (and paths specified in codeBase configuration elements, see further; and the "standard locations"). Weakly named assemblies are also probed, by looking in the same folder as the application and in subfolders named after the dependent assemblies themselves. However, sometimes it’s not that easy and you really want to see what’s going on (a common problem is an assembly being loaded from the GAC while you have recompiled it to your bin\Debug folder in Visual Studio which leads to unexpected debugging results).

In this post, I’m showing you how to make a jumpstart with Fusion, assembly probing and the "Fusion log viewer" aka fuslogvw.exe. For the record, Fusion is the codename of the assembly loader component of the CLR which (you can still see this in the SSCLI source tree under sscli20\clr\src\fusion).

A faulty application

Right, let’s create a plain simple demo to illustrate the principle. It’s so simple it fits in one console window using "copy con" file generation:

The code is:

foo.cs (compile using csc /t:library foo.cs)
public class Foo
{
}

bar.cs (compile using csc /t:library /r:foo.dll bar.cs)
class Bar
{
   public static void Main()
   {
      new Foo();
   }
}

Now you should have two assemblies: foo.dll and bar.exe. Run the application, it should just run (although it doesn’t do anything useful, it doesn’t produce any errors either).

Time has come to make the app faulty. Create a subfolder called "oops" and move the foo.dll file to it. Now bar.exe will fail:

And guess what, you shouldn’t even read my blog to find out what’s wrong. The runtime is so kind to tell you to enable Fusion:

To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

Setting up Fusion

Redundant info maybe, but here are two ways to enable Fusion.

For modern developers – PowerShell

PS C:\Users\Bart> new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Fusion -n EnableLog -t Dword -va 1

PSPath       : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\               Microsoft\Fusion
PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\               Microsoft
PSChildName  : Fusion
PSDrive      : HKLM
PSProvider   : Microsoft.PowerShell.Core\Registry
EnableLog    : 1

For command-line freaks – reg.exe

C:\temp>reg add HKLM\Software\Microsoft\Fusion /v EnableLog /t REG_DWORD /d 1
The operation completed successfully.

For UI lovers – regedit.exe

You should be able to find out yourself :-).

Analyzing the problem

Run bar.exe again after you’ve enabled Fusion. This time you get a realm of information:

The most interesting portion is the last part:

LOG: Attempting download of new URL file:///C:/temp/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/foo/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/foo.EXE.
LOG: Attempting download of new URL file:///C:/temp/foo/foo.EXE.

These are the locations where the system attempted to find the referenced assembly "foo". The "oops" folder isn’t there obviously, so the probing operation fails.

Now run fuslogvw.exe and you should see the following log information:

If you double-click on the last line, you’ll see the following in a browser window:

*** Assembly Binder Log Entry  (12/10/2006 @ 12:16:56) ***
The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.
Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable  C:\temp\bar.exe
— A detailed error log follows.
=== Pre-bind state information ===
LOG: User = Bart-PC\Bart
LOG: DisplayName = foo, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null
(Fully-specified)
LOG: Appbase = file:///C:/temp/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = bar.exe
Calling assembly : bar, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null.
===
LOG: This bind starts in default load context.
LOG: No application configuration file found.
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).
LOG: Attempting download of new URL file:///C:/temp/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/foo/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/foo.EXE.
LOG: Attempting download of new URL file:///C:/temp/foo/foo.EXE.
LOG: All probing URLs attempted and failed.

Since there’s no application configuration file to specify probing locations, the default probing process is used, effectively looking in the same folder as the application (seeAppbase) and in a subfolder with the assembly name (without the extension, i.e. Appbase\AssemblyName, in our example c:\temp\foo). All logs end up in the IE temporary files cache, but you can override this in fuslogvw (or by setting registry entries):

where "c:\temp" is set in the LogPath REG_SZ value in the Fusion registry key. The logging info will end up in a subfolder called "NativeImage":

Setting a custom probing path

You can drive the probing mechanism by specifying probing paths in a configuration file. So create the following bar.exe.config file in the bar.exe folder (c:\temp on my system):

<?xml version="1.0"?>
<configuration>
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <probing privatePath="oops" />
      </assemblyBinding>
   </runtime>
</configuration>

Now bar.exe works fine again:

If you configure Fusion to log all binds to disk, like this:

you’ll see log entries appear after re-running the bar.exe applicatin again:

This time with the following logging info:

*** Assembly Binder Log Entry  (12/10/2006 @ 13:01:05) ***
The operation was successful.
Bind result: hr = 0x0. The operation completed successfully.
Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable  C:\temp\bar.exe
— A detailed error log follows.
=== Pre-bind state information ===
LOG: User = Bart-PC\Bart
LOG: DisplayName = foo, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null
(Fully-specified)
LOG: Appbase = file:///C:/temp/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = bar.exe
Calling assembly : bar, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null.
===
LOG: This bind starts in default load context.
LOG: Private path hint found in configuration file: oops.
LOG: Using application configuration file: C:\temp\bar.exe.config
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).
LOG: Attempting download of new URL file:///C:/temp/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/foo/foo.DLL.
LOG: Attempting download of new URL file:///C:/temp/oops/foo.DLL.

LOG: Assembly download was successful. Attempting setup of file: C:\temp\oops\foo.dll
LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: foo, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null
LOG: Binding succeeds. Returns assembly from C:\temp\oops\foo.dll.
LOG: Assembly is loaded in default load context.

As you can see, the oops path is being probed and the assembly is found thanks to our "private path hint" in the configuration file.

Conclusion

Once the FileNotFoundExceptin might have been your worst nightmare (actually MissingMethodException should deserve that spot) but when things get bad, Fusion comes to the rescue. Happy probing!

Enable Fusion Assembly Binding Logging

The WRN: Assembly binding logging is turned off message is annoying but assembly binding logging can be extremely helpful when you need to know how .NET is (or isn’t) locating the assemblies you’ve referenced. The message is annoying because, if you’re like me, you never have logging enabled and the registry key cited is a bit unusual:

To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

What does the exclamation mark mean? Presumably EnableLog is a DWORD in the Fusion key but it’s all a bit unclear for my liking. Once you’ve set this DWORD, you need to figure out what to do next and for many people that will likely involve firing up the Fusion Log Viewerfuslogvw.exe or the Windows/.NET SDK. But then what?

Gary Kindel was kind enough to post the following details in response to a related Stack Overflow question:

Add the following values to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion
DWORD ForceLog set value to 1
DWORD LogFailures set value to 1
DWORD LogResourceBinds set value to 1
String LogPath set value to folder for logs ie) C:\FusionLog\

Make sure you include the backslash after the folder name. I also found an IISRESET was necessary in a web context.

Since I wanted to enable this logging in an environment without Visual Studio or the Windows SDK installed, the above option was clean and lightweight. Log files were dumped to the expected location in .html format and it was then a case of locating the assembly I was interested in… and, oh yeah, fixing the problem 😉 The issue was also detailed in the ASP.NET error message returned by IIS.

Advanced geeks: because Fusion logging supposedly affects performance, you might want to create a reg file/batch script to toggle logging on and off. Here’s a starting point—put this into a file called EnableFusionLogging.reg and double-click to run it:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion]
"ForceLog"=dword:00000001
"LogFailures"=dword:00000001
"LogResourceBinds"=dword:00000001
"LogPath"="C:\\Temp\\FusionLog\\"

I actually call this from a batch script which also resets IIS and I have a mirror reg file that disables logging.

http://community.bartdesmet.net/blogs/bart/archive/2006/10/23/Assembly-probing_2C00_-Fusion-and-fuslogvw-in-5-minutes.aspx

http://blog.mediawhole.com/2010/08/enable-fusion-assembly-binding-logging.html

http://stackoverflow.com/questions/255669/how-to-enable-assembly-bind-failure-logging-fusion-in-net/1527249#1527249

Posted in .NET, Herramientas | Etiquetado: | Leave a Comment »

tf Command-Line Utility Tool in Team Foundation Server

Posted by kiquenet en 3 febrero 2011

Use the tf command line in scripting

Example for get:
cd C:\TFS\Arquitectura
%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\TF.exe get $/Arquitectura/Main /recursive

Example for checkout:
cd C:\TFS\Arquitectura
%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\TF.exe checkout $/Arquitectura/Main /recursive

Example for checkin:
cd C:\TFS\Arquitectura
%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\TF.exe checkin $/Arquitectura/Main /recursive

%ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Common7\IDE\TF.exe" get $/Project /recursive for VS 2010 on Win x64.

Referencia de tf.exe

Tf Command-Line Utility Commands
http://msdn.microsoft.com/en-us/library/z51z7zy0(v=VS.90).aspx

Ruta del comando:

%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\TF.exe

tf Command line utility provide many useful operations that we can perform with Team Foundation Server.

tf add – adds news folder and file from file system to TFS Source Control. Need to do check-in before these file can be visible.
tf add itemspec [/lock:none|checkin|checkout] [/type:filetype] [/noprompt] [/recursive]

tf branch – copies an item/s from one location to another inside TFS Source Control. Need to do check-in before these file can be visible.
tf branch olditem newitem [/version:versionspec] [/noget] [/lock] [/noprompt]

tf branches – displays the history of a branch which can be a file or a folder.
tf branches [/server:servername] itemspec [/version]

tf changeset – displays info about changesets such as comments, notes and etc…
tf changeset [/comment:comment|@commentfile] [/server:servername] [/notes:(“NoteFieldName”=”NoteFieldValue”|@notefile)] [/noprompt] [changesetnumber | /latest]

tf checkin – checks in pending changes to the TFS Source Control.
tf checkin [/author:author name] [/comment:("comment"|@comment file)] [/noprompt] [/notes:(“Note Name”=”note text”|@notefile)] [/override:reasonfile|@reason] [/recursive] [/saved] [/validate] [filespec]

tf checkout – checks out local files for edit.
tf checkout [/lock:(none|checkin|checkout)] [/recursive] [/type:encoding] itemspec

tf configure – updates Source Control Settings with modified Check-out settings; Check-in policies; Check-in notes
tf configure [path of team project] /server:servername

tf delete – removes files and folders from TFS
tf delete [/lock:(none|checkin|checkout)] [/recursive] itemspec

tf diff – displays differences between two files or a shelveset
tf diff[erence] itemspec [/version:versionspec] [/type:filetype] [/format:format [/ignorespace] [/ignoreeol] [/ignorecase] [/recursive] [/options]
tf diff[erence] itemspec itemspec2 [/type:filetype] [/format: format] [/ignorespace] [/ignoreeol] [/ignorecase] [/recursive] [/options]
tf diff[erence] [/shelveset:shelvesetname[;shelvesetowner]] shelveset_itemspec [/server: servername] [/type:filetype] [/format: format] [/ignorespace] [/ignoreeol] [/ignorecase] [/recursive] [/options]
tf diff[erence] /configure

tf dir – presents the contents of the TFS Source Control server
tf dir [/server:servername] itemspec [/version:versionspec] [/recursive] [/folders] [/deleted]

tf get – gets a read-only copy of a file from the TFS to the workspace and creates folders with the file on the disk.
tf get itemspec [/version:versionspec] [/all] [/overwrite] [/force] [/preview] [/recursive] [/noprompt]

tf help – shows help topics about TFS command.
tf help commandname

tf history – shows history of revisions for file/s and folders.
tf history [/server:servername] itemspec [/version:versionspec] [/stopafter:number] [/recursive] [/user:username] [/format:(brief|detailed)] [/slotmode] [/noprompt]

tf label – creates or removes a label from a version of a file or folder
tf label [/server:servername] labelname[@scope] [/owner:ownername] itemspec [/version:versionspec] [/comment:("comment"|@commentfile)] [/child:(replace|merge)] [/recursive]
tf label [/server:servername] /delete labelname[@scope] [/owner:ownername] itemspec [/version:versionspec] [/recursive]

tf labels – shows labels in the TFS
tf labels [/owner:ownername] [/format:(brief|detailed)] [/server:servername] [labelname]

tf lock – locks or unlocks a file or folder
tf lock itemspec /lock:(none|checkout|checkin) [/workspace:workspacename] [/server:servername] [/recursive]

tf merge – applies changes from one branch into another
tf merge [/recursive] [/force] [/candidate] [/discard] [/version:versionspec] [/lock:none|checkin|checkout] [/preview] [/baseless] [/nosummary] source destination

tf merges – shows detailed information about past merges between branches
tf merges [/server:servername] destination [/recursive]

tf properties – presents info about items under TFS Source Control
tf properties [/server:servername] [/recursive] itemspec [/version:versionspec] [/workspace]

tf rename – renames files or folder paths. Also used to move files or folders.
tf rename [/lock:(none|checkout|checkin)] olditem newitem

tf resolve – resolves conflicts between changed items
tf resolve [itemspec] [/auto:(AcceptMerge|AcceptTheirs|AcceptYours|OverwriteLocal|DeleteConflict|AcceptYoursRenameTheirs)] [/preview] [(/overridetype:overridetype | /converttotype:converttype)] [/recursive] [/newname:path]

tf shelve – stores or deletes pending changes with check-in notes, a comment, and work items.
tf shelve [/replace] [/comment:(@commentfile|"comment")] [shelvesetname[;owner] [/validate]
tf shelve [/move] [/replace] [/comment:(@commentfile|"comment")] [/recursive] shelvesetname[;owner] filespec [/validate]
tf shelve /delete [/server:servername] shelvesetname[;owner] [/validate]

tf shelvsets – shows info about a set of shelved changes inside TFS Source Control
tf shelvesets [/owner:ownername] [/format:(brief|detailed)] [/server:servername] shelvesetname

tf status – shows info about pending changes to items
tf status itemspec [/server:servername] ([/workspace:workspacename[;workspaceowner]] | [/shelveset:shelvesetname[;shelvesetowner]]) [/format:(brief|detailed)] [/recursive] [/user:(*|username)]

tf undelete – restores deleted items
tf undelete [/noget] [/lock:(none|checkin|checkout)] [/newname:name] [/recursive] itemspec[;deletionID]

tf undo – removes pending changes from a TFS workspace
tf undo [/workspace:workspacename[;workspaceowner]] [/server:servername] [/recursive] itemspec [/noprompt]

tf unlabel – removes an item from an existing label
tf unlabel [/server:servername] [/recursive] labelname itemspec

tf unshelve – restores shelved file revisions, check-in notes, comments, and work items.
tf unshelve [/move] [shelvesetname[;username]] itemspec [/recursive] [/noprompt]

tf view – retrieves a specific version of a file to a disk
tf view [/server:servername] [/console] [/recursive] [/shelveset:shelvesetname[;owner]] [/noprompt] itemspec [/version:versionspec]

tf workfold – creates, updates, or displays info about the mappings between your workspace and TFS Source Control
tf workfold localfolder
tf workfold [/workspace: workspacename]
tf workfold [/server:servername] [/workspace: workspacename] serverfolder
tf workfold [/map serverfolder localfolder] [/server:servername] [/workspace: workspacename] (serverfolder|localfolder)
tf workfold /unmap [/server:servername] [/workspace: workspacename] [/recursive] (serverfolder|localfolder)
tf workfold /cloak (Team Foundation source control serverfolder|localfolder) [/workspace: workspacename] [/s:servername]
tf workfold /decloak (serverfolder|localfolder) [/workspace: workspacename] [/s:servername]

tf workspace – creates, modifies and views properties and mappings
tf workspace /new [/noprompt] [/template:workspacename[;workspaceowner]] [/computer:computername] [/comment:(“comment”|@comment file)] [/s:servername] [workspacename[;workspaceowner]]
tf workspace /delete [/server:servername] workspacename[;workspaceowner]
tf workspace [/server:servername] [/comment:comment] [/newname:workspacename] [workspacename[;workspaceowner]]

tf workspaces – displays info about workspaces in the system
tf workspaces [/owner:ownername] [/computer:computername] [/server:servername] [/format:(brief|detailed)] [/updateUserName:oldUserName] [/updateComputerName:oldComputerName] workspacename
tf workspaces /remove:(*|workspace1[,workspace2,...]) /server:(*|server)

Posted in Scripts, TFS | Etiquetado: | Leave a Comment »