How do you prevent leading zeros from being stripped when importing an excel doc using c#

Question

I'm able to connect to and read an excel file no problem. But when importing data such as zipcodes that have leading zeros, how do you prevent excel from guessing the datatype and in the process stripping out leading zeros?

Answer 1

I believe you have to set the option in your connect string to force textual import rather than auto-detecting it.

Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=c:\path\to\myfile.xlsx;
    Extended Properties=\"Excel 12.0 Xml;IMEX=1\";

Your milage may vary depending on the version you have installed. The IMEX=1 extended property tells Excel to treat intermixed data as text.

Answer 2

Prefix with '