Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it[vbcol=seagreen]
>