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...[vbcol=seagreen]
> 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
>

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]
>

Bulk Insert and Decimal type

Hi there

I am trying to write a program which will bulk load data from a bcp file into a newly made database on the users PC.

I create the data from an existing DB using SQL-DMO BulkCopy.
I then load it into the users DB using "Bulk Insert " transact SQL.

It all works fine on SQL Server 2000. However on SQL Server 7.0 whenever the .bcp file is being loaded into a table with a field of type decimal, it throws an OLEDB stream error. Even when the .bcp file is empty.

I have tried exporting/importing the data as tab delimited and as native, but it seems to make no difference.

This has really got me stumped and I am running out of time. Can anyone help?

Thanks.

justinOK I just discovered from the Microsoft web site that there is a bug in SQL Server 7.0. Using Bulk Insert on a table that includes a default value for decimal or numeric data typed fields, throws an error.

There is no solution. It is incurable. The workaround is to "use bcp instead."

Programatically that would be an issue, so i will have to use DMO.