3

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?

PeterX
  • 2,713
  • 3
  • 32
  • 42
  • Your data looks corrupted. Why are there three values in one row? I would assume that the missing comma at the end of `ABCDE` is throwing that error since it is the column delimiter. – TsSkTo Oct 28 '13 at 08:43
  • There's no missing comma sorry: I just drew an arbitrary line showing a small sub-set of the data, as I couldn't show all 49 columns of data. Something, but I'm not sure what, causes a line-feed, but Excel handles it without a problem. SSIS chokes. – PeterX Oct 28 '13 at 10:20
  • I'm assuming you're using a flat file source for the import. The columns are then predefined. So are the column delimiters, and the row delimiters. If you define a file with 2 columns with a comma column delimiter and a `LF` row delimiter, the flat file will **always** expect this character combination `foo,bar *LF* ` – TsSkTo Oct 28 '13 at 12:37
  • The only way i can think of for detecting two consecutive line feeds is a script task. The flat file import is not smart enough to handle it like excel does – TsSkTo Oct 28 '13 at 12:39
  • Yeah, I've started work on script task, but it's tricky (one of the files has more than one/extra-lines feeds). The last column's delimiter is `{CR}{LF}`, so SSIS really shouldn't choke on a single `{CR}` or single `{LF}` embedded in the data. – PeterX Oct 29 '13 at 02:35

2 Answers2

1

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.

cabbagetreecustard
  • 647
  • 2
  • 13
  • 22
0

The easiest solution for us was to stage the input into a SQL table, and then in a subsequent data-flow, query it back-out without line-feeds in the CSV output, e.g.

SELECT COLUMN1
       ,REPLACE(REPLACE([COLUMN2],CHAR(10),''),CHAR(13),'') AS [COLUMN2]
FROM TABLE
PeterX
  • 2,713
  • 3
  • 32
  • 42