Reading Variable Flat File

Question

Reading Variable Flat File

Arpan Shah-374571

Grasshopper

Points: 22
More actions
September 19, 2007 at 11:40 am

#121705

We have a comma delimited flat file where the number of columns is not fixed for each row.
Is there any SSIS task that will allow reading such a flat file format?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

I've run into this before, and ended up running it through a script task to standardize the format.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Heh... yeah... I've run into this a couple of times my own self... most of the time, I pummeled the vendor until they provided correct data.

When all hope was lost, I did it the way Tim suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

The best option, as suggested, get the source right. of course, this is not always easy.

An option would be to use a ragged right. Split on the first comma or the highest low number of commas and use a derived column to split the rest. Not sure if it would be easier that a script component. Might be a bit faster. Will have to test though...

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Arpan Shah-374571 Grasshopper Points: 22 More actions · Answer 4

Thanks for the replies guys.

I thought there is probably an easier way and we wouldn't have to write a script task. The columns are comma delimited and the rows are CR/LF delimited so I thought SSIS might be able to read the row delimiter and substitute the remaining columns to null. But I guess not.

Will try to work with changing the source or defaulting to the script task.

Thanks

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

... or....

You could import whole lines into a very wide column and split them there using a split function...

... or ...

You could do the same but a bit different... import whole lines into a very wide column, add the correct number of commas, export, and then re-import. I think you'll find that to be surprisingly fast. My even be able to use an OPENDATASOURCE or OPENROWSET to modify the file directly (although, I've never done that particular deed myself).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Friday the 13th Newbie Points: 5 More actions · Answer 6

Hi,

I am having the same issue, but with a fixed length file (Rugged Right)..

But there here is an excellent example for the comma delimited :

The only problem with this example is that this can only work with task that require only a few transformations. I am working on finding a more of a universal approach to the problem.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Are you guys all trying to make me believe that there are some bad vendor apps out there :hehe:??

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Heh... why sure, Remi... just look at the difficulties Steve is having trying to fix some of the problems with this "forum in a can" code that RedGate bought. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

He's not having any troubles... it's just the other 2-3 programmers who are having a hard time :hehe:.

gopal mehta SSC Rookie Points: 29 More actions · Answer 10

try this out....http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx