Tuesday, August 27, 2013

How to import unicode characters from Excel to SQL Server

I got a complaint this monring

when the user run the import application to extract excel data to SQL Server.  the data did not import properly since the data contains french characters.

in the excle spreadsheet

LAVALLÉE-BRIEN          

in SQL Server

LAVALLÉE-BRIEN          
 
you can find the specific identifier for each encode in .net from the page below.

Code Page Identifiers


My excel is Unicode delimited format file. so i will have to use CharacterSet=65001 to represent that file format . Warmming if you use CharacterSet=Unicode, you will see the data table will scremup in the debug window.

here is the solution

  string ConnectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0} ; 
Extended Properties=""Text; HDR=NO;FMT=Delimited; CharacterSet=65001;IMEX=1;"";"
Excle File with absolute path);
 
                         







No comments:

Post a Comment