locked
export multiple tables to .csv / .txt RRS feed

  • Question

  •  

    Hi ,

     

    I need to export data from sql data base tables to .csv files with vertical bar (column seperator).The names of the .csv file is to be similar to table name. Eg. exporting      Employee  table should result in Employee.txt / Employee.csv

    Using export / import wizard I could export only 1 table at a time to text file.

     

    The content in the file will be as shown below:

    EMP  |ALL       |103000353|102000001|104000000|n

    EQUP|ALL       |104000117|104000001|105000000|n

     

    As there are huge number of tables and I need to transfer all the user tables to text files seperately, Is there any method / sql query / script to export multiple tables at a time , such that with a single execution i could export 100 tables to 100 text files individually.

     

     

    Looking forward for solution

     

    Saturday, November 24, 2007 12:08 PM

Answers

  • Try

    Code Block
    -t |

     

     

    You might want to check out this topic in Books Online: Specifying Field and Row Terminators

    Saturday, November 24, 2007 3:31 PM

All replies

  • Hi,

     

    I got a sql command to export data of a table to text file.

     

    ////////////////////////////////////////////////////////////////////////////

    DECLARE @cmd VARCHAR(2048)
     
    SET @cmd = 'bcp database1.dbo.table1 out'
      + ' D:\Dbtables\table1.csv -c, -t '
      + ' -SLOCALHOST -Usa -Penter'
     
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT

    /////////////////////////////////////////////////////////////////////////////

     

    This is exporting table to csv file with tab delimited format. But i need it to set Column delimiter with Vertical bar.

     

     

    Can any one help me in suggesting the options of how and what to select from the syntax given below. I could get the syntax for this command but lack of sufficient examples for it in sql help.

     

    ///////////////////////////////////////////////////////////////////////////////////////////////////////////

    bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
        {in | out | queryout | format} data_file
        [-m max_errors] [-f format_file] [-e err_file]
        [-F first_row] [-L last_row] [-b batch_size]
        [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
        [-q] [-C code_page] [-t field_term] [-r row_term]
        [-i input_file] [-o output_file] [-a packet_size]
        [-S server_name[\instance_name]] [-U login_id] [-P password]
        [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

    //////////////////////////////////////////////////////////////////////////////////////////////////////////////

     

    I tried to use the option -t expecting that it would be for column delimiter. but failed by using -t'|'

     

     

     

    Awaiting for Reply.....

     

     

     

     

     

     

     

     

    Saturday, November 24, 2007 1:47 PM
  • Try

    Code Block
    -t |

     

     

    You might want to check out this topic in Books Online: Specifying Field and Row Terminators

    Saturday, November 24, 2007 3:31 PM
  • Hi,

    Have you got answer for exporting multiple tables at a time

    Friday, May 27, 2011 3:34 AM