End of Line characters include CR or LF. Windows uses both CRLF at the end of a line, whereas Unix uses only a LF.
- CR = Carriage Return
- LF = Line Feed
Recently, while troubleshooting why data wont import successfully as part of an automated process, I was pulling a subset of data out of the main text file, but the end of line markers weren't correct. I copied several lines using Notepad ++ and it automatically used CRLF markers. The automated process expected the end of line markers to be LF to be read by the SSIS package properly.
This article will help job my memory when I run across this again... but hopefully it helps someone else too! :)
First off, within Notepad ++ to see the end of line markers, you need to indicate you want to see them. Click on View > Show Symbol > then either Show End of Line, or Show All Characters if you want to see spaces and tabs, sometimes the second option is easier).
After that, you will be able to see the end of line characters and see which ones are used.
Using the find and replace within Notepad++, we can easily change back and forth between CRLF and LF, as shown below. We will be using \r (CR) and \n (LF) as matching values. In this case, I am replacing CRLF with LF, but you can switch the values and do vice versa easily.
- Click on Search > Replace (or Ctrl + H)
- Find what: \r\n
- Replace with: \n
- Search Mode: select Extended
- Replace All
This automated process used an SSIS package. In the flat file connection manager within SSIS, you will set what the end of the line character(s) are. This is very easy to use while building the automated process, etc. The screenshot below shows what the connection manager will look like for a line ending with LF.