Post reply

transference of datas using bcp???

  • June 21, 2005 at 10:29 am

    #66623

    Hi Masters, i need your help please , i need copy datas from a "txt file" to a "sql table" using bcp ,but i just want  copy two fields (colummns) of the txt file to the table...how can i do that?

    bcp "mybase..mytable" in myfile.txt -c -q -Smyserver -U"myname" -P"mypass"

    thank you...

     

  • June 21, 2005 at 10:44 am

    #567139

    You would want to use Format Files -f"FormatFileName"

    Use Host file field order and Server column order, Name to specify the order and data.

    Regards,
    gova

  • June 21, 2005 at 10:46 am

    #567142

    You'll need to use a 'format file' (see the Books Online documentation for a good description).  Basically, the format file describes the input file and indicates which table columns correspond to which input columns.

    And then again, I might be wrong ...
    David Webb

  • June 21, 2005 at 11:04 am

    #567151

    Format files are a real pain... You could go with the insertion into a temporary table, SELECT your two columns and then drop the temporary table.

  • June 21, 2005 at 11:14 am

    #567152

    mmmm Hi , occurs that my txt file has 50 columns , and i woudnt want to create a table with 50 fields, i would want to find a easier way to do that,

    i have to create a format file with 50 fields??? what a horrible

    mmm , is posible create a table with the fields of txt easily???

  • June 21, 2005 at 11:19 am

    #567153

    mm i was read the documentation, but is necessary to write a format file with all the fields of txt? my txt file has 50 fields.... ugh and i need only two fields............

  • June 21, 2005 at 11:39 am

    #567163

    No need to write the format files for entire 50 fileds. You have to mention poition of the column and column names you have to load. Just play around with Format Files you will know in no time. You can also use BulkInsert which uses bcp.

    BOL -

    A format file provides a way to bulk copy data selectively from a data file to an instance of SQL Server. This allows the transfer of data to a table when there is a mismatch between fields in the data file and columns in the table. This approach can be used when the fields in the data file are:

    • Fewer than the columns in the table.
    • More than the columns in the table.
    • In a different order from the columns in the table.

    Regards,
    gova

  • June 23, 2005 at 9:21 am

    #567958

    If you have the Table just use this command to generate the FormatFile that you need

    bcp [MyDB]..[MyTable] format test.txt -f "ExportFormat.fmt"  -c -t \t -r\n -S ServerName -T

    or you can replace the table  with a query

     

    Kindest Regards,

    Vasc

Viewing 8 posts - 1 through 7 (of 7 total)

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