Import CSV file in SQL keeping leading zero

Dmtr_Grms 331

Good evening, I want to import a CSV file in SQL using Bulk import. I have a field containing the following value 010010 and that field has to remain like that in database. I have done several test but all of them have for result 10010 ---> leading zero is missing. Could someone explain what I should do to avoid that type of problem? Thanks

As requested by @Yitzhak Khabinsky

FROM 'C:\Users\xxxxx\Downloads\Sviluppo C#\SQL_Dati per tests\ScenariosUploadCSV.csv' WITH ( CODEPAGE = '65001', FORMAT = 'CSV', FIELDQUOTE = '', FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', TABLOCK )

Yitzhak Khabinsky 25,106 Reputation points

2023-05-22T19:47:18.82+00:00

Are you using BULK INSERT ... T-SQL statement?

If yes, please add it to the original question.
PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

2023-05-23T06:06:06.8566667+00:00

Hi @Dmtr_Grms

As Erland said, it could be a matter of data type. You can check it out.
Dmtr_Grms 331 Reputation points

2023-05-23T10:09:44.63+00:00
Thanks to all. I found the solution last night.

Had to modify Column Type in Excel file in TEXT (by default Excel put General and considered my column as a numeric) before to save in CSV format.

Had to modify the destination field from int to string in .cs and DbContext, this even if manage a numeric field content (only combination of 0 and 1).
PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

2023-05-26T08:32:32.84+00:00

Hi @Dmtr_Grms

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
anitha 0 Reputation points

2023-08-23T18:44:08.4+00:00

I have a csv file and modified column type as text and saved. And destination db column also varchar data type. Even though the dataload omitting leading zero's for numeric data and keeping leading zero's for non- numeric data in a same column. How can i get numeric data also with leading zero's?
Erland Sommarskog 101.4K Reputation points MVP

2023-08-23T20:49:32.3533333+00:00

House rules: don't piggyback on old threads, but post a new Question, explaining your problem from start to end.

Accepted answer

Erland Sommarskog 101.4K Reputation points MVP

2023-05-22T21:59:22.73+00:00
Sounds like the data type of the column is int. You will need a string data type for the leading zero to be maintained.

Else, to assist with BULK INSERT questions, it helps a lot if you include:

The CREATE TABLE statement for your target table.

You attach a sample file that demonstrates the problem. (We need to see the exact bytes, so including it as text in the post is not a good idea.)

Any format file you are using.

The complete statement as you are running it right now.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Import CSV file in SQL keeping leading zero

0 additional answers