ASCII Character Translation issue into SQL Server 2008 R2

• AP24

  Old Hand

  Points: 312

  More actions

  October 2, 2014 at 3:45 am

  #290623

  Hello All,

  Good Morning All!

  My First question on here!

  I am having trouble loading Extended ASCII Characters into my SQL Server 2008(R2) DB.

  The source file is an .xlsx,

  PROCESS:

  I remove unwanted columns, save as .csv and then I open the .csv in NOTEPAD and then SAVE as "UNICODE".

  Finally I BULK INSERT into a table.

  I used this method because leaving the .csv in NON-Unicode format meant after loading the data and viewing in my DEV environment(website) the characters came out incorrectly as ? or upside down. ?

  I have also used the SSIS approach but to no avail.

  The Characters I am having trouble with are:

  a) –this is U+2013 : EN DASH

  b) ’this is U+2019 : RIGHT SINGLE QUOTATION MARK

  INFO:

  i) All Columns in the table are NVARCHAR

  ii) Bulk Insert settings with or without.... "datafiletype=''widechar'',

  I can work around this in the database with update\replace script for the specific characters in the column BUT I would like to solve the issue at SOURCE if possible.

  I hope there is a SIMPLE solution to this. 😀

  ANY IDEAS?

• JohnCurrie

  SSC Veteran

  Points: 269

  More actions

  October 3, 2014 at 6:15 am

  #1749946

  Have you tried adding

  with (codepage = 'ACP')

  to your bulk insert command?

• AP24

  Old Hand

  Points: 312

  More actions

  October 3, 2014 at 6:39 am

  #1749956

  Hi John,

  Yep, I tried that also but sadly no luck there either.

  Thanks for your response. 🙂