SQL Server Import Data from a Text File Part 4


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. 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. We do want to accept French characters, but we may have opened the door too wide. Did we?

Series Navigation<< SQL Server Import Data from a Text File Part 3

Leave a comment

Your email address will not be published. Required fields are marked *