How to write UTF-8 characters using bulk insert in SQL Server?

by

I came here before looking for a solution for bulk inserting special characters.
Didn’t like the workaround with UTF-16 (that would double the size of csv file).
I found out that you definitely CAN and it’s very easy, you don’t need a format file.
This answer is for other people who are looking for the same, since it doesn’t seem to be documented well anywhere, and I believe this is a very common issue for non-english speaking people. The solution is:
just add CODEPAGE=’65001′ inside the with statement of the bulk insert. (65001=codepage number for UTF-8).
Might not work for all unicode characters as suggested by Michael O, but at least it works perfect for latin-extended, greek and cyrillic, probably many others too.

Note: MSDN documentation says utf-8 is not supported, don’t believe it, for me this works perfect in SQL server 2008, didn’t try other versions however.

e.g.:

BULK INSERT #myTempTable 
FROM  'D:\somefolder\myCSV.txt'+
WITH 
    ( 
        CODEPAGE = '65001',
        FIELDTERMINATOR = '|',
        ROWTERMINATOR ='\n'
    );

If all your special characters are in 160-255 (iso-8859-1 or windows-1252), you could also use:

BULK INSERT #myTempTable 
FROM  'D:\somefolder\myCSV.txt'+
WITH 
    ( 
        CODEPAGE = 'ACP',
        FIELDTERMINATOR = '|',
        ROWTERMINATOR ='\n'
    );

More Related Contents:

  1. Bulk Insert Partially Quoted CSV File in SQL Server
  2. Create a view with ORDER BY clause
  3. How to run the same query on all the databases on an instance?
  4. SQL Update with row_number()
  5. SQL Server Bulk insert of CSV file with inconsistent quotes
  6. Is a single SQL Server statement atomic and consistent?
  7. How to Use UTF-8 Collation in SQL Server database?
  8. Connecting to MS SQL Server with Windows Authentication using Python?
  9. BULK INSERT with identity (auto-increment) column
  10. How to add xml encoding to xml Output in SQL Server
  11. determining the character set of a table / database?
  12. How to query for Xml values and attributes from table in SQL Server?
  13. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  14. Should every User Table have a Clustered Index?
  15. cross apply xml query performs exponentially worse as xml document grows
  16. Bulk insert with text qualifier in SQL Server
  17. SQL Server – defining an XML type column with UTF-8 encoding
  18. SQL Server Configuration Manager cannot be found
  19. Compute MD5 hash of a UTF8 string
  20. Sql Server replication requires the actual server name to make a connection to the server
  21. Cannot bulk load. Operating system error code 5 (Access is denied.)
  22. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  23. Convert from DateTime to INT
  24. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  25. How to Bulk Insert from XLSX file extension?
  26. SQL Server 2008 Spatial: find a point in polygon
  27. SQL Server – error when creating stored procedure
  28. Format SQL in SQL Server Management Studio
  29. Data generators for SQL server? [closed]
  30. sql geography to dbgeography?

Leave a Comment