CSV file to table

Question

CSV file to table

rs80

SSCrazy

Points: 2312
More actions
April 4, 2014 at 11:26 am

#290008

I'm wondering if the data from a sample csv file can be transformed and loaded into a table. I have attached a workbook where worksheet1 has the sample data and worksheet2 has how the data should look in the table.
Thanks for the help!

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rs80 (4/4/2014)
I'm wondering if the data from a sample csv file can be transformed and loaded into a table. I have attached a workbook where worksheet1 has the sample data and worksheet2 has how the data should look in the table.
Thanks for the help!

I didn't look at your excel document but this is pretty simple to do. You can right click the database and import if this is a one time thing. If it is something you need to do repeatedly then look into creating an SSIS package to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rs80 SSCrazy Points: 2312 More actions · Answer 2

Thanks Sean for your reply. The loading of the file is on a daily basis. The layout of the data isn't straightforward so it's difficult for me to figure out how to store it in the table. Please take a look at the file.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

rs80 (4/4/2014)
Thanks Sean for your reply. The loading of the file is on a daily basis. The layout of the data isn't straightforward so it's difficult for me to figure out how to store it in the table. Please take a look at the file.

I am not by any stretch of the imagination good with SSIS but this doesn't look that bad. Have you tried working with it to see if you can get it even close?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rs80 SSCrazy Points: 2312 More actions · Answer 4

I usually use a flatfile source for csv files. But since this csv has a different layout because of the way the headers are repeated I wasn't sure what to do.

My co-worker ended up loading the data into a staging table and parsing the data using a SP to a final table.

However, I was wondering if there is a way to do this in SSIS. I would imagine I'd have to use a script component to do some preprocessing. But that's why I thought of asking the experts to see what they think.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

rs80 (4/4/2014)
I usually a flatfile source for csv files. But since this csv has a different layout because of the way the headers are repeated I wasn't sure what to do.
My co-worker ended up using loading the data into a staging table and parsing the data using a SP to a final table.
However, I was wondering if there is a way to do this in SSIS. I would imagine I'd have to use a script component to do some preprocessing. But that's why I thought of asking the experts to see what they think.

Loading to a staging table is the best way to go. Sounds like you figured it out. COOL!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rs80 SSCrazy Points: 2312 More actions · Answer 6

rs80

SSCrazy

Points: 2312

April 4, 2014 at 2:04 pm

#1703421

Does anyone else have any other suggestions?

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 7

I wouldn't quite call your source a csv file!!! This is not formatted in a consumable manner.

Whomsoever it is that generates this file should provide an actual data file. SSIS can't do anything with this.

You are better off using vba to parse the Excel into an actual csv but methinks this is not the only manner in which this file will show up so I am guessing there is no repeatable process you can write until they standardize the source data into an actual Data File.

Good luck!

rs80 SSCrazy Points: 2312 More actions · Answer 8

rs80

SSCrazy

Points: 2312

April 8, 2014 at 1:04 pm

#1704284

Thanks methinks.

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 9

Please let us know how it goes. I have often had a user adamant that what they provide is "good enough" so if you meet with resistance and really have to work with that file as is, come on back here.

I strongly hope they have the desire to create a real data file for you as it will be smooth sailing for you after that.

a4apple SSCrazy Points: 2620 More actions · Answer 10

If the format keeps changing you can try using a Script task as Destination and you need to use a significant amount of code, I tried once.. I will try to post if I have it.

But in my case the data was coming from some stored procedure to a file. It was long back so I prefer saying I have to try again..

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 11

a4apple (4/8/2014)
If the format keeps changing you can try using a Script task as Destination and you need to use a significant amount of code, I tried once.. I will try to post if I have it.
But in my case the data was coming from some stored procedure to a file. It was long back so I prefer saying I have to try again..

I think you mean a script component. If not, please expand on what you mean.

This can be done using an asynchronous script component as source (not destination - destination is the table where the data needs to be inserted) and it's not that much code.

The 'asynchronous' bit refers to the fact that the number of rows coming out of the component is different from what goes in.

Each row of the file should be read as a single string and then parsed in the script.

You will find examples if you search for them, or I can provide more input if you decide to change how you are doing things.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rs80 SSCrazy Points: 2312 More actions · Answer 12

I was waiting and wondering when you would provide your input, Phil.

My co-worker resolved this by loading the data into a staging table and then using a SP to parse the records in the staging table and loading it into a final table.

Methinks that this isn't the best approach because he had to write of sql to accomplish this. And if the file changes by including an extra (or removing) column then it would take time to modify the sql. Do you think it would be "easier" to add code in the script component? And also to maintain the code in case there is a requirement to add or remove a column.

Thanks!

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 13

herladygeekedness (4/8/2014)
I have often had a user adamant that what they provide is "good enough" so if you meet with resistance ...

Heh... I love users like that. They're the first to whine when you send them data in their same silly format. That's why I invite them to a high velocity pork-chop dinner at the first sign of such resistance.

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