zeroes ommitted in the ID column

boycoto

I have csv file that if I open it using EXCEL file, it ommitted the zeroes at the begginning of the text whereas if I am going to view using notepad, the ID is correct. For example, the value of the ID is 000102233, if I am going to view it using Excel it displays as 112233, which is also the value that my program gets if I extract it from the csv file and store it to database. How am I going to deal with it?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

boycoto14y ago
I did but it returns an error, 'Couldn't find installable ISAM'. So, I retain my connection but I append the IMEX. ;Extended Properties='text;IMEX=1; HDR=Yes;FMT=Delimited(,)' And still zeroes were vanished.
Akkiraju Ivaturi14y ago
I believe you need to set the option in your connection 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\";

The IMEX=1 extended property tells Excel to treat intermixed data as text.
boycoto14y ago
But then, I am retrieving the ID from file using OleDB, and it turns out the the zeroes of the ID in the dataset was already ommitted.
Akkiraju Ivaturi14y ago
Format the column in the Excel sheet where the IDs are stored as TEXT instead of numeric. If you are storing the values programmatically, put the value in a quote ''. Single quote is used as an escape character and it preserves all the values enclosed in quotes. This should solve your problem.