November 28, 2024 at 1:13 am
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"
}
]
November 28, 2024 at 10:43 am
one way - other is to have the file as a valid SQL Server unicode file - there are quirks about it.
drop table if exists Test2
CREATE TABLE test2 (record nvarchar(max)
);
drop table if exists Test3
CREATE TABLE test3 (
UserID INT PRIMARY KEY,
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Email NVARCHAR(100),
DateOfBirth DATE
);
DECLARE @json VARCHAR(MAX);
bulk insert test2 from 'c:\temp\test2.json'
with (codepage = '65001')
select @json = STRING_AGG(record, '')
from test2
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
November 28, 2024 at 4:38 pm
Thanks for the response. Much appreciated. This worked with the testing I did with json (I am new to json files).
Now I received the real file from the 3rd party system and the structure is totally different. Modified the code to match it and ran the script. Got empty results. Could you tell me how to fix this please?
I have attached the sql code and json file.
November 28, 2024 at 6:58 pm
My JSON handling is pretty dodgy, but the following query gets you part of the way.
DECLARE @x NVARCHAR(MAX)
= N'{
"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": "ssssaaa@aaa.com",
"value": "ssssaaa@aaa.com"
},
{
"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": "afe3wfwef@aaa.com",
"value": "afew3fwef@aaa.com"
},
{
"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": {}
}
}';
SELECT People = j2.Value
,j4.*
,RowNum = ROW_NUMBER () OVER (PARTITION BY j2.Value ORDER BY(SELECT NULL))
FROM OPENJSON (@x, '$.data.children') j1
CROSS APPLY OPENJSON (j1.Value, '$.key') j2
CROSS APPLY OPENJSON (j1.Value, '$.attributes') j3
CROSS APPLY OPENJSON (j3.Value) j4
WHERE j4. IN ( 'key', 'value' )
ORDER BY People
,RowNum;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 28, 2024 at 7:13 pm
Thanks for the reply. The JSON file contain over 500 records. So I need to point it to the filename each time.
After doing some research, I modified the code and it works for the first 34 records. After that I get the error.
Msg 13609, Level 16, State 5, Line 25
JSON text is not properly formatted. Unexpected character '"' is found at position 16. I think this is due to the bulk insert statement. I ran the same script on another sql server and this time it processed 52 rows and gave the error.
Not sure how to fix this.
drop table if exists #Test2
CREATE TABLE #Test2 (record nvarchar(max));
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);
BULK INSERT #Test2 FROM 'd:\json\File2.json'
WITH (CODEPAGE = '65001');
SELECT @json = STRING_AGG(record, '') FROM #Test2;
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') -- Traverse the 'children' array
WITH (
EMP_COMMON_FULL_NAME NVARCHAR(500) '$.attributes[0].value', -- Extract from the attributes array of each child
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;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply