2

I am trying to bulk insert a UTF-8 CSV file that I downloaded as that type from Google Drive, because Excel was not saving my CSV correctly.

I opened the Google Drive generated CSV file in notepad++ and went to View > Show Symbol > Show All Characters and I could see that it contained LF line feeds for the row terminator (correct me if I am wrong here)

So I tried the below and I don't get any records in the temp table. This works for other CSV files that are not UTF-8 when I use the default row terminator (i.e. '\r\n' when you don't specify one).

I have also tried '\t', '\r\n', '\r' & '\0' for the row terminators and with and without a data file type.. nothing seems to be working? is this to do with my field types in the temp table? or something else?

CREATE TABLE #TEMPResourceContents (
    [ResourceName] [nvarchar](250) NOT NULL,
    [Language] [nvarchar](250) NOT NULL,
    [Content] [nvarchar](max) NOT NULL
)
GO

BULK INSERT #TEMPResourceContents 
FROM 'C:\import-resources.csv'
WITH
(FIRSTROW = 2, DATAFILETYPE = 'widechar', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
GO

SELECT * FROM #TEMPResourceContents
Pricey
  • 5,799
  • 12
  • 60
  • 84

1 Answers1

4

By the way BULK INSERT doesn’t support UTF-8.

See Reference Link MSDN

See Reference Link

Community
  • 1
  • 1
Sutirth
  • 922
  • 10
  • 14
  • AH ok so widechar is likely to be UTF-16, so my file would have to be UTF-16 instead of UTF-8 :( from the sounds of that second link. I shall try that and see if it does not mess with my text. – Pricey Jan 02 '13 at 00:00
  • yup. as per discussed in second link it should be work. Try it. – Sutirth Jan 02 '13 at 00:11
  • I opened my .xls file and went to `save as` and selected the file type of `Unicode Text (*.txt)` and then changed the bulk insert to use that text file with the field terminator of `'\t'` instead. This fixes my issue with Chinese characters that were not importing correctly. Thanks for pointing me in the right direction. – Pricey Jan 02 '13 at 00:34