Export Large Dataset to Excel

famadorian

I have a query like this:

var query1 = (from memberprograms in MemberPrograms
select new
{
Insertdate=memberprograms.Insertdate
}).ToList().Dump();

, which dumps more rows than Linqpad can display, so when I export, I don't get all the rows.

Can I write the result of this query directly to an Excel file?

JoeAlbahari

The best way to accomplish this is with Util.ToCsvString and File.WriteAllText.

famadorian

great; did this: Thanks;)

var query1 = (from memberprograms in MemberPrograms
select new
{
Insertdate=memberprograms.Insertdate
}).ToList().Dump();

File.WriteAllText (@c:\tmp\foo.html, Util.ToHtmlString (query1));

famadorian

Hmm, I still don't get all the rows in the output file:
List<> (First 1000 items of 136003)

JoeAlbahari

Call Util.ToCsvString instead of Util.ToHtmlString.

famadorian

, indeed; thanks

famadorian

Seems to be an issue with locale now, as I don't see correct Norwegian characters when opening the CSV in Excel. Any way to export as UTF-8?

JoeAlbahari

File.WriteAllText in fact uses UTF-8 by default. The problem as I understand is that Excel doesn't recognize UTF-8 without byte-order marks. You have to write the file with BOM (either in UTF-8 or UTF-16):

File.WriteAllText (@"c:\temp\test.csv", Util.ToCsvString (...), Encoding.Unicode);

To make this easier, LINQPad provides a shortcut that writes CSV files with a BOM:

Util.WriteCsv (..., @"c:\temp\test.csv");

lionel06

i was thinking there is no bom for utf-8 since we have one byte !??

JoeAlbahari

You'd think so, but the reality is more subtle. The UTF-8 "BOM" doesn't indicate byte order - it's a fixed 3-byte header that signals that the file is UTF-8. Most programs don't require it because they treat UTF-8 is the default. Excel is the exception.