Avatar of Amanda Walshaw
Amanda Walshaw
Flag for Australia asked on

sql 2008 insert table - data conversion error (truncation)

insert issues for sql bulk insert, inserting by csv file
TRUNCATE TABLE dbo.NaturalAccount

BULK

INSERT dbo.NaturalAccount

FROM '\\myserver\myfolder\NATURALACCOUNT.CSV'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW=1

)

GO

getting the error
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 78, column 4 (Account_type).
Msg 4863, Level 16, State 1, Line 2

 
Please find attached error log and csv file I am trying to upload.  It only happens on certain lines and the data is fine.  the column is varchar 3. it will always contain 3 numericals ie 100, 150, 160

I would appreciate your help here.  Please find attached files.
error.txt
NaturalAccount.csv
Microsoft SQL Server

Avatar of undefined
Last Comment
Habib Pourfard

8/22/2022 - Mon
SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Habib Pourfard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lowfatspread

yes you have some "embedded" comma in the data especially in column 3....
Habib Pourfard

I have converted csv file to excel and problem solved. the excel file is attached.
NaturalAccount.xls
Amanda Walshaw

ASKER
Hello pourfard have done this but now getting the error

Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 1, column 1 (Code).
Msg 4866, Level 16, State 8, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 13. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Still using the script

TRUNCATE TABLE dbo.Fin_DIM_NaturalAccount
BULK
INSERT dbo.Fin_DIM_NaturalAccount
FROM '\\myserver\myfolder\NATURALACCOUNT.xls'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW=1
)
GO
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.