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).


.

Relevant Pages

  • Re: Import large CSV file data into Oracle Table
    ... into Oracle Table using a Bulk Insert. ... I'm not going to do all of your work for you, but the BULK INSERT ... command options that will describe the format of the input file, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Code Efficiency Suggestions
    ... - the input file format ... - the Access table info (table name, field names, data types, and how ... each field is derived from the input file fields) ... Prev by Date: ...
    (microsoft.public.excel.programming)
  • Re: pl/sql table and commit
    ... I would also suggest using a BULK COLLECT instead of cursor LOOP, ... FETCH rowtmp BULK COLLECT INTO mytab_tmp; <-- error here EXIT WHEN rowtmp%NOTFOUND; ... I both declare my own data types and use ...
    (comp.databases.oracle.server)
Loading