Reputation: 5654

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

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?

Upvotes: 8

Answers (8)

zoobiezoobie

Reputation: 59

Add "\t" before your string. It'll make the string seem in a new tab.

Upvotes: 0

Rajans

Reputation: 1

Sending value 00022556 as '=" 00022556"' from Sql server is excellent way to handle leading zero problem

Upvotes: 0

TheEmirOfGroofunkistan

Reputation: 5654

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?

Upvotes: 0

cjk

Reputation: 46445

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.

Upvotes: 1

palehorse

Reputation: 27496

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.

Upvotes: 8

Krantz

Reputation: 6283

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.

Upvotes: 0