How can i map the SQL Table fields to match the way the data is presented in the flatfile.

Question

Good Day

I have data in this format below ,

and i tried to use SQL Server Import Tools

and i am trying to use

but the data does not filter through correctly in terms of columns , what i get is this

How can i map the SQL Table fields to match the way the data is presented in the flatfile.

Answer 1

Hi @Vuyiswa Maseko
Agree with YitzhakKhabinsky, your Json is not well formatted, since it doesn't have comma(,) at the end of each Json data. And you need to add '[' and ']' at both the start and end position.
Try this:

DECLARE @JSON NVARCHAR(MAX)  
  
SELECT @JSON='[' + STUFF(REPLACE(BulkColumn,'{"id":',',{"id":'),1,1,'')+ ']'  
FROM OPENROWSET (BULK 'G:\hacki\gz\part-00000\part-1', SINGLE_CLOB) j;  
     
SELECT book.*   
FROM OPENJSON (@JSON)  
WITH (id varchar(max)  ,  
 full_name varchar(max) ,   
 middle_name varchar(max)  ,  
 last_name varchar(max)  ,  
 gender varchar(max)  ,  
 birth_year varchar(max)  ,  
 birth_date varchar(max)  ,  
 linkedin_url varchar(max)  ) as book  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

Hi @Vuyiswa Maseko ,

It seems that your input file is in JSON format. It is not a flat file.
Please attach your JSON file sample to the question.

You would need to use something like follows.

SQL

DECLARE @JSON VARCHAR(MAX);  
  
SELECT @JSON=BulkColumn  
FROM OPENROWSET (BULK 'e:\Temp\Input.json', SINGLE_CLOB) j;  
  
SELECT tradeF.*   
FROM OPENJSON (@JSON)  
WITH  (  
...  
   ) as tradeF;  

Answer 3

Hi @Vuyiswa Maseko
For JSON data file, you could write a query using OPENROWSET along with OPENJSON to save the JSON type data into a new table.
For example:

SELECT book.*  
INTO new_table  
FROM OPENROWSET (BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) AS j  
CROSS APPLY OPENJSON(BulkColumn)  
WITH(id nvarchar(100),   
     name nvarchar(100),   
	 price float,  
     pages_i int,   
	 author nvarchar(100)) AS book  

You could use SSIS package for inserting JSON data into the SQL Server table as well.
Please refer to this blog for more detail steps: Import JSON data into SQL Server

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 4

Than you very much , i did the following from your advice

SELECT book.*  
 INTO CONTACTS  
 FROM OPENROWSET (BULK 'G:\hacki\gz\part-00000\part-1', SINGLE_CLOB) AS j  
 CROSS APPLY OPENJSON(BulkColumn)  
 WITH(id varchar(max) '$.id',  
full_name varchar(max) '$.full_name',   
middle_name varchar(max) '$.middle_name',  
last_name varchar(max) '$.last_name',  
gender varchar(max) '$.gender',  
birth_year varchar(max) '$.birth_year',  
birth_date varchar(max) '$.birth_date',   
emails varchar(max) '$.emails',  
interests varchar(max) '$.interests',  
skills varchar(max) '$.skills',  
regions varchar(max) '$.regions',  
countries varchar(max) '$.countries',  
street_addresses varchar(max) '$.street_addresses',  
experience varchar(max) '$.experience',  
company varchar(max) '$.company',  
[location] varchar(max) '$.location' ) AS book  

the query ran and said (1 row affected) and there are a lot of records on the file . I Must also mention that part-00001 is 2 Gigs Big

Thanks

Answer 5

Thank you this works nicely.