CSV CRLF Issue

Question

CSV CRLF Issue

MichaelMontgomery

Mr or Mrs. 500

Points: 561
More actions
June 25, 2014 at 10:39 am

#287156

Hi All
I have a user who is providing a csv of SQL data exported from ssms. When she exports the data the row delimiter is LF and not CRLF. She has selected CRLF as the delimiter but it is appearing as LF only in the file.
I am trying to import this csv using a SSIS package which is set up for other csv and expects CRLF.
Anyone know why the csv would have LF and not CRLF?
Thanks in advance 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Xedni SSCertifiable Points: 6951 More actions · Answer 1

This may not be 100% the answer, but different parsers by default may use different line terminators (CR or LF). Generally when both are used it means whichever the parser is configured to use as the line terminator is more than likely one of the two.

If it's causing problems for you to parse them, I'd open the file in your favorite text editor (assuming it's not too big) and use a regular expression to replace the line terminating "LF" to "CRLF", although SSIS should also give you tools to change what line terminator it expects.

Executive Junior Cowboy Developer, Esq.[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

I need to see the sourcing code or specifics about the technique the originator is using Michael to really nail down why that's happening.

Jay's recommendation above is your best bet for a fast fix to a one off.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

MichaelMontgomery Mr or Mrs. 500 Points: 561 More actions · Answer 3

Thanks for this. I will use the find replace for now. I thought it may be a setting in windows or SSMS which could be changed. Hopefully the final regular export does not have the issue.

I could set SSIS to change the expected delimiter but I would like to reuse an existing package to import data from multiple source files.

Thanks again!

MichaelMontgomery Mr or Mrs. 500 Points: 561 More actions · Answer 4

Just an update on this. I have figured out what the problem is. We are using an FTP to move the files from one location to another. Turns out the FTP is replacing the CRLF with a LF.

Doh:doze: