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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.