none
Export SQL data to comma delimited csv file

    Question

  • Hi,

    I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file.

    So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited.  I tried finding examples but couldn't see what I should do to have a comma delimited file.  (I'm getting a bit tired now, so I might be missing something!)

    I have created a bat file containing the following code:

    bcp "TestDB..GA_FSM_DCSF_Extract" out "C:\GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:\error.log"  -c -T -S srckvzg2j -r \r\n

    Any help / pointers would be much appreciated.

    Thanks,

    Henrik
    Friday, October 19, 2007 1:07 PM

Answers

  • If anyones interested, I think I refined this a bit better:

    bcp "TestDB..GA_FSM_DCSF_Extract" out "C:\GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:\error.log" -T -S srckvzg2j -r \r\n

    I'm using the following format file (export.fmt).

    8.0
    4
    1       SQLCHAR       0       15      ","         1     NINO        Latin1_General_CI_AS
    2       SQLCHAR       0       20      ","         2     NASN        Latin1_General_CI_AS
    3       SQLCHAR       0       24      ","         3     DOB         ""
    4       SQLCHAR       0       40      "\r\n"      4     Surname     Latin1_General_CI_AS


    The "," replaces what I had before with the -t"," bit.  Also removed the -c bit as this overrode the format file.

    I'm getting there, even though my eyes are starting to blur from staring at my screen for too ling.. ah well.

    And for those who show further interest, I used the following command to create the initial fmt format file:

    bcp TestDB..GA_FSM_DCSF_Extract format nul -fexport.fmt -Ssrckvzg2j -T -c -r
    Friday, October 19, 2007 2:19 PM

All replies

  • Is there a reason why you are using BCP and not using SSIS? (SQL Server Integration Services)

     

    Using SSIS I can explain very easily how to export as CSV but I am not versed in BCP.

     

    Friday, October 19, 2007 1:13 PM
  • BCP was the first thing I found that could do what I wanted.  I'm a C# developer normally and don't know much about SQL Server, except how to write basic queries.

    Anyway - I think I found what I was looking for by accident, my modified code produces a comma delimited csv file:

    bcp "TestDB..GA_FSM_DCSF_Extract" out "C:\GA_FSM_DCSF_Extract.csv" -fexport.fmt -t"," -e "C:\error.log"  -c -T -S srckvzg2j -r \r\n

    The -t"," bit does it.

    Thanks for your quick response, much appreciated!

    H
    Friday, October 19, 2007 1:32 PM
  • No worries. Don't forget to mark your topic as answered so people can check it out if they need assistance with BCP.

     

    I am going to research it myself it looks like a useful tool.

     

    Regards, Ian.

     

    Friday, October 19, 2007 2:07 PM
  • If anyones interested, I think I refined this a bit better:

    bcp "TestDB..GA_FSM_DCSF_Extract" out "C:\GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:\error.log" -T -S srckvzg2j -r \r\n

    I'm using the following format file (export.fmt).

    8.0
    4
    1       SQLCHAR       0       15      ","         1     NINO        Latin1_General_CI_AS
    2       SQLCHAR       0       20      ","         2     NASN        Latin1_General_CI_AS
    3       SQLCHAR       0       24      ","         3     DOB         ""
    4       SQLCHAR       0       40      "\r\n"      4     Surname     Latin1_General_CI_AS


    The "," replaces what I had before with the -t"," bit.  Also removed the -c bit as this overrode the format file.

    I'm getting there, even though my eyes are starting to blur from staring at my screen for too ling.. ah well.

    And for those who show further interest, I used the following command to create the initial fmt format file:

    bcp TestDB..GA_FSM_DCSF_Extract format nul -fexport.fmt -Ssrckvzg2j -T -c -r
    Friday, October 19, 2007 2:19 PM
  • Ian,

    I am trying to export a view as a CSV file but an unsure how to do it.  Could you explain how to do it using SSIS?

     

    Wednesday, June 25, 2008 5:31 PM
  • Can you explain in details how to export SQL 2008 data to a CVS file? If it is the long term thing, can I use a stored procedure for it not query?
    Monday, July 11, 2011 3:44 PM
  • Hi,

    Coiuld you please explain the steps so that i can export data from sql server table to csv file.

    When i use SSIS its getting failed due to data conversion.

    Please give me a solution.In sql tabluar one column having combinition of english and chinese characters.SO please do the needful.

    thanks in advance..

    Santosh Y


    Santosh Y
    Monday, October 17, 2011 8:19 AM