This part of our multi-part series discusses BULK INSERT. Here we focus in on this command. Microsoft’s Books Online has a detailed description of this command.

In this post we use an example. We will be importing a CSV text file into a table using the BULK INSERT command. CSV stands for comma separated values. The text file will contain French characters. The character we use is é. When these characters are imported, they are “corrupted” by SQL Server and stored as two symbols instead of one French character. The character é is Latin small letter e with acute. é

You will find the character é in the extended ASCII codes as the decimal value 233. Here is a web page that lists them.

As for importing these into a SQL Server table, you may opt for actually not importing these French characters and then checking for “bad characters” after. Here is a post at this site called SQL Server Finding Bad Characters.

Code Page

One solution to importing French characters is to specify a code page when you use BULK INSERT. If you use 65001 as the code page, you are using Unicode (UTF-8). Below is a fragment of code that works. Here we are able to import French characters.

SET @FileInputLocation = 'D:\Temp\frenchin.csv'; 
SET @sqlString = 'BULK INSERT ' + @DBName + '.[dbo].[Persons]
    FROM ' + '"' + @FileInputLocation + '"' +
    ' WITH
    (    
    CODEPAGE = ''65001'',
    DATAFILETYPE = ''char'',
    FIELDTERMINATOR = '','', 
    ROWTERMINATOR = ''0x0a'', 
    FIRSTROW = 1      
    )';
EXEC(@sqlString); 

Open the Door

We have opened up the door to accepting lots of characters with this solution. The codepage 65001 does refer to the UTF-8 character set. Vendors that use a code page system allocate their own code page number to a character encoding, even if it is better known by another name; for example, UTF-8 has been assigned page numbers 1208 at IBM, 65001 at Microsoft, and 4110 at SAP.