BCP and decimal columns

Question

BCP and decimal columns

aribi

Old Hand

Points: 315
More actions
October 22, 2001 at 2:56 am

#77620

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?

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

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

neiljacobson SSCommitted Points: 1717 More actions · Answer 1

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 Old Hand Points: 315 More actions · Answer 2

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 Old Hand Points: 315 More actions · Answer 3

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: 736105 More actions · Answer 4

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: 5472 More actions · Answer 5

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.