How to retrieve German characters from a large CSV File into SQL Server 2017 script

Question

I have a CSV file including a list of employees, where some of them includes German characters like 'ö' in their names. I need to create a temp table in my SQL Server 2017 script and fill it with the content of the CSV file. My script is:

CREATE TABLE #AllAdUsers(
    [PhysicalDeliveryOfficeName] [NVARCHAR](255) NULL,
    [Name] [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DisplayName] [NVARCHAR](255) NULL,
    [Company] [NVARCHAR](255) NULL,
    [SAMAccountName] [NVARCHAR](255) NULL
)

--import AD users
BULK INSERT #AllAdUsers
    FROM 'C:\Employees.csv'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = ',',  --CSV field delimiter
        ROWTERMINATOR = '\n',   --Use to shift the control to next row
        TABLOCK
    )

However, even though I use "Nvarchar" variable type with the collation of "SQL_Latin1_General_CP1_CI", the German characters are not seem OK, for instance "Kösker" seems like:

"K├╢sker"

I've tried many other collations but couldn't find a fix for it. Any help would be very much appreciated.

Is the CSV file encoded using UTF-8? If so may try codepage 65001 (or ACP). See [this answer](https://stackoverflow.com/a/13997372/61305) from a long, long time ago. — Aaron Bertrand, Aug 19 '20 at 21:10
What was the encoding of the source file? What change to your BULK INSERT code fixed it? — Aaron Bertrand, Aug 20 '20 at 12:51

score 0 · Answer 1 · answered Mar 02 '23 at 11:21

0

First, convert the Encoding of your source CSV file to UTF-8 with Notepad++ and try to import it again.

It worked for me.

answered Mar 02 '23 at 11:21

Taner

1
1

that could be another solution, thanks for the suggestion, but it requires a manual procedure in the middle. The CSV file here was being generated by another application and I needed to read it immediately via a SQL job after another application stopped running. – Eray Balkanli Mar 03 '23 at 18:56

score 0 · Accepted Answer · answered Mar 03 '23 at 18:54

Adding codepage=65001 to the bulk insert query fixed the issue:

--import AD users
BULK INSERT #AllAdUsers
    FROM 'C:\Employees.csv'
    WITH
    (
        FIRSTROW = 2,
        CODEPAGE = '65001',
        FIELDTERMINATOR = ',',  --CSV field delimiter
        ROWTERMINATOR = '\n',   --Use to shift the control to next row
        TABLOCK
    )

How to retrieve German characters from a large CSV File into SQL Server 2017 script

2 Answers2