Bulk CSV File upload

hi

I am BULK  uploading a CSV file into q SQL 2005 table

I am getting the following error
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 2 (name_1).

The reason is that one of the fields I am uploading contains the character / in the middle of a text field  - when I remove this character the upload is fine - I wish to upload this text field with the / character - how can I do this ?

my code is

Bulk Insert #File01

from 'c:\test.csv'

with
(
      Fieldterminator = ',' ,
      FIRSTROW = 2,
      rowterminator = '\n'
)





regards
Mike
MECR123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron TomoskySD-WAN SimplifiedCommented:
In the field name there is a /?
Or in the data somewhere?
0
DcpKingCommented:
You need to "escape" the unwelcome character. I believe putting a backslash in front of it will help

Hth

Mike
0
Mark WillsTopic AdvisorCommented:
@aarontomosky

Yes, there is a "/" in the middle of the name field, the Asker said as much in the Question...


@mecr123

Is the rowterminator really just a "\n" would expect rowterminator = '\r\n'

Have you tried "openrowset" ? Often a good test to see if it is simply readable in the first instance.

What if you insert into a copy of #File01 with a very large column for name_1, or is there plenty of length ?

 Cannot recall a slash being a problem, will test out a couple of things...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Hi,

I just whipped up a small test and didn't get the error, so, double check those lengths in #file_1 and increase the field size...

CREATE TABLE #ee_CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40))
GO

/* select the two rows below and save into c:\ee\testcsv.csv (or whatever path + file you like)
1,mark,w/lls
2,m/a/r/k,wills
*/

BULK INSERT #ee_CSVTest
FROM 'c:\ee\testcsv.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM #ee_CSVTest
 GO

/* returns
1	mark	w/lls
2	m/a/r/k	wills
*/

Open in new window


And, double check in Excel. Double clisk on the CSV and make sure columns are aligned correctly etc... At this stage, must be field length in your table.
0
MECR123Author Commented:
I am going to accept this solution - I had to extend my field length and this has resolved the issue - The field length was mentioned in this solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.