Re: Bulk insert data with decimal point

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@xxxxxxxxxxxxx> wrote in message
news:%qAte.49744$Ph4.1337809@xxxxxxxxxxxxxxxxxxxxxxxxxx
>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: Bulk insert data with decimal point
    ... > have you try to create an FMT file? ... > have you try to use DTS to do this job? ... >> BULK INSERT MYDB.me.MY_TABLE ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Bulk Insert comma seperated text file with text qualifiers
    ... In the Bulk Insert Task I choose a delimited file and also text qualifiers. ... The FMT file I created using the Bulk Insert Task looks like this: ... > format file OR if you use the Data Transformation task it will strip them ...
    (microsoft.public.sqlserver.dts)
  • Problems with DTS in SQL 2005
    ... I'm try to edit/run DTS packages against a SQL 2005 ... I installed the download to support the DTS designer, ... occasionally the designer still prompts for the download and then ... It looks like DTS is ignoring the .fmt file and trying ...
    (microsoft.public.sqlserver.tools)
  • Re: Problems with DTS in SQL 2005
    ... SQL 2005 seems to have a problem importing 2000 DTS ... I installed the download to support the DTS designer, ... get the task to recognize the .fmt file specified. ...
    (microsoft.public.sqlserver.tools)
  • Re: copying word documents into sqlserver
    ... Microsoft OLE DB Provider for SQL Server error '80040e14' ... Cannot perform bulk insert. ... I'm not sure that I understand the rest of what you are saying, but my fmt file looks something like this: ...
    (microsoft.public.sqlserver.programming)
Loading