Export to Excel with Formatting drops leading zeroes (0)

jlavallet

Thanks for a great product. Unfortunately we've hit upon an issue. When we Export to Excel with Formatting, the export drops any leading zeroes (0) on columns that contain numeric data with leading zeroes. This is an Excel behavior that is generally resolved by explicitly setting the column format to text. Unfortunately this has to be done prior to the export because the leading zeroes are not retained in the exported data. Linqpad could detect this issue and handle it during export (preferred) or allow us to specify the exported column type prior to export.

JoeAlbahari

LINQPad exports to Excel via HTML, which is limited in how it handles data typing. A workaround might be to try Util.WriteCsv, and then Process.Start on the resulting file.

jlavallet

Joe, the HTML output includes the leading zeroes. How do you invoke Excel? Process.Start on a temporary HTML file? Or do you use a library of some kind? Is there no way to pass the column configuration to Excel?

JoeAlbahari

LINQPad includes the leading zeros, but it seems Excel will drop them if it decides the column is numeric. There's no way to for LINQPad to tell Excel to do otherwise if HTML is used as the transfer medium, AFAIK.

I take is CSV is not an option?

Bayi

Excel uses a default formatting for CSV columns depending on the content. So if you have 001 in a csv, excel will automatically turn it to 1.

The only way to keep the leading zeros in excel from a csv file is by changing the extension of the csv file to .txt, then just open excel, click on open, select the txt file, and you'll see the Text Import Wizard. Select your csv format (separated by commas), then just make sure you select "Text" as the format.

And that's it, now you can export that previous csv data to any other while keeping the leading zeros.