CODEWITHSUNDEEP
sqlsql-serversql-server-2005csvbulkinsert
Mayou
Mayou

Reputation: 8818

Leading zeros disappear when using bulk insert from file

I am using bulk insert to insert data from a csv file to a SQL table. One of the columns in the csv file is an "ID" columns: i.e. each cell in the column is an "ID number" that may have leading zeros. Example: 00117701, 00235499, etc.

The equivalent column in the SQL table is of varchar(255) type.

When I bulk insert the data into the table, the leading zeros in each element of the "ID" column disappear. In other words, 00117701 becomes 117701, etc.

Is this a column type problem? If not, what's the best way to overcome this problem?

Thanks!

Upvotes: 0

Views: 1205

Answers (1)

BWS
BWS

Reputation: 3836

not sure what is causing it to strip off the leading zeroes, but I had to 'fix' some data in the past and did something like this:

UPDATE <table> SET <field> = RIGHT('00000000'+cast(<field> as varchar(8)),8)

You may need to adjust it a bit for your purposes, but maybe you get the idea from it?

Upvotes: 1

Related Questions