Json import and unicode?

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  November 9, 2018 at 6:48 am

  #409299

  Hey

  I am import json data into a MSSQL database, running on linux.
  In the json file, the special characters is okay, but when importing them,the special characters gets messed up.
  DECLARE @thesql nvarchar(1000)
        SET @thesql = '
        SELECT BulkColumn,''filenamecrap'', GETDATE()
        FROM OPENROWSET(BULK ''/var/www/html/dbs/cronfiles/files/thefile.json'', SINGLE_CLOB) as x';
        EXEC(@thesql)

  I have tryed changing the SINGLE_CLOB to SINGLE_NCLOB, but that just give me this error:
  "SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode."
  When opening the json file in notepadd++, it says the encoding is UTF-8

  Anybody that can help me get this fixed? The json file is downloaded trough php and curl.

  Please enlight me - unicode and encoding stuff always confuses me:)

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 9, 2018 at 9:06 am

  #2012981

  tommy 41661 - Friday, November 9, 2018 6:48 AM

  Hey

  I am import json data into a MSSQL database, running on linux.
  In the json file, the special characters is okay, but when importing them,the special characters gets messed up.
  DECLARE @thesql nvarchar(1000)
        SET @thesql = '
        SELECT BulkColumn,''filenamecrap'', GETDATE()
        FROM OPENROWSET(BULK ''/var/www/html/dbs/cronfiles/files/thefile.json'', SINGLE_CLOB) as x';
        EXEC(@thesql)

  I have tryed changing the SINGLE_CLOB to SINGLE_NCLOB, but that just give me this error:
  "SINGLE_NCLOB requires a UNICODE (widechar) input file. The file specified is not Unicode."
  When opening the json file in notepadd++, it says the encoding is UTF-8

  Anybody that can help me get this fixed? The json file is downloaded trough php and curl.

  Please enlight me - unicode and encoding stuff always confuses me:)

  Thanks

  Could you upload the file you are working with?  I am assuming from the name it is just a test file with some test json.

• tommy 41661

  SSC Veteran

  Points: 274

  More actions

  November 9, 2018 at 9:58 am

  #2012988

  Not a test file, just cleaned up the scripts from company related stuff;)

  And so will i do with the json file, cant upload a json file. But the content is this:
  {"city":[{"id":1004998120,"name":"Zürich}]}
  Its the name Zürich, that I am having problems with,weird characters are turning up in the database when importing