.csv with internal line breaks

[SQLSister]

I'm trying to convert a .csv file and one field is a long descriptive field which has internal line breaks. Unfortunately SQL server is interpreting these as new records when I do the import. Has anyone else run into this nad how did you solve it?

 

[fluteplr]

Are you using csv with double quotes as text delimiters?

 

[SQLSister]

NO it has pipe delimiters

 

[MaffewW]

hi SQL Sister. did you fix this? I think I have seen this before, having carriage return/line feed in the data and having carriage return/line feed as the row delimiter cant be done I dont think. What we had to do was re export the data (mightnt be so easy if its from a client I know) from the source and specify something different as the row delimeter instead of CR/LF, so you would have pipe as the column delimiter and say tab or semicolon as the row delimiter.

Matt

Brighton, UK

 

[SQLSister]

What we ended up doing was writing a parser in vb.net which checks the number of delimiter characters on a line (|in this case) and if it does not match the number which are supposed to be there, then it removes the carriage return from the line. This puts all the data for a record back on one line. We then plan to run this in DTS using the Execute Process task.