CODEWITHSUNDEEP
sql-server-2008csvssis
LegalEagle
LegalEagle

Reputation: 97

SSIS CSV Commas in fields

Problem:

I am attempting to load a CSV file into our SQL Server 2008 database and having difficulty parsing the fields.

I have found instructions on reading the file using a Flat File Source and using double quotes as the text qualifier in order to count the fields with commas as a single string value, and have been able to successfully do this.

Data:

This file has three line items per account record. All account records have lines 1, 2, and 3, and 1's have 35 columns, 2's have 22, 3's have 37 columns. The columns are not a 1:1 relationship, so for example column 6 on record 1 is NOT the same data (or data type) as column 6 on record 2. Additionally, each line item needs to go into separate tables, so I have to process them individually.

My first step is to separate the 1, 2, and 3 records with a Derived Column transformation. The second step would be to parse the fields of each record type into their individual fields and this is where I encounter issues.

The biggest problem is that one (sometimes two) of the fields in the 1 record will SOMETIMES have an enclosed comma and sometimes not. So... sometimes SSIS parses the 1 records as 36 fields, sometimes 35, sometimes 34.

Attempted solutions:

If I use Flat File Source with quotes as text qualifier to import the file, then the preview doesn't recognize the {CR}{LF} between lines 1 & 2 and the columns don't line up right in the preview (columns 2 & 3 get run together) and I get errors when running. Alternatively I have tried using a Ragged Right file source, reading all data into one 4000-length string column and then parsing using FINDSTRING and SUBSTRING and counting delimiters, but this is where I encounter issues with the enclosed commas.

If I use Flat File, it doesn't seem to recognize the {CR}{LF} terminators correctly. If I use Ragged Right, I can't seem to count the delimiters correctly because of the enclosed commas. It's a Catch-22!

Any insight???

Upvotes: 0

Views: 316

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

If I correctly understand that your flat file looks something like this:

"This Is Record 1, line 1, 34 other fields...
This Is Record 1, line 2, 22 other fields...
This Is Record 1, line 3, 37 other fields..."
"This Is Record 2, line 1, 34 other fields...
This Is Record 2, line 2, 22 other fields...
This Is Record 2, line 3, 37 other fields..."

Then you are not going to be able to handle this with a Flat File Source. You will need to use a Script Transformation source and write custom code to parse each line of the file and send it to the appropriate destination table.

Upvotes: 1

Related Questions