0

I have come across an awkward situation where I have got CSV files with | as the column delimiter. In these files, I don't have Text column qualifier. (i.e. "). My problem is when I am importing these CSV files using BULK INSERT, if the last column of a row has a line break in it, the text continues into the next row. I am unable to use SSIS so I am looking for a pure T-SQL solution.

It would be great if someone could guide me in the right direction. I tried reading files using openrowset but that's not changing anything either.

Improve this question

1 Answer 1

Reset to default
1

This is probably due to the fact that the text has Carriage Returns in it and that you are using the default row delimiter which is the Carriage Return.

This will be tough to fix since you can't know when a Carriage Return is part of a column or actually a new line. the file needs to be fixed manually.

Unless you know where the file came from... If there's a possibility for you to reprocess the file, then I would reprocess it, replacing all Carriage Returns found in columns by blanks ('').

I hope this helps.

Improve this answer
1
  • Unfortunately, file is generated automatically for our warehouse by our ERP system. I can not change anything with it. In fact, it took lot of convincing from our ERP Provider to get access to these files. :(
    – mouliin
    Commented Nov 17, 2015 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.