Load JSON data obtained from a column of a CSV to populate a table SQL Server

Question

I am having problems when loading data that is in JSON in a table using SQL Server.

I have the following row from a file in CSV:

A: 15957
B: test_category_labeling_highlight_fordham_bbbbb
C: 116
D: 3864
E: 1
F: Policy Change
G: {"Change Type": {"selectedText": "Privacy Policy Last modified: June 30, 2015 (view archived versions)", "startIndexInSegment": 0, "endIndexInSegment": 79, "value": "Unspecified"} , "User Choice": {"selectedText": "Privacy Policy Last modified: June 30, 2015 (view archived versions)", "startIndexInSegment": 0, "endIndexInSegment": 79, "value": "Unspecified"}, " Notification Type ": {" selectedText ":" Privacy Policy Last modified: June 30, 2015 (view archived versions) "," startIndexInSegment ": 0," endIndexInSegment ": 79," value ":" General notice in privacy policy "} }
H: 6/30/15
I: https://www. google.com/intl/en/policies/privacy/
of which I am interested in columns G, H, I.

To load the data use

CREATE TABLE #tempTable1
(
a VARCHAR(max),
b VARCHAR(max),
c VARCHAR(max),
d VARCHAR(max),
e VARCHAR(max),
f VARCHAR(max),
g VARCHAR(max),
h VARCHAR(max),
i VARCHAR(max),
);

BULK INSERT #tempTable1
FROM 'C:\Users\file.csv'
WITH (FORMAT = 'CSV', ROWTERMINATOR = '\n', FIRSTROW = 2);

ALTER TABLE #tempTable1
DROP COLUMN a, b, c, d, e, f, h, i;
So far it does good for me, and I get a column with cells of JSON values ​​{...} in each row.

And now, I would have to somehow reconvert that generated table. The values ​​of columns h and I are the same, and only the JSON changes. To declare said JSON in a temporary variable I had tried

DECLARE @json varchar (max);

SELECT g INTO @json FROM #tempTable1;
but without success to later access its key values ​​with

INSERT INTO Data (G_1, G_2, E, F)
SELECT *
FROM OPENJSON (@json )
WITH (
G_1 VARCHAR(50) '$."Personal Information Type".value',
G_2 VARCHAR(100) '$."Purpose".value'
);

Answer 1

BULK INSERT #tempTable1
FROM 'C:\Users\file.csv'
WITH (FORMAT = 'CSV', ROWTERMINATOR = '\n', FIRSTROW = 2);

ALTER TABLE #tempTable1
DROP COLUMN a, b, c, d, e, f, h, i;

Rather than doing this, you can use a format file which goes:

   9.0  
   9  
   1 SQLCHAR 0 0 "," 0 "" ""  
   2 SQLCHAR 0 0 "," 0 "" ""  
   3 SQLCHAR 0 0 "," 0 "" ""  
   4 SQLCHAR 0 0 "," 0 "" ""  
   5 SQLCHAR 0 0 "," 0 "" ""  
   6 SQLCHAR 0 0 "," 0 "" ""  
   7 SQLCHAR 0 0 "," 1 g Latin1_General_CI_AS  
   8 SQLCHAR 0 0 "," 0 "" ""  
   9 SQLCHAR 0 0 "\r\n" 0 "" ""  

And then you only need one column in the table.

DECLARE @json varchar (max);

SELECT g INTO @json FROM #tempTable1

SELECT INTO creates a table, but you cannot create a table variable this way. It seems that you simply mean:

   SELECT [@](/users/na/?userId=b1b84c87-4001-0003-0000-000000000000) = g FROM #tempTable1  

Answer 2

Hi @mike ,

Welcome to Microsoft Q&A!

We could get the default schema as below:

DECLARE @json varchar (max);  
SELECT @json=g FROM #tempTable1  
  
SELECT *  
FROM OPENJSON (@JSON)  

What is the expected output? The 'Personal Information Type' and 'Purpose' are missing in your JSON.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.