June 2, 2017 at 9:26 am
I am trying to import several pipe delimited text files. The files have a field called COMMENT, which sometimes contains a return. I have viewed the files in Notepad++, and turned on the option to show all characters. Each normal row ends with a line feed (LF). The bad comment field sometimes contains just a line feed, and sometimes a carriage return + line feed (CRLF).
In SSIS I have a derived column transformation. I have tried several different expressions to remove the returns. But nothing has worked for me. When I run the package and query the table, I still see instances where a row has been shifted down due to the bad comment field. Here are some of the expressions I have tried. I have actually tried several other variations of this expression.
REPLACE(REPLACE(COMMENT,"\x000D"," "),"\x000A"," ")
REPLACE(REPLACE(COMMENT,"\n"," "),"\r"," ")
I found these expressions from Googling, and reading other blogs / forums. But this is not working in my case. I hope someone can suggest a solution.
thanks
June 2, 2017 at 9:54 am
I blogged about something similar a few years ago. Might this help? https://www.timmitchell.net/post/2015/03/23/fix-inconsistent-line-terminators-in-ssis/
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
July 27, 2020 at 4:14 pm
Hi Tim,
I am having an issue where there the line terminator is LF, but the flat file contains some CRLF. I tried the method in your blog and it did not resolve the issue. Below is what the flat file looks like in notepad++ (sensitive data blurred for security). The LF is what should be the line terminator. I've tried the replacing this in a derived column and in a script component (.replace("\r\n","") and it is not working. Do you have any suggestions?
September 20, 2024 at 3:44 pm
@brianksouth I have this exact issue now. Did you get this resolved and do you recall how?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy