BCP and decimal columns

  • aribi

    SSC Veteran

    Points: 265

    We have copied tables from AS400 into .txt files with http://FTP.We want now to load these files in SQL tables using BCP. The source and target tables have exactly the same stucture; some columns are in decimal format and cause always errors. What can we do?

  • neiljacobson

    SSCommitted

    Points: 1717

    If you use DTS you can shape (transform) the data. When the dts package runs it can then clean any hidden characters or at least tell you what lines are causing problems.

    If you must use BCP watch for first line containing filed names and identity inserts.

  • aribi

    SSC Veteran

    Points: 265

    quote:


    If you use DTS you can shape (transform) the data. When the dts package runs it can then clean any hidden characters or at least tell you what lines are causing problems.

    If you must use BCP watch for first line containing filed names and identity inserts.


  • aribi

    SSC Veteran

    Points: 265

    Thanks's Neil.

    I think DTS is not as fast as FTP + BCP;

    so whe had a preference for FTP, but it seems not easy!

    Alain RIBIERE

    OPAC PARIS FRANCE

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720491

    BCP can be tricky and requires exact formats. You might try experimenting with the format files, or even extract a row from your table with BCP and save the format file.

    If you post the DDL for the table and a couple rows of sample data, maybe one of us can help.

    Steve Jones

    steve@dkranch.net

  • nigelrivett

    SSCertifiable

    Points: 5362

    decimal shouldn't cause a problem. Are you sure the file is the correct format?

    Have you try batch size 1 and fail on first error to see what happens. Get rid of the data after the decimal point (and the decimal point).

    I like to bcp into a view rather than a table so that I can add fields to the table (like identity or status or load date) without messing up the bcp.

    I have never had to use a format file - but that's because I have always been able to control the source data. Playing around with them they look like a source of problems but will be necessary if you can't change the source.


    Cursors never.
    DTS - only when needed and never to control.

Viewing 6 posts - 1 through 6 (of 6 total)

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