bcp and decimals

[Brad Beebe]

I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???

[Tom Goltl]

Brad,
Have you thought about loading the data from the text file and then in query analyzer multiplying that everything in that row by .01 (123455 * .01 = 1234.55). If there is other data in the table already I would create a load table and then export that data into the other field.

Tom



------------
Brad Beebe at 10/25/00 4:06:23 PM


I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???

[MAK]

Why can't you use DTS and use transformation scripts or a sql statement to process your change? Simpler than BCP.

-MAK


------------
Tom Goltl at 10/26/00 8:50:13 AM

Brad,
Have you thought about loading the data from the text file and then in query analyzer multiplying that everything in that row by .01 (123455 * .01 = 1234.55). If there is other data in the table already I would create a load table and then export that data into the other field.

Tom



------------
Brad Beebe at 10/25/00 4:06:23 PM


I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???