Post reply

BCP

  • October 7, 2003 at 1:22 pm

    #107173

    While BCP'ing how can I ignore some of the columns??

    Description: I want to insert one flat file contains 10 columns into a table which has 7 columns....I want to igonre the rest three filed from the flat file what is the syntax for that?

    Thanks

    Paul

    Dimpal Patel


    Dimpal Patel

  • October 7, 2003 at 5:21 pm

    #477467

    Use a format file. Check "Using Format Files" in books online.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • October 8, 2003 at 8:15 am

    #477553

    Or you can create a view and BCP from the view.

    Linda

  • October 8, 2003 at 9:04 am

    #477570

    Format files can be a pain. I would first create a table to match the file structure exactly and use a DTS bulk insert task. It has a "Generate" button that can create the format file. I would also test the task with a few records (use the options "Only copy selected rows"). I would also set the batch size to about 1000 unless you need a single transaction; otherwise, the db log file will grow very large for large amounts of data and affect performance.

    Once you have the format file, the second to last column (6th) can be adjusted to remove (set to 0) or reorder the columns to the destination. The last column does not change and is the destination column name for the source field of the format file row. Once the new format file is available, a new bulk insert task with the new destination should work.

    You could also use the above test table as a staging table. Transfer from it to the final destination. This is a good choice if you do not bulk into the final destination (full recovery) or do not need to use most of the data imported. I prefer to not touch the final destination unless required.

    If you have ANSI nuls in your data, the generate format file chokes. The DTS designer does not read data past the nul. You have to either “fix” the data or write the format file by hand. I had a very large ledger file with about a hundred columns. And, of course, it had nuls here and there. It was too big to edit easily. What fun that was. A test file can be made by extracting a few records and removing any nuls. Once the format file is created, it can be used with the original data, provided fixed column widths are used. Parsing for column delimiters will fail if nuls exist.

    An error message usually tells you that it does not work, but not what the problem is. The best way I know to identify a problem record in the format file is to "turn off" columns (set 6th column to 0) until the problem stops.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • October 8, 2003 at 9:07 am

    #477572

    I think the view will work if the destination table has extra columns, not fewer.

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 5 posts - 1 through 4 (of 4 total)

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