April 21, 2014 at 7:00 pm
Hi,
I am trying to import CSV files into my table
I have three columns in my table.
TableName : Keys
key nvarchar(50)
English nvarchar(4000)
Spanish nvarchar(4000)
My logic:
BULK INSERT Keys
FROM 'F:\Keys.csv'
with ( FIELDTERMINATOR =',', FIRSTROW = 2,CODEPAGE = 1252);
the reason i am using codepage=1252 is to insert Spanish text.
I am able to do the bulk insert. I could see some row values of English column has double quotes and some text of the row cut and moved with Spanish Text. Not sure why.
Any suggestion please how to insert exactly what i have in my CSV file.
Thanks in advance
April 22, 2014 at 1:14 am
You should probably use
DATAFILETYPE = 'widechar'
with the BULK INSERT statement due to having unicode data, as per http://msdn.microsoft.com/en-us/library/ms188289%28v=sql.105%29.aspx (for using different code pages)
Also the requirements for bulk inserts from a CSV as the source are listed here:
http://msdn.microsoft.com/en-us/library/ms188609.aspx#sectionToggle0
Are all the fields terminated with double quotes "data"?
Can you quote a line from the csv that is causing issues?
April 22, 2014 at 5:19 am
Hi Andrew,
Thanks for the reply.i did analyse further and seems it's problem with the CSV file. When i convert my excel data to CSV and if i open the CSV in notepad i could see the unwanted double quotes. but the when i open the CSV in excel it doesn't have extra quotes. Am confused here how to come out from this problem. Did anyone already faced this issue and have solution. please help me on this
April 22, 2014 at 5:32 am
Found the issue. It's problem with my text. it has some extra comma's. Thanks.
April 22, 2014 at 9:39 am
Extra comma's where? Do you mean there were extra fields in the text file that had empty string values? Else the purpose of the double quotes is to denote any and all characters, including commas, within the quotes as a valid character and thus part of that string.
----------------------------------------------------
April 22, 2014 at 1:30 pm
MMartin1 (4/22/2014)
Extra comma's where? Do you mean there were extra fields in the text file that had empty string values? Else the purpose of the double quotes is to denote any and all characters, including commas, within the quotes as a valid character and thus part of that string.
No, I guess the problem is that some values contain commas and bulk insert is interpreting them as field terminators. A format file would help with this problem. Words in spanish do not require unicode values (unless you're trying to import something from the XIX century).
To help you get started on format files and other good practices when using bulk insert, you could read this article: http://www.sqlservercentral.com/articles/BCP+%28Bulk+Copy+Program%29/105867/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply