Forum Discussion

dp3450's avatar
dp3450
Copper Contributor
Nov 28, 2024

Issue with importing json file

I am using the following script to read data from a json file and import into SQL server. Data import works without any issue. However, I noticed that when the json file contains special characters (Ex: è) is replaces it with è
I already saved the json file with UTF-8.
Is there anyway to import the actual character?

drop table if exists #Test3
CREATE TABLE #Test3 (
    EMP_COMMON_FULL_NAME NVARCHAR(500),
    EMP_COMMON_PRIMARY_JOB NVARCHAR(500),
    PEOPLE_EMAIL NVARCHAR(500),
    EMP_COMMON_PRIMARY_ORG NVARCHAR(500),
    PEOPLE_BADGE_NUMBER NVARCHAR(500),
    PEOPLE_EXPECTED_WEEKLY_HOURS NVARCHAR(500),
    PEOPLE_EXPECTED_DAILY_HOURS NVARCHAR(500)
);

DECLARE @json NVARCHAR(MAX);
SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'F:\PowerBI\json\file2.json', SINGLE_CLOB, CODEPAGE = '65001') AS jsonFile;
INSERT INTO #Test3 (
    EMP_COMMON_FULL_NAME,
    EMP_COMMON_PRIMARY_JOB,
    PEOPLE_EMAIL,
    EMP_COMMON_PRIMARY_ORG,
    PEOPLE_BADGE_NUMBER,
    PEOPLE_EXPECTED_WEEKLY_HOURS,
    PEOPLE_EXPECTED_DAILY_HOURS
)
SELECT
    JSONData.EMP_COMMON_FULL_NAME,
    JSONData.EMP_COMMON_PRIMARY_JOB,
    JSONData.PEOPLE_EMAIL,
    JSONData.EMP_COMMON_PRIMARY_ORG,
    JSONData.PEOPLE_BADGE_NUMBER,
    JSONData.PEOPLE_EXPECTED_WEEKLY_HOURS,
    JSONData.PEOPLE_EXPECTED_DAILY_HOURS
FROM OPENJSON(@json, '$.data.children')
WITH (
    EMP_COMMON_FULL_NAME NVARCHAR(500) '$.attributes[0].value',
    EMP_COMMON_PRIMARY_JOB NVARCHAR(500) '$.attributes[1].value',
    PEOPLE_EMAIL NVARCHAR(500) '$.attributes[2].value',
    EMP_COMMON_PRIMARY_ORG NVARCHAR(500) '$.attributes[3].value',
    PEOPLE_BADGE_NUMBER NVARCHAR(500) '$.attributes[4].value',
    PEOPLE_EXPECTED_WEEKLY_HOURS NVARCHAR(500) '$.attributes[5].value',
    PEOPLE_EXPECTED_DAILY_HOURS NVARCHAR(500) '$.attributes[6].value'
) AS JSONData;

SELECT * FROM #Test3;

 

{
    "metadata": {
        "numNodes": "500",
        "metadataKey": "fewfewf-sdvrv-evfewsv",
        "cacheKey": "fewfewf-sdvrv-evfewsv",
        "currencyCode": "NONE",
        "totalNodes": "500",
        "totalElements": "500"
    },
    "data": {
        "key": {
            "ROOT": "-1"
        },
        "coreEntityKey": {},
        "attributes": [],
        "children": [
            {
                "key": {
                    "PEOPLE": "67648"
                },
                "coreEntityKey": {
                    "EMP": {
                        "id": "11111"
                    }
                },
                "attributes": [
                    {
                        "key": "EMP_COMMON_FULL_NAME",
                        "alias": "Name",
                        "rawValue": "Sam, Rogers",
                        "value": "Sam, Rogers"
                    },
                    {
                        "key": "EMP_COMMON_PRIMARY_JOB",
                        "alias": "Primary Job",
                        "rawValue": "Accountant",
                        "value": "Accountant"
                    },
                    {
                        "key": "PEOPLE_EMAIL",
                        "alias": "Email Address",
                        "rawValue": "email address removed for privacy reasons",
                        "value": "email address removed for privacy reasons"
                    },
                    {
                        "key": "EMP_COMMON_PRIMARY_ORG",
                        "alias": "Location",
                        "rawValue": "1ère Inc/1ère Inc",
                        "value": "1ère Inc/1ère Inc"
                    },
                    {
                        "key": "PEOPLE_BADGE_NUMBER",
                        "alias": "Active Badge Number",
                        "rawValue": "1234",
                        "value": "1234"
                    },
                    {
                        "key": "PEOPLE_EXPECTED_WEEKLY_HOURS",
                        "alias": "Weekly Hours",
                        "rawValue": "35.75",
                        "value": "35.75"
                    },
                    {
                        "key": "PEOPLE_EXPECTED_DAILY_HOURS",
                        "alias": "Daily Hours",
                        "rawValue": "7.8",
                        "value": "7.80"
                    }


                ],
                "children": [],
                "summaryListDisplay": [],
                "rootEntity": "PEOPLE",
                "customProperties": {}
            },
            {
                "key": {
                    "PEOPLE": "22222"
                },
                "coreEntityKey": {
                    "EMP": {
                        "id": "22222"
                    }
                },
                "attributes": [
                    {
                        "key": "EMP_COMMON_FULL_NAME",
                        "alias": "Name",
                        "rawValue": "F3irst1, Last1",
                        "value": "F3irst1, Last1"
                    },
                    {
                        "key": "EMP_COMMON_PRIMARY_JOB",
                        "alias": "Primary Job",
                        "rawValue": "Cl3erk",
                        "value": "Cl3erk"
                    },
                    {
                        "key": "PEOPLE_EMAIL",
                        "alias": "Email Address",
                        "rawValue": "email address removed for privacy reasons",
                        "value": "email address removed for privacy reasons"
                    },
                    {
                        "key": "EMP_COMMON_PRIMARY_ORG",
                        "alias": "Location",
                        "rawValue": "1ère 3Inc/1ère Inc",
                        "value": "1ère 3Inc/1ère Inc"
                    },
                    {
                        "key": "PEOPLE_BADGE_NUMBER",
                        "alias": "Active Badge Number",
                        "rawValue": "23222",
                        "value": "23222"
                    },
                    {
                        "key": "PEOPLE_EXPECTED_WEEKLY_HOURS",
                        "alias": "Weekly Hours",
                        "rawValue": "30.0",
                        "value": "30.00"
                    },
                    {
                        "key": "PEOPLE_EXPECTED_DAILY_HOURS",
                        "alias": "Daily Hours",
                        "rawValue": "30.0",
                        "value": "30.00"
                    }






                ],
                "children": [],
                "summaryListDisplay": [],
                "rootEntity": "PEOPLE",
                "customProperties": {}
            }
        ],
        "summaryListDisplay": [],
        "rootEntity": "ROOT",
        "customProperties": {}
    }
}

 

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    1. Save your json file with format UTF-16, instead of UTF-8.
    2. Open json file with parameter SINGLE_NCLOB instead of SINGLE_CLOB. SINGLE_CLOB returns content as ASCII string(varchar(max)), SINGLE_NCLOB returns UNICODE string(nvarchar(max))

Resources