char conversion Issue 2

[see123]

I am importing a txt file into MS SQL table. One of the column in txt file has special char as í. This char is loading into SQL as f.

I am using format file to load the data.

Any Ideas why this í char is converting as f.

thanks in Advance

 

[gmmastros]

I'm 99% certain you are running in to a collation/code page issue.

For example:

Select ASCII(N'í' Collate SQL_Latin1_General_Cp850_CS_AS)
Select ASCII(N'í' Collate SQL_Latin1_General_Cp1250_CS_AS)

Notice that the only thing different is the collation (different code pages), but the output is different for each character.

It is possible to identify the collation in format file, and columns in tables have a collation. I suggest that you make them match. To find the collation of the column in the table...

Select Table_Name, Column_Name, Collation_Name 
From   Information_Schema.Columns
Where  Table_Name = 'YourTableNameHere'
       And Column_Name = 'YourColumnNameHere'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[see123]

Thanks for the quick reply George.

I used SQL_Latin1_General_Cp1250_CS_AS for my table column which converted this column as ?. This will work. Is there a way I can convert this as pipe |.

Once again thanks for the quick response

 

[gmmastros]

Wait a minute. Did you change the collation for the column? I strongly recommend against doing that.

What I meant to say earlier was that the collation in the format file should match the collation of the table column. Not the other way around.

If you want to replace characters with another character, then I usually recommend you create a temporary table in your database that you actually import in to. Do your replaces in this temporary table, and then copy the data from the temp table to the real table. Once the data has been copied, you can delete it from the temp table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

Thanks George! ==> [purple]*[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[see123]

Actually this table is temp table(Staging) to load the data from txt file. I use this table to load daily files and manipulate the data to load into my main tables.


thanks for the suggestions......