Import data from json file with special characters

• don075

  SSC-Addicted

  Points: 423

  More actions

  November 28, 2024 at 1:13 am

  #4490151

  I am using the following script to import data from a json file. This file contains special characters for some names. Once imported, I noticed that sql server is replacing the special characters with some other ones. Is there away to import special characters without changing them? Any help is greatly appreciated.

  CREATE TABLE test3 (
      UserID INT PRIMARY KEY,
      FirstName NVARCHAR(100),
      LastName NVARCHAR(100),
      Email NVARCHAR(100),
      DateOfBirth DATE
  );
  
  DECLARE @json NVARCHAR(MAX);
  SELECT @json = BulkColumn
  FROM OPENROWSET(BULK 'F:\json\test2.json', SINGLE_CLOB) AS j;
  
  INSERT INTO test3 (UserID, FirstName, LastName, Email, DateOfBirth)
  SELECT 
      JSONData.UserID,
      JSONData.FirstName,
      JSONData.LastName,
      JSONData.Email,
      JSONData.DateOfBirth
  FROM OPENJSON(@json)
  WITH (
      UserID INT,
      FirstName NVARCHAR(100),
      LastName NVARCHAR(100),
      Email NVARCHAR(100),
      DateOfBirth DATE
  ) AS JSONData;
  
  
  select * from Test3
  
  drop table Test3

  Following is my json file.

  [
      {
          "UserID": 1,
          "FirstName": "John",
          "LastName": "Doe",
          "Email": "john.doe@example.com",
          "DateOfBirth": "1985-02-15"
      },
      {
          "UserID": 2,
          "FirstName": "Jane",
          "LastName": "ABCDère ",
          "Email": "jane.smith@example.com",
          "DateOfBirth": "1990-07-25"
      },
      {
          "UserID": 3,
          "FirstName": "Alice",
          "LastName": "Johnson",
          "Email": "alice.johnson@example.com",
          "DateOfBirth": "1992-10-30"
      }
  ]