After doing a load of CSV files, there are various words that are "written" to the database incorrectly.

Some examples:

  • Diã¡ria should be Diária
  • Crã©dito should be Crédito
  • Ligaã§ãµes should be Ligações
  • Usuã¡rio should be Usuário
  • Nãºmeros should be Números

Is there any way to translate the symbols to the correct characters?

I've done several tests with different collations and functions that could search the internet, but without success.

  • 5
    For char/varchar columns, the column collation must support the source characters and the code page used to parse the source file must also support the characters. – Dan Guzman Apr 25 at 11:06
  • 4
    Need more info. Data type and collation of the column, encoding type of the CSV file, a sample line from the file, actual code that performed "a load", and how/where you are validating the data. – Aaron Bertrand Apr 25 at 13:43
up vote 3 down vote accepted

there are various words that are "written" to the database incorrectly.

No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the characters are being written incorrectly to the data file. Either way, SQL Server is doing exactly what it is being asked to do.

This is a simple encoding issue. The data was originally exported as UTF-8, but then that UTF-8 encoded file was read into SQL Server as if it were an Extended ASCII file using Code Page 1252. There are three clues which indicate that this is the issue:

  1. Most characters come through correctly. The ones that don't are the accented characters.
  2. The characters that are incorrect are coming through as two characters instead of one.
  3. You mentioned that exporting to an "ASCII" file worked.

UTF-8 is a multi-byte encoding: it uses a different number of bytes depending on the character being encoded. The first 128 Code Points (U+0000 - U+007F), which contain the US English alphabet, all use 1 byte. Code Points above that range take 2 - 4 bytes. This is why some characters transferred between systems as expected: N (upper-case Latin "N") is 0x4E in UTF-8 as well as in Code Page 1252 (in fact, it's 0x4E across all 8-bit Code Pages supported by SQL Server). This is one of the reasons that UTF-8 is so popular. However, the accented characters are not 1 byte in UTF-8:

  • The á ( U+00E1 ) is encoded as two bytes in UTF-8: 0xC3 and 0xA1. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being à ( 0xC3 on Code Page 1252 ) and ¡ ( 0xA1 on Code Page 1252 ). Then, either you or your import process, lower-cased the à (probably because it was in the middle of a word) which is how you ended up with: Usuã¡rio.

  • The ú ( U+00FA ) is encoded as two bytes in UTF-8: 0xC3 and 0xBA. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being à ( 0xC3 on Code Page 1252 ) and º ( 0xBA on Code Page 1252 ). Then, either you or your import process, lower-cased the à (probably because it was in the middle of a word) which is how you ended up with: Nãºmero.

Your choices are:

  1. Encode the file as (Extended) ASCII using Code Page 1252 (as it is exported), and do not change how it is being read into SQL Server. (it sounds like you have already done this).

  2. Continue exporting the file using the UTF-8 encoding, but change how the file is being read into SQL Server by specifying that it is encoded as UTF-8. Please note that for anyone using BCP.exe, BULK INSERT, or OPENROWSET(BULK...), this option only became available starting in SQL Server 2016. The Code Page to use is 65001 (which usually means UTF-8 with Byte Order Mark, but I am not sure if SQL Server requires the Byte Order Mark in these cases).

We export the file to ASCII and it worked.

  • 3
    I wonder how 'Ligações' was exported. – ypercubeᵀᴹ Apr 25 at 17:31

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.