SQL Server importing strange characters despite specifying nvarchar

[ions]

Hello Access Forum,

I imported a .csv that contains special Unicode characters using the Import Flat File wizard from SSMS. The .csv shows the Unicode characters properly but the output of the import in SQL Server does not. Please see the below screenshots.

I have attached a sample .csv file.

Thank you for your feedback.

1) .csv shows Unicode characters properly

2) I specify nvarchar for all DataTypes

3) Unicode does not appearing properly in SQL Server or MS Access Link.

 

[Isaac]

double check the codepage property
after first determining what actually IS the codepage of your data source

 

[ions]

Thank you Isaac.

The codepage feature is available in the Import Data Wizard. Unfortunately, it's not available in the Import Flat File Wizard which is much simpler. That being said I am getting an error using the Import Data Wizard. I decided to import into MS Access and then use the SQL Server Migration Assistant (SSMA) Tool.

 

[sonic8]

That being said I am getting an error using the Import Data Wizard.

Did you bother to read the error message?
It's telling you the problem is that the file you are trying to import contains a character that is not included in the target codepage.

Did you ever verify that the file you assume to be Unicode encoded is actually encoded this way?

 

[tvanstiphout]

Notepad++ shows it is an ANSI file, *not* Unicode.
Also, when using the Hex editor, you can see that it is one byte per character, *not* Unicode.
You have several options, including going back to the source of this file and ask them for a Unicode file. Or use importing tools that can work with ANSI files and the correct codepage.

One such tool is Access > External Data > New Data Source > From File > Text file

 

[Isaac]

I assumed you were using the normal import/export data wizard. Which has what I mentioned I believe. Definitely use this not some shortcut in that right click menu you pictured.

You should either use that or just fire up data tools and start an SSIS package cuz it'll definitely be configurable there

 

[ions]

Did you bother to read the error message?
It's telling you the problem is that the file you are trying to import contains a character that is not included in the target codepage.

Did you ever verify that the file you assume to be Unicode encoded is actually encoded this way?

The file contains accents in the Province Field as shown in the first post. Waste Class is a field that does not contain any special characters but is longer than 255 so that is probably the truncate message. I was in a rush Sonic so I just decided to go the route that is slower but worked using SSMA.

 

[ions]

Notepad++ shows it is an ANSI file, *not* Unicode.
Also, when using the Hex editor, you can see that it is one byte per character, *not* Unicode.
You have several options, including going back to the source of this file and ask them for a Unicode file. Or use importing tools that can work with ANSI files and the correct codepage.

One such tool is Access > External Data > New Data Source > From File > Text file

Thank you Tom. I assumed it was Unicode due to the Accent on the e and SQL Server not being able to import it using the Flat File import Wizard. I see now that the e with an accent is still part of the ANSI character set. Yes, MS Access link can process the file correctly and I used SSMA to import into SQL Server.

PS. I am surprised that the Flat File import couldn't process it if it is ANSI. Out of curiosity, any explanation why not?

 

[ions]

I assumed you were using the normal import/export data wizard. Which has what I mentioned I believe. Definitely use this not some shortcut in that right click menu you pictured.

You should either use that or just fire up data tools and start an SSIS package cuz it'll definitely be configurable there

Thanks Isaac. The Import Flat File was recommended by this SQL Server teacher on YouTube(URL below), who has courses on Udemy. He advises to use the Flat File Import for .csv files. He does cover the Import Data wizard for Excel files later on in the video.

Import Tutorial

 

[Isaac]

Thanks Isaac. The Import Flat File was recommended by this SQL Server teacher on YouTube(URL below), who has courses on Udemy. He advises to use the Flat File Import for .csv files. He does cover the Import Data wizard for Excel files later on in the video.

Import Tutorial

Use the one I recommended and paid attention to the properties page that I originally mentioned that's where you need to fix it

 

[ions]

Understood. Thanks everyone.