import from CSV - special chars
-
My data csv file sometimes comes in with special characters and French accents too (è ë à ç )
And then this special apostrophe in surnames e.g. O’Brien.
They go in the database as this: O’brien
I am using SSIS for import. Where do I need to make changes to allow these special chars?
The tables have standard varchar Name fields.
Thank you,
V
-
that has to do with what the encoding of the files are, the encoding defined on your SSIS package file definition (and potentially on any transforms you may have on your dataflow) and on your database.
as for what you say goes to your database - how you see them its going to depend on what application you are using to view that data. Again encoding (of database and application) will have a play at that.
-
Thanks, Frederico.
The front-end CRM sees exactly what i see on SQL SSMS too.
e.g. O’brien
The CSV file when I open them in Notepad++, they are in UTF-8 encoding, hence they look fine there.
So, it is just file manager source -SSIS where this needs changing?
What encoding will work for most special characters?
V
-
In SQL Server UTF-8 collation is only available with SQL2019.
With SQL2016, you might be able to get away with importing into a varchar with a French collation. (eg French_100_CI_AI) If that does not work you will have to use nvarchar.
-
NVARCHAR is not needed for french characters, neither is a french collation (which does affect ordering but not much more). but SSIS being very picky will require NVARCHAR on destination column, or a transformation on the dataflow to convert from NVARCHAR to VARCHAR
your SSIS file definition will likely need to change as well - see codepage below.
this was extracted from one of my packages that loads UTF-8 files onto staging tables defined as nvarchar - and it is afterwards transferred to varchar columns on final tables without loosing the contents.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply