Unicode Issue

[SQLJoe]

I am importing a pipe-delimited text file into a table, but special characters (accent marks, tildes, etc.) are getting changed into math or non-English characters. I thought SQL handled this automatically. Do I control that during import, or is there a server level setting that needs to be changed?

Thanks!

 

[gmmastros]

text file into a table

SQL can handle unicode data just fine, but you need to do certain things. Specifically, the columns in this table should have a unicode capable data type.

In SQL there are several data types for strings.

char
nchar
varchar
nvarchar
varchar(max)
nvarchar(max)
text
ntext

The "n" versions of the data types accommodate unicode data. Can you check the data types in your table to make sure it is a unicode aware data type?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

I've tried both varchar and nvarchar with the same results. (?)

 

[gmmastros]

How are you importing this data? SSIS, BCP, Bulk Insert, etc...

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

I've done it a couple of ways; bulk insert as well as importing using the built-in utility in the Access front-end. It's a 2K8 box, but we can't use SSIS immediately, so I have to have some other method.

 

[gmmastros]

If you look at the data file using wordpad, do you see the special characters? I mean... it could be that the data is mungled before it gets in to SQL Server.

Also, with the Bulk Insert method, did you specify the DataFileType?

According to books on line, you'll want to use DATAFILETYPE='widechar'

so...

BULK INSERT TableName
FROM 'YourFileName.txt'
WITH DATAFILETYPE='widechar'

You'll probably want to specify other things in the WITH part, like RowTerminator and FieldTerminator.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

I have the RowTerminator and FieldTerminator already, but I'll try looking at it in wordpad, as well as using the widechar and see how it goes. Thanks!

 

[SQLJoe]

Apparently I don't have wordpad installed, but regardless, if I open it in Word, it prompts me to choose the text encoding. If I choose Windows (Default), the characters come through with no problems. If I choose MS-DOS, however, I have the same issues as before. (If I choose Other Coding: US-ASCII, then I get still other strange behavior).

I added the "DATAFILETYPE='widechar' but the problem persists.

?

 

[SQLJoe]

I'm googling some things to see if I can find answers as well, but so far no luck. It can't be this hard. People do this sort of thing all the time.

 

[SQLJoe]

Interesting find:

http://www.sqldev.org/sql-server-tools-general/bulk-insert-unicode-52190.shtml

"the solution was to convert the input file from UTF8 text encoding to ANSI text encoding"

Don't suppose there's a way to get SQL to do this automatically during import?