Post reply

Adding Quotes around Every Column

  • June 29, 2011 at 9:15 am

    #389863

    Our requirements specify that the data file we submit look like this... with quotes around every field i pull.

    od_custid, od_dob, od_age, od_qty should result in a csv file like this...

    "555","02/05/76","35","0"

    What is the expression of function i need to use to do this. Sorry i'm not a developer, i'm an administrator and this got dropped on me. I really appreciate it!

  • June 30, 2011 at 9:30 am

    #1346623

    Anybody?

  • June 30, 2011 at 10:21 am

    #1346646

    it depends completely on how you are exporting the data; you never mentioned what you are using.

    Are you using SSIS? bcp? your own application?

    bcp requires a format file to describe the text qualifiers for each column; it's a little more work, but that's the fastest app i know to create an export.

    here's a quickie link i found that covers a lot of bcp examples:

    http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2011 at 1:51 pm

    #1346734

    If you are using SSIS, you just need to specify " as the text qualifier in the flat file connection manager.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 6, 2011 at 5:39 am

    #1348845

    Lowell (6/30/2011)

    it depends completely on how you are exporting the data; you never mentioned what you are using.

    Are you using SSIS? bcp? your own application?

    bcp requires a format file to describe the text qualifiers for each column; it's a little more work, but that's the fastest app i know to create an export.

    here's a quickie link i found that covers a lot of bcp examples:

    http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

    Sorry I should have been more clear.

    I have already imported a cobal database into a sql database using an ODBC connection.

    Now that I have finished mapping the fields that our vendor wants, I would like to use SSIS to export the specific fields as a CSV file however I need to also add quotes before and after each comma. "PK","F.name","L.name" and so on. Could i do this using an SSIS derived column?

  • July 6, 2011 at 5:52 am

    #1348853

    Vertigo44 (7/6/2011)

    Sorry I should have been more clear.

    I have already imported a cobal database into a sql database using an ODBC connection.

    Now that I have finished mapping the fields that our vendor wants, I would like to use SSIS to export the specific fields as a CSV file however I need to also add quotes before and after each comma. "PK","F.name","L.name" and so on. Could i do this using an SSIS derived column?

    Koen mentioned how to do it for SSIS, but here's a screenshot;

    in your exisitng flat file connection, if you edit it's properties, you should see the text qualifier that Koen mentioned. That's currently as you see in this screenshot, changing it to dblquote ["] will do what you are after:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 6, 2011 at 8:57 am

    #1349041

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

  • July 6, 2011 at 11:07 am

    #1349151

    Vertigo44 (7/6/2011)

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

    Funny you added a derived task when you can just add the Text Qualifier either as a variable or use it at the component level.

    😉

    Kind regards,
    Gift Peddie

  • July 6, 2011 at 12:20 pm

    #1349215

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

    Funny you added a derived task when you can just add the Text Qualifier either as a variable or use it at the component level.

    😉

    What do you mean by adding it as a variable? I assume when you say the component level I take it you mean Lowell's screenshot just above?

  • July 6, 2011 at 1:49 pm

    #1349274

    Vertigo44 (7/6/2011)

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

    Funny you added a derived task when you can just add the Text Qualifier either as a variable or use it at the component level.

    😉

    What do you mean by adding it as a variable? I assume when you say the component level I take it you mean Lowell's screenshot just above?

    You can create a string variable and add it at the package level and your results files will come with double qoutes. And yes the component level is what Lowell showed. And yet another option is to add it as configuration if you are executing an sql task in the package. In sql you can turn it on and off by changing your config value.

    Kind regards,
    Gift Peddie

  • July 6, 2011 at 2:27 pm

    #1349292

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

    Funny you added a derived task when you can just add the Text Qualifier either as a variable or use it at the component level.

    😉

    What do you mean by adding it as a variable? I assume when you say the component level I take it you mean Lowell's screenshot just above?

    You can create a string variable and add it at the package level and your results files will come with double qoutes. And yes the component level is what Lowell showed. And yet another option is to add it as configuration if you are executing an sql task in the package. In sql you can turn it on and off by changing your config value.

    I consider myself quite well versed in SSIS, but I have no idea what you are talking about :blush:

    You add a string variable with the scope set to the package, and suddenly the flat file destination will put double quotes around the text?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 6, 2011 at 3:33 pm

    #1349333

    Koen Verbeeck (7/6/2011)

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Gift Peddie (7/6/2011)

    Vertigo44 (7/6/2011)

    Thanks! I eventually got it to work by using "\"" + CHG_PATNUM + "\"" in the Derived Column expression box.

    Funny you added a derived task when you can just add the Text Qualifier either as a variable or use it at the component level.

    😉

    What do you mean by adding it as a variable? I assume when you say the component level I take it you mean Lowell's screenshot just above?

    You can create a string variable and add it at the package level and your results files will come with double qoutes. And yes the component level is what Lowell showed. And yet another option is to add it as configuration if you are executing an sql task in the package. In sql you can turn it on and off by changing your config value.

    I consider myself quite well versed in SSIS, but I have no idea what you are talking about :blush:

    You add a string variable with the scope set to the package, and suddenly the flat file destination will put double quotes around the text?

    I would not say suddenly because the package will not compile if that variable value is missing.

    Kind regards,
    Gift Peddie

  • July 6, 2011 at 11:21 pm

    #1349432

    Gift Peddie (7/6/2011)

    I would not say suddenly because the package will not compile if that variable value is missing.

    What are you doing with that variable? Probably something more than just adding it to the package?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 7, 2011 at 8:09 am

    #1349730

    Koen Verbeeck (7/6/2011)

    Gift Peddie (7/6/2011)

    I would not say suddenly because the package will not compile if that variable value is missing.

    What are you doing with that variable? Probably something more than just adding it to the package?

    We have many variables some left without value but SSIS will not compile if the Text Qualifier variable is left blank. I also know if the data is from a database table a configuration which can be turned on with one can add text qualifier and a zero to turn off.

    Kind regards,
    Gift Peddie

  • January 2, 2015 at 4:29 pm

    #1767503

    adding " in text qualifier worked for me...thanks! although, it won't show up in preview mode, so i'll add that the job needs to be executed to see the results

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

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