CODEWITHSUNDEEP
vb.net
Hozrot Ali
Hozrot Ali

Reputation: 3

Leading zeros not retained reading csv with ACE OleDb

Leading zeros not retaining while reading comma separated value(csv) file by Microsoft ACE Ole Db means if col value is 0000123456, i am getting only 123456 only, while reading programmatically in vb.net. i don't want to put any condition while preparing the comma separated value(csv) file i.e. used of apostrophe before the zeros etc. Data structure is like below in comma separated value(csv) file

Name,ID
John,0001234
moon,0001235

but i am getting id as 1234 , 1235 but i want with zeros using Microsoft ACE Ole Db

Please guys any idea.Thanks in advance

Upvotes: 0

Views: 603

Answers (1)

Steve
Steve

Reputation: 216243

The Text File driver used by OleDb to read CSV files is unable to accurately determine the datatype of the columns. In your case, your second column is misunderstood to be a numeric column because it contains only digits, thus the leading zero are removed.

You can give a strong hint to OleDb creating a file called SCHEMA.INI that explains what is the content of the file.

In your case you could create one in the same folder of the file (I assume temp.csv for this example) and write these lines: 

[temp.csv]
Format=CSVDelimited
ColNameHeader=True
Col1=Name Text
Col2=ID Text
DecimalSymbol=.

Notice that I need to specify the DecimalSymbol being a point because in my locale the comma is used as separator between the decimal and the integer part of a number (thus we use CSV files separated by semicolons)

Upvotes: 1

Related Questions