CODEWITHSUNDEEP
sqlsql-servervb.net
Desichele
Desichele

Reputation: 399

VB.net Bulk Insert

Its regarding 'Bulk Insert' sql command where I having problem regarding some French Accents. The 'à' or 'a acute' is showing up as n++ in database table after I insert them from a text file. And this is the only character that is having problem with current conversion. I tried to modify the command using 'WITH' clause and assigning value such as 'RAW' or '1252' or 'ACP' but none of them were able to retain this particular character. The database I am using is Sql Server 2008. Any help is greatly appreciated. Thanks.

FYI, I aint using any encode or decode technique. Just trying to put whatever on text file back into database.

Thanks for the response. However, changing the column type to 'nvarchar' from 'varchar' didnt actually solve the issue. Its now appearing as '�' in the database table column. Any idea people ?? Thanks a lot in advance

Upvotes: 0

Views: 1267

Answers (2)

user565869
user565869

Reputation:

As Bridge says, you'll need NVARCHAR. You'll also need to convert the file to UTF-16, as BULK INSERT does not play nice with UTF-8 even when you're using NVARCHAR.

See this question for more information.

There are several questions here regarding converting file encodings, but for the record this worked for me:

public static void  ConvertUtf8ToUtf16(string path, int bufferSize = 4096)
    {
    string  tempPath = path + ".tmp";

    char[]  buffer = new char[bufferSize];
    using (var input = File.OpenText(path))
    using (var output = new StreamWriter(tempPath, false, Encoding.Unicode))
        {
        int  index = 0;
        int  chars;
        while ((chars = input.Read(buffer, index, bufferSize)) > 0)
            output.Write(buffer, 0, chars);
        }

    // Swap the files
    File.Delete(path);
    File.Move(tempPath, path);
    }

Upvotes: 1

Jirka Hanika
Jirka Hanika

Reputation: 13529

Change the column type to nvarchar.

Upvotes: 0

Related Questions