Using SSIS to import a non fixed length CSV file

Question

Post reply

Using SSIS to import a non fixed length CSV file

Alex koranteng

SSC Veteran

Points: 228
More actions
November 15, 2008 at 2:13 pm

#124312

Is it possible to import non fixed length CSV file into SQL server 2005 table. I have reading forums and have seen inconsistent reviews. I am a newbie to SSIS and need the best approach/solution to this problem
Thanks
Alex

Viewing 15 posts - 1 through 14 (of 14 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

CSV files are, by nature, not fixed length. Do you mean CSV files which have varying numbers of records on each line?

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alex koranteng SSC Veteran Points: 228 More actions · Answer 2

Phi,

That is exactly the case here, the number of coulmns can change from line to line and at this point I do not have control of the incoming file. Any ideas

Thanks

Alex

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Yuk - so how do you know what the data is in each field? Or is it just that there may be some missing fields at the end of the record?

Can you give a small example of the input data?

BTW, I meant 'fields', not 'records' in my previous post, as you obviously guessed.

Cheers.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

I've run into this need before, and in most cases the row type was identified by the first element on each row. A row type of 10 might have demographics and include 20 fields, row type of 25 would have insurance information with 15 fields, etc. I've had good luck using a script component as a data source and handle the dissimilar record types with multiple outputs in the script component.

hth,

Tim

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: 1004340 More actions · Answer 5

Alex koranteng (11/15/2008)
Is it possible to import non fixed length CSV file into SQL server 2005 table. I have reading forums and have seen inconsistent reviews. I am a newbie to SSIS and need the best approach/solution to this problem
Thanks
Alex

Yep, it's possible... I've just never uses SSIS to do it. Any chance of you attaching a file and a record layout?

--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)

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

I have done this using a script component as Tim referred to above. Since you are new to SSIS, however, just be aware that you will be taking a deep dive into the Script Component functionality. In my case, we prefer to keep our ETL processing in SSIS if possible. However, I do recall thinking that it just might be easier to use the filesystem object in VBscript .

Alex koranteng SSC Veteran Points: 228 More actions · Answer 7

This is a snapshot of the incoming file

e-TIME,,,,,,,,,,,,,,,,,,,,,,,,

Co Code,Batch Id,File #,Pay #,Rate Code,Temp Dept,Temp Rate,Reg Hours,O/T Hours,Hours 3 Code,Hours 3 Amount,Hours 3 Code,Hours 3 Amount,Hours 3 Code,Hours 3 Amount,Hours 3 Code,Hours 3 Amount,Earnings 3 Code,Earnings 3 Amount,Earnings 3 Code,Earnings 3 Amount,Earnings 3 Code,Earnings 3 Amount,Earnings 3 Code,Earnings 3 Amount

T82,HOURLY,67681,1,,,,80,17.25,,,,,,,,,,,,,,,,

T82,HOURLY,595544,1,,,,76,20.5,H,8,P,12,,,,,,,,,,,,

T82,HOURLY,496269,1,,,,56.75,8,H,8,S,24,,,,,,,,,,,,

T82,HOURLY,86179,1,,,,74.75,21,H,8,,,,,,,L,52.75,,,,,,

T82,HOURLY,551487,1,,,,80,11,H,8,,,,,,,L,53.63,,,,,,

T82,HOURLY,805934,1,,,,70.75,13.5,P,16,H,8,,,,,L,45.06,,,,,,

T82,HOURLY,585298,1,,,,59,8.25,H,8,,,,,,,L,33.63,,,,,,

T82,HOURLY,417134,1,,,,80,27,H,8,,,,,,,L,88.38,,,,,,

T82,HOURLY,785874,1,,,,75.75,12.5,H,8,,,,,,,,,,,,,,

T82,HOURLY,811350,1,,,,70.75,4.5,,,,,,,,,,,,,,,,

T82,HOURLY,829513,1,,,,65.75,2.75,H,8,P,8,,,,,,,,,,,,

T82,HOURLY,863666,1,,,,71.5,12,H,8,,,,,,,,,,,,,,

T82,HOURLY,979700,1,,,,34.75,,H,8,S,24,,,,,,,,,,,,

T82,HOURLY,313843,1,,,,61.75,8,H,8,P,16,,,,,L,34.88,,,,,,

T82,HOURLY,615087,1,,,,80,28,H,8,,,,,,,L,89.44,,,,,,

T82,HOURLY,936943,1,,,,80,9.5,H,8,,,,,,,,,,,,,,

T82,HOURLY,962651,1,,,,57.75,,,,,,,,,,,,,,,,,

T82,HOURLY,225913,1,,,,74.25,1.5,H,8,,,,,,,,,,,,,,

T82,HOURLY,898356,1,,,,56.25,8.75,H,8,,,,,,,L,42.13,,,,,,

T82,HOURLY,187656,1,,,,8.75,8.75,,,,,,,,,L,8.75,,,,,,

T82,HOURLY,139252,1,,,,65.25,8,H,8,,,,,,,L,41.75,,,,,,

T82,HOURLY,775634,1,,,,67,12,H,8,,,,,,,,,,,,,,

T82,HOURLY,107777,1,,,,75.25,12.5,H,8,P,8,E,8,,,L,52.19,,,,,,

T82,HOURLY,886000,1,,,,80,11.5,H,8,,,,,,,,,,,,,,

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

SSIS will eat that up, no problem - that is standard CSV format. The fact that many of the source fields are NULL just needs to be accommodated as part of your config. No need for scripting.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alex koranteng SSC Veteran Points: 228 More actions · Answer 9

Thanks for the info. Do you mean to say that SSIS can do this job for me. And if it would you point me to some good links to get me started. Obe of the threads mentioned using the file system object using VBScript, but i do not that scripting language

Alex

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

I think people misunderstood your first post a little - you suggested that there could be varying numbers of fields on each record. To accommodate that scenario, scripting would be required, containing decision-based logic.

But your records all contain the same number of fields, it's just that sometimes they are NULL.

SSIS can bring this data into SQL Server without the need for scripting, for sure.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Yep... I agree... no scripting required for that. In fact, you don't need SSIS, either. Simple BULK INSERT will do.

--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)

dva2007 SSCertifiable Points: 7707 More actions · Answer 12

Hi,

i am trying to use bulk insert first time. I need data from csv to sql table. it gives some error.

annot bulk load because the file "\\\wcl\Extracts\Executive\2008-12-19.csv" could not be opened. Operating system error code 5(Access is denied.).

I do have read permission on csv and i can open the file. Do i need full permission on csv? i dont think so.

I am member of sysadmin.

Can you please advice?

thanks,

vijay

Mukti SSCrazy Points: 2826 More actions · Answer 13

You need to create a flat file connection. Specify [b],[/b] as your column delimiter and {CR}{LF} as your row delimiter. You actully don't need to set all this. If you chose the file SSIS will automatically do this for you if each row has equal number of fields.

http://msdn.microsoft.com/en-us/library/ms140266.aspx

HTH

KKK17 SSChasing Mays Points: 603 More actions · Answer 14

Hi,

I have a similar case, where one of my user asked us grant bulk insert permission, as he is trying to import a flat file using SSIS. I am new to SSIS. I am little confused that, only Bulk Admin role will fix it or do i need to do something on SSIS.

Please suggest.

Thanks a lot,

KK