SQLTeam.com | Weblogs | Forums

Csv to ssis (edit flat file before import)

sql2008r2

#1

I am trying to upload data from csv file to sql using ssis package, unable to upload.
my file has different type of data
e.g. first 4 lines included summary of one column only and remaining contains summary_detail of 25 columns.
I have tried by skipping 4 rows by the time of reading that flat-file but unfortunately out of 25 columns i am getting only 14 columns and last column is getting clubbed with 15th to 25th columns


#2

I haven;t fully understood the problem, so my suggestion my not be relevant.

Has a Line Break crept into the CSV file - i.e. it is splitting a ROW onto two/many lines?


#3

I have one csv file with comma(,) deliminator.
this file contains summary as well as detailed records.

summary acquired 1 column and 5 rows

detailed records acquired 25 columns and 1540 rows.

so I wanted to upload only detailed record.
and skip/delete that summary record...

hopefully this will be more comfortable to understand.


#4

Got it, thanks.

I don't know a way to solve that. We import such files into a "staging" table with lots of VARCHAR(MAX) columns, allow the import to bring in all the rows, regardless of the number of columns, and then process the data FROM the Staging Table INTO the Target Tables. Don't know if that would work for you? or is applicable?


#5

It can be done via the scripting component. I have done this before where you have multiple data sets in one flat file. Here is a brief overview of how to do this in SSIS:

Ragged Right File Processing

You can determine if you want to include the sections based on criteria in the the file in the script logic also. Very simple to do once you understand the concept.