Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
issue with csv format using ssis

Answered issue with csv format using ssis

  • Friday, January 11, 2013 7:15 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I have urgent requirement in ssis,i.e when I am loading data from sql table to csv format,I need to get the requirement in below format in csv file:

    "filed1","field2","field3",......................

    Regards,

    Sudha

    sudha

     

All Replies

  • Friday, January 11, 2013 7:18 AM
    Moderator
     
     
    Sign In to Vote
    0
    What do you have now in your flat file? field1, field2, field3.... or a different delimiter "field1";"field2";"field3";.....
    You can set the qualifier ("") in the Flat File Connection manager on the general page/tab. The delimiter can be changed in the columns page/tab.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



     
  • Friday, January 11, 2013 7:20 AM
     
     
    Sign In to Vote
    0

    Hi,

    if your question is about quotation mark then just write quotation Mark to Text qualifier box in Flat File Connection Manager.

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

     
  • Friday, January 11, 2013 7:26 AM
     
     
    Sign In to Vote
    0

    Hi ,

    Thanks for your quick response.

    Actually I am loading data from sql table to csv file.In Sql table the values are like field1,filed2,..,now i want out put csv files as"filed1","filed2"....

    In the flat file connection manager I have given quotation mark,But i am able to get only double quotes to the values,i am not able to get commas.

    I am getting the out put as "filed1" "filed2" filed3" in csv file.

    Now my requirement is to get commas inbetween fileds.Can anyone suggest me on this.

    sudha

     
  • Friday, January 11, 2013 7:29 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi ,

    Thanks for your quick response.

    Actually I am loading data from sql table to csv file.In Sql table the values are like field1,filed2,..,now i want out put csv files as"filed1","filed2"....

    In the flat file connection manager I have given quotation mark,But i am able to get only double quotes to the values,i am not able to get commas.

    I am getting the out put as "filed1" "filed2" filed3" in csv file.

    Now my requirement is to get commas inbetween fileds.Can anyone suggest me on this.

    sudha


    go to the second tab/page named columns.... there you see the property Column Delimiter

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Friday, January 11, 2013 7:29 AM
     
     
    Sign In to Vote
    0
    Then you have to set format to delimited and on columns tab set Clumn delimiter to Comma{,}

    Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

     
  • Friday, January 11, 2013 7:30 AM
    Moderator
     
     
    Sign In to Vote
    0
    and make sure you have set the format property on the first tab/page to "Delimited"

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Friday, January 11, 2013 7:51 AM
     
     Proposed
    Sign In to Vote
    0

    Package DFT and Flat file connection manager settings: One file got generated open it using notepad (any editor not with excel you will not see the difference if opened with excel)

    Regards,Eshwar.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed As Answer by Eswararao C Friday, January 11, 2013 9:54 AM
    •  
     
  • Friday, January 11, 2013 8:04 AM
     
     
    Sign In to Vote
    0
    I have tried that way also in column delimiter I have given comma(,)

    sudha

     
  • Friday, January 11, 2013 8:12 AM
     
     
    Sign In to Vote
    0

    Hi Eshwar,

    Is there a way to get commas displayed in csv file,I have tried many ways but no luck.I have to report it to my client,could kindly suggest.

    Regards,

    Sudha

    sudha

     
  • Friday, January 11, 2013 9:06 AM
     
     Answered
    Sign In to Vote
    0

    It will be created in the format that you are expecting but because excel default delimiter is , and text qualifier is " when you open it in excel you will not find the difference open it in notepad.

    Regards,Eshwar.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Friday, January 11, 2013 9:33 AM
     
     
    Sign In to Vote
    0
    Thanks eshwar

    sudha

     
  • Friday, January 11, 2013 9:33 AM
     
     
    Sign In to Vote
    0

    If you have set the file to delimited and the delimeter to "comma" then it should work

    Ensure that you open the file in NOTEPAD rather than Excel to see this as Excel will always open a csv with the commas representing column delimiters

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Friday, January 11, 2013 10:07 AM
     
     
    Sign In to Vote
    0

    Hi Sudha,

    Why dont u use the bcp commands as given below.

    declare @ReportSQL varchar(8000)
    select @ReportSQL = 'bcp " Select QuoteName(<Col1>,'+''''+'""'+''''+'),QuoteName(<Col2>,'+''''+'""'+''''+'),QuoteName(<Col3>,'+''''+'""'+''''
    +') from <DatabaseName>..<TableName>" Queryout "D:\test.csv" -c -t "," -T -S <ServerName>'
    --Print @ReportSQL
    exec master..xp_cmdshell @ReportSQL

    Please have look on the comment