Bulk load data conversion error from csv file to SQL

Question

I have this problem inserting my data from csv file to my SQL database. I just don't get it why there is a error when i already replace some unwanted characters. it should be able to insert but i got this error.

"Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 13 (creditLimit)."

this is my create table:

CREATE TABLE Customers(
    customerNumber integer NOT NULL,
    customerName varchar(50) NOT NULL,
    customerLastName varchar(50) NOT NULL,
    customerFirstName varchar(50) NOT NULL,
    Phone varchar(50) NOT NULL,
    addressLine1 varchar(50) NOT NULL,
    addressLine2 varchar(50) NULL,
    city varchar(50) NOT NULL,
    [state] varchar(50) NULL,
    postalCode varchar(15) NOT NULL,
    country varchar(50) NOT NULL,
    salesRepEmployeeNumber integer NOT NULL,
    creditLimit double precision NOT NULL
    PRIMARY KEY(customerNumber) );

this is the first few lines of my csv file:

first line- 103,Atelier graphique,Schmitt,Carine ,40.32.2555,54 rue Royale,NULL,Nantes,NULL,44000,France,1370,21000

second line- 112,Signal Gift Stores,King,Sue,7025551838,8489 Strong St.,NULL,Las Vegas,NV,83030,USA,1166,71800

im not sure why is there error can you please help me?

are you using mysql or sql-server? please don't tag both if the question only belongs to one of them. — user4622594
– user4622594, Commented Jul 10, 2015 at 7:00
What bulk load tool are you using - BCP? What command line and definition file are you using? — Ben
– Ben, Commented Jul 10, 2015 at 7:04
BULK INSERT Customers FROM 'C:\Users\Student\Desktop\BIca1folder\CustomersBI.csv' WITH (fieldterminator=',', rowterminator='/n') @HoneyBadger — XuanYi Leon
– XuanYi Leon, Commented Jul 10, 2015 at 7:29
In the datafile you send there is a 21000.00, see my previous comment. Since you need to heavily edit the file (change decimal . to , or remove all other dots, and change field seperator as well) you may need to write some tooling to pre-process the file. — HoneyBadger
– HoneyBadger, Commented Jul 10, 2015 at 8:05

PrfctByDsgn · Accepted Answer · 2015-07-10 09:33:40Z

1

I think it's because your rowterminator is wrong ... you should use \n not /n ...

answered Jul 10, 2015 at 9:33

PrfctByDsgn

1,0621 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bulk load data conversion error from csv file to SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related