Export Large Dataset to Excel
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?
Comments
-
The best way to accomplish this is with Util.ToCsvString and File.WriteAllText.
-
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));
-
Hmm, I still don't get all the rows in the output file:
List<> (First 1000 items of 136003) -
Call Util.ToCsvString instead of Util.ToHtmlString.
-
, indeed; thanks -
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?
-
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");
-
i was thinking there is no bom for utf-8 since we have one byte !??
-
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.
