April 4, 2014 at 11:26 am
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!
April 4, 2014 at 12:07 pm
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/
April 4, 2014 at 12:10 pm
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.
April 4, 2014 at 12:24 pm
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/
April 4, 2014 at 12:35 pm
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.
April 4, 2014 at 1:06 pm
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/
April 4, 2014 at 2:04 pm
Does anyone else have any other suggestions?
April 8, 2014 at 12:43 pm
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!
April 8, 2014 at 1:04 pm
Thanks methinks.
April 8, 2014 at 2:30 pm
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.
April 8, 2014 at 2:54 pm
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.
April 9, 2014 at 12:13 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 9, 2014 at 11:17 am
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!
April 9, 2014 at 5:26 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply