0

I am looking for a solution to my task. I am importing 800 000 records in CSV format into SQL server. The data is comma separated and the text qualifier is double quotes. But some data in columns contain comma. When I tried it using SSIS package, getting the following error

Error: Data conversion failed. The data conversion for column "xyz" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Therefore I googled and found one solution as Bulk Insert. The data is uploaded but with errors. The column containing comma are splitting into another columns. Below is the query.

BULK
INSERT [5022019]
FROM 'E:\5022019.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Can any one help me out to eliminate the splitting of text into another columns.

  • Start by editing the question and including destination schema and some sample data that is successfully inserted and some that isn't. – vonPryz May 3 at 10:19
  • Personally, I find it kinda cool to read about "lakhs" (and "crores" &c.), but most people here won't know what you're talking about - is a lakh a kilo, or a metre, or .... whatever. Could you please edit and change to 100K? – Vérace May 3 at 13:34
  • Can you change the CSV delimiter to something that will never be in the data e.g. tilde (~)? – Jhunter1 May 3 at 21:38
  • In sql server 2017 and up you can use FIELDQUOTE – HoneyBadger May 3 at 22:33
  • @HoneyBadger thanks for your suggestion. I installed ms sql 2017 and that magic word FIELDQUOTE worked for me. Sorry my late post. I got busy with the data analyis :) – Karan.T. May 10 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.