Jeff Miller's Big Idea Creativity seeking form

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: