CSV file to table

  • 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!

  • 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/

  • 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.

  • 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/

  • 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.

  • 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/

  • Does anyone else have any other suggestions?

  • 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!

  • Thanks methinks.

  • 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.

  • 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.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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!

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

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

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