Forum Discussion
dp3450
Nov 28, 2024Copper Contributor
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": {}
}
}
- rodgerkongIron Contributor
- Save your json file with format UTF-16, instead of UTF-8.
- 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))