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.
varexcelQuery=newExcelQueryFactory(@"C:\Data\MySpreadsheet.xlsx");varinsertStatementDictionary=newDictionary<string,string>();varallStatements=newStringBuilder();foreach(varworksheetNameinexcelQuery.GetWorksheetNames().Where(name=>name.EndsWith(" Data"))){vardataRows=fromdataRowinexcelQuery.WorksheetRangeNoHeader("N8","W8",worksheetName)selectdataRow;varkey="-- "+worksheetName;foreach(vardataRowindataRows){varcellList=dataRow.ToList();varinsertStatements=fromcellincellListletstatement=string.Format("{0}",cell.Value).Replace("N/A",string.Empty).Trim()wherestatement.Length>0letwrappedStatement=statement// wrap SELECT column list with one column per line
.Replace(" SELECT [","\nSELECT\n\t[").Replace(", [","\n\t, [")selectwrappedStatement;varcombinedInsertStatements="\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));varscript=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
INSERTINTOSales.CustomerTypes(CustomerTypeId,CustomerType)SELECT[CustomerTypeId]=1,[CustomerType]='Enterprise';INSERTINTOSales.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.