zeroes ommitted in the ID column

Question

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?

boycoto · Answer

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 Ivaturi · Answer

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	o\myfile.xlsx; Extended Properties=\Excel 12.0 Xml;IMEX=1\; The IMEX=1 extended property tells Excel to treat intermixed data as text.

boycoto · Answer

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 Ivaturi · Answer

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.