March 28, 2002 at 6:56 am
I am trying to use Bulk Insert to import a text file, but I keep getting an error that the data will be truncated. The file is comma delimited, character data is surrounded by double quotes and numeric data is not. When running the bulk insert, sql server is trying to import the double quotes as well as the data. How do I tell bulk insert that character data has double quotes around it?
Thanks
Angela
Angela Beckwith
Angela Beckwith
March 28, 2002 at 10:47 am
From Books On-Line:
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
Why not use FIELDTERMINATOR = '","'
March 28, 2002 at 11:05 am
Because the first field in the file is character and it still thinks the first double quote is part of that field.
Angela Beckwith
Angela Beckwith
March 28, 2002 at 11:14 am
Yes and that won't work for non character data types either. Can you use DTS?
March 28, 2002 at 12:15 pm
Yes, I've already done it in DTS but I just wanted to figure out if it could be done with Bulk Insert. I think I figured it out though, I think you need to create a format file with bcp first. Thanks for the suggestions.
Angela Beckwith
Angela Beckwith
April 1, 2002 at 7:04 am
FIELDTERMINATOR [ = 'field_terminator' ]
Specifies the field terminator to be used for char and widechar data files. The default is \t (tab character).
Try FIELDTERMINATOR = '"'
But the format file builds based on the output which is why it will work. If all your data is the same it should work to keep this file. Or open in notepad and see if it tells what it is using as the field terminator.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply