Free Software

Using LINQPad with LINQ-to-Excel

I've been really enjoying the free LINQPad app
http://www.linqpad.net/

...in conjunction with LINQ-to-Excel
http://code.google.com/p/linqtoexcel/

Here's a video tutorial on how to use LINQ-to-Excel:

LINQ-to-Excel took some figuring out, but it enables compelling new LINQ integration scenarios like SQL scripts built from spreadsheets. I used formulas on multiple worksheets to build INSERT statements from tabular data and used LINQPad w/ LINQ-to-Excel to extract them into a master script.

LINQPad script:

var excelQuery = new ExcelQueryFactory(@"C:\Data\MySpreadsheet.xlsx");

var insertStatementDictionary = new Dictionary();
var allStatements = new StringBuilder();

foreach(var worksheetName in excelQuery.GetWorksheetNames().Where (name => name.EndsWith(" Data")))
{
	var dataRows =
		from dataRow in excelQuery.WorksheetRangeNoHeader("N8", "W8", worksheetName)
		select dataRow;
	
	var key = "-- " + worksheetName;

	foreach(var dataRow in dataRows)
	{
		var cellList = dataRow.ToList();
		
		var insertStatements =
			from cell in cellList
			let statement = string.Format("{0}", cell.Value).Replace("N/A", string.Empty).Trim()
			where statement.Length > 0
			let wrappedStatement = statement
				// wrap SELECT column list with one column per line
				.Replace(" SELECT [", "\nSELECT\n\t[").Replace(", [", "\n\t, [")
			select wrappedStatement;
		
		var combinedInsertStatements =
			"\n\n" + key + "\n" + 
			string.Join("\n\n", insertStatements.ToArray());

		insertStatementDictionary.Add(key, combinedInsertStatements);
	}
}

// create an alphabetically-sorted script using the Dictionary and a StringBuilder
insertStatementDictionary.OrderBy(sd => sd.Key).Select(kvp => kvp.Value).ToList().ForEach(st => allStatements.Append(st));
var script = allStatements.ToString();
script.Dump();
// copy the script to the clipboard to avoid whitespace "standardization" of the Dump() output
Clipboard.SetText(script);

Result:

-- Customer Type Data
INSERT INTO Sales.CustomerTypes (CustomerTypeId, CustomerType)
SELECT
  [CustomerTypeId] = 1
  , [CustomerType] = 'Enterprise';

INSERT INTO Sales.CustomerTypes (CustomerTypeId, CustomerType)
SELECT
  [CustomerTypeId] = 2
  , [CustomerType] = 'Personal';

Both tools are free.

If you want "Intellisense" (a.k.a., "auto-complete") in the LINQPad tool, you have to pay for it (~$40, sometimes on sale for <$30). For that price you can activate LINQPad auto-complete on 3 physical machines and 3 virtual machines. I paid for the auto-complete license, and it has been worth it.

Plus, sample code from two popular LINQ books (C# 4.0 in a Nutshell and LINQ in Action) is directly downloadable into the LINQPad tool and is extremely educational.

You can find both books on Amazon.com:

Useful Software Tools

Here are some (mostly great) tools I use. I'm happy to recommend them here. Some are free and others cost money, but in general, even the non-free tools are worth it--otherwise they would not be listed here.

Instant Messaging

Text Editors

My favorite text editor for Windows:

  • UltraEdit (costs ~$40, but definitely worth it!)

A free alternative (but not as good) to UltraEdit when you can't afford/acquire a license for it:

File Tools

  • WinMerge -- compare folder trees and individual files. I love this tool! It has saved me lots of time when comparing book manuscript files (plain text, not binary) and source code. It's great for reconciling two different folder structures or simply understanding the differences.
  • Junction -- create symbolic links to other folders in Windows NTFS-formatted disks
Syndicate content