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

Prefix with '

Answer 2

Prefixing the contents of the cell with ' forces Excel to see it as text instead of a number. The ' won't be displayed in Excel.

Answer 3

I think the way to do this would be to format the source excel file such that the column is formatted as Text instead of General. Select the entire column and right click and select format cells, select text from the list of options.

I think that would explicitly define that the column content is text and should be treated as such.

Let me know if that works.

Answer 4

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 5

Saving the file as a tab delimited text file has also worked well.

---old Unfortunately, we can't rely on the columns of the excel doc to stay in a particular format as the users will be pasting data into it regularly. I don't want the app to crash if we're relying on a certain datatype for a column.

prefixing with ' would work, is there a reasonable way to do that programatically once the data already exists in the excel doc?

Answer 6

I got the same problem. Yes,Its working. Thanks for your help

Answer 7

There is a registry hack that can force Excel to read more than the first 8 rows when reading a column to determine the type:

Change

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

To be 0 to read all rows, or another number to set it to that number of rows.

Not that this will have a slighht performance hit.