Import Files with Different Header Structures

Question

Import Files with Different Header Structures

rcr69er

Ten Centuries

Points: 1010
More actions
June 13, 2015 at 5:08 am

#301522

Hi
I currently have a directory of csv import files, all of which have the same data structure but different header information.
For example:
File 1
This is header info.
This is header info.
This is header info.
ID,Name, DOB, etc…
File 2
This is header info.
This is header info.
This is header info.
This is header info.
This is header info.
ID,Name, DOB, etc…
The data starts with the column title row, ie ID,Name, DOB.
What I need to happen is process that removes all the header rows up to the title row so that all import file structures will be the same.
I was thinking of using a ForEach Loop container that will run a script on each of the files to remove the header. Does anyone have a script which does is this or point me in the direction that will help.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Quite few options here, simplest would probably be a script transformation task which "knows" the column headers and starts splitting the file once passed the header information, pretty straight forward. Another option is to import the file to a staging table, line to row, and then find the column header and split the consequent rows.

😎

rcr69er Ten Centuries Points: 1010 More actions · Answer 2

Thanks for reply.

Can you point me in a direction on creating a script like this.

Fairly new at using script transformations.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

I'll look for some samples I should have, in the meantime there is a fairly good article here: SSIS - Script Component, Split single row to multiple rows and here:Split Comma separated value from input table into Output Table in SSIS[/url]

😎

This can also be done in T-SQL, have a look at this article: Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

The logic has to

1) read the rows from the beginning of the file

2) find the column header row, discarding all previous rows

3) parse all subsequent rows into columns

Quick question, what version of SQL Server are you using?

rcr69er Ten Centuries Points: 1010 More actions · Answer 4

Hi

Thanks for that.

Current using a mixture of 2012 and 2014.