Post reply

Exporting a column that contains CSV data

  • October 20, 2011 at 11:56 am

    #146131

    Hi all,

    I just had a question posed to me and I thought I'd post it here for feeback, as i'm not much of an SSIS guy.

    I have a query that returns information from a student records database, and one of the columns lists guardians (parents, grandparents, nannies, etc), which the query grabs from different places in the database.

    The resultant column ends up being a list of guardians, separated by commas.

    Guardians

    --------------------------

    Jack,Susan,Bob,Mary,Frank

    This column is one of a number of columns that exist in a result set. What I need is a CSV file that contains the entire result set so I can export it to a student management product, but that has each guardian name listed as it's own separated value (basically guardian names are each in their own column in the CSV.

    Any ideas of how to approach this?

    First off, I'm not sure how to translate the original gaurdians column into separate columns in the csv, which is the biggest issue.

    Second, I'm thinking that I will need to create a column immediately following the guardians column that contains some type of flag to let my student database import know that the list of guardian columns in the csv is done, as the number of guardians will vary from student to student.

    Any feedback would be greatly appreciated.

    Thanks

  • October 20, 2011 at 8:56 pm

    #1397728

    jasonkellington (10/20/2011)

    Any ideas of how to approach this?

    Yep... first, you need a splitter. See the following article for that... the actual functions are listed as files in "Resources" near the bottom of the article...

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Then just do something like the following in a stored procedure...

    SELECT src.SomeVarcharCol1,

    src.SomeCol2,

    src.SomeCol3,

    Guardian = split.Item,

    src.SomeCol4

    FROM dbo.YourTable src

    CROSS APPLY dbo.DelimitedSplit8k(src.Guardian,',') split

    Then just export that result set as your CSV file.

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

  • October 21, 2011 at 8:05 am

    #1397986

    jasonkellington (10/20/2011)

    Any ideas of how to approach this?

    First off, I'm not sure how to translate the original gaurdians column into separate columns in the csv, which is the biggest issue.

    Second, I'm thinking that I will need to create a column immediately following the guardians column that contains some type of flag to let my student database import know that the list of guardian columns in the csv is done, as the number of guardians will vary from student to student.

    Any feedback would be greatly appreciated.

    Thanks

    If you are going to use SSIS to export the data into a CSV, it should actually work just fine (without any extra work) if you don't include the headers in the target file as well.

    This is because your values are already comma separated. If you used any other delimiter, or if you plan to include the headers in the file you will have to do something as per Jeff's suggestion.

    Just remember that you are going to end up with a ragged file in which the number of fields may be different for each record. So we are assuming that the other product receiving this file would be able to deal with that.

    Hope this helps.

    Martin.

  • October 21, 2011 at 11:18 am

    #1398140

    Martin Schoombee (10/21/2011)

    jasonkellington (10/20/2011)

    Any ideas of how to approach this?

    First off, I'm not sure how to translate the original gaurdians column into separate columns in the csv, which is the biggest issue.

    Second, I'm thinking that I will need to create a column immediately following the guardians column that contains some type of flag to let my student database import know that the list of guardian columns in the csv is done, as the number of guardians will vary from student to student.

    Any feedback would be greatly appreciated.

    Thanks

    If you are going to use SSIS to export the data into a CSV, it should actually work just fine (without any extra work) if you don't include the headers in the target file as well.

    This is because your values are already comma separated. If you used any other delimiter, or if you plan to include the headers in the file you will have to do something as per Jeff's suggestion.

    Just remember that you are going to end up with a ragged file in which the number of fields may be different for each record. So we are assuming that the other product receiving this file would be able to deal with that.

    Hope this helps.

    Martin.

    I could be wrong but I believe the OP wants 1 "duplicated" row for each element in the CSV column.

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

  • October 21, 2011 at 11:30 am

    #1398146

    Jeff Moden (10/21/2011)

    I could be wrong but I believe the OP wants 1 "duplicated" row for each element in the CSV column.

    Wasn't sure myself, but thought I would give the "other side of the coin".

    Jason, if you require duplicated rows (one for each guardian), then Jeff's proposed solution is definitely the way to go. On the other hand, if your concern was that you would have had to do something extra to ensure the valid output of the CSV...I think my suggestion would do the trick.

    By the way Jeff: Congrats on the "Exceptional DBA" award. Hearing the acceptance speech at the SSC party, your humility and words of wisdom truly inspired us all.

    Martin.

  • October 21, 2011 at 12:59 pm

    #1398185

    Thanks for the feedback on the speech, Martin. Thank you for being there. Very much appreciated.

    --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 6 posts - 1 through 5 (of 5 total)

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