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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

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

var insertStatementDictionary = new Dictionary<string, string>();
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 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 (LINQ in Action) is directly downloadable into the LINQPad tool and is extremely educational.

You can find both books on Amazon.com: