BULK INSERT PROBLEM for CAV file

Question

BULK INSERT PROBLEM for CAV file

pgainer

SSC Enthusiast

Points: 181
More actions
February 19, 2023 at 11:00 pm

#4151706

Hello SQL Server Community,
I'm not new to T-SQL but I have a simple problem that I have not been able to solve after several hours of research.
I have several CSV files that I want to import into SQL Server (v2008 R2)
I have created a temp table.
CREATE TABLE DataTMP
(
Col1 varchar(255) NOT NULL,
Col2 varchar(255) NOT NULL,
Col3 varchar(255) NOT NULL,
Col4 varchar(255) NOT NULL,
Col5 varchar(255) NOT NULL,
Col6 varchar(255) NOT NULL,
Col7 varchar(255) NOT NULL
)
I have used the following query
BULK INSERT DataTMP
FROM 'c:\data\File1.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'); -- I have also used 0x0a
When I run the query, I get the following error message
"Bulk load data conversion error (truncation) for row 1, column 7 (Col7)".
I loaded the file in a hex viewer and the columns are separated with a comma and each line is terminated with a hex 0a
The CSV file has 7 Columns [Transaction Date],[Post Date],[Description],[Category],[Type],[Amount],[Memo]
The Memo column is almost always NULL.
Does anybody see what I'm doing wrong?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

At first blush, it would appear that the memo field of the file has at least one line in the file where the text of the memo is wider than 255 characters. To temporarily eliminate that possibility, change column 7 to a VARCHAR(MAX) to at least get the file to import. You may also have to change the row terminator to 0x0a, as you did before but not sure here. Try both ways.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pgainer SSC Enthusiast Points: 181 More actions · Answer 2

pgainer

SSC Enthusiast

Points: 181

February 20, 2023 at 7:02 pm

#4152051

Thank you very much. The "MAX" worked.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

pgainer wrote:

Thank you very much. The "MAX" worked.

Awesome. Now, check the MAX(LEN() of the column so you can right-size it. It may still need to be a MAX but maybe not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pgainer SSC Enthusiast Points: 181 More actions · Answer 4

Thank you. I did resize the columns and the bulk insert properly imports the CSV files.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Awesome and thank you kindly for the feedback. Nicely done on your part, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)