SSIS: Can't handle line-feeds in CSV (Column delimiter not found)

character-encoding ssis carriage-return linefeed
PeterX picture PeterX · Oct 27, 2013 · Viewed 8.1k times · Source

I have some CSV files that appear OK in Notepad and Excel however seem to have extra line-feeds in them when I view them in VS2010 or Notepad++. When I attempt to process them in SSIS, the files fail with errors like this:

Error: 0xC0202055 at Merge Files, Interface [225]: The column delimiter for column "Column 48" was not found.

Here's a truncated example (there's about 50 columns, and the line-wrap appears to wrap randomly at the same position):

enter image description here

The questions are: how does Notepad and Excel open these files OK (and seemingly ignore the line-feeds)? Is there a way to get SSIS to process these files? Could it be an SSIS setting on code-page etc?

Answer

cabbagetreecustard picture cabbagetreecustard · Jun 22, 2014

For me opening the file in Excel, saving as an excel file (xlsx but I am sure the old xls format would work fine too), then using the Excel Source in SSIS enabled me to load a file into a SQL table with this kind of problem.

Obviously this would not work if you need to load this kind of file regularly or if there was many of these files. In that case the first answer would be better.

Related questions

Best way to convert string to bytes in Python 3?

There appear to be two different ways to convert a string to bytes, as seen in the answers to TypeError: 'str' does not support the buffer interface Which of these methods would be better or more Pythonic? Or is it …

Read More
How do I get a consistent byte representation of strings in C# without manually specifying an encoding?

How do I convert a string to a byte[] in .NET (C#) without manually specifying a specific encoding? I'm going to encrypt the string. I can encrypt it without converting, but I'd still like to know why encoding comes to …

Read More
Working with UTF-8 encoding in Python source

Consider: $ cat bla.py u = unicode('d…') s = u.encode('utf-8') print s $ python bla.py File "bla.py", line 1 SyntaxError: Non-ASCII character '\xe2' in file bla.py on line 1, but no encoding declared; see http://…

Read More