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

Vuyiswa Maseko 351 Reputation points
2022-11-15T14:41:03.937+00:00

Good Day

I have data in this format below ,

260603-image.png

and i tried to use SQL Server Import Tools

260621-image.png

and i am trying to use

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

260535-image.png

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,523 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2022-11-17T02:32:40.433+00:00

    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.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,731 Reputation points
    2022-11-15T14:54:17.89+00:00

    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;  
    
    2 people found this answer helpful.
    0 comments No comments

  2. LiHongMSFT-4306 25,651 Reputation points
    2022-11-16T02:21:40.323+00:00

    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.

    0 comments No comments

  3. Vuyiswa Maseko 351 Reputation points
    2022-11-16T19:52:37.383+00:00

    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


  4. Vuyiswa Maseko 351 Reputation points
    2022-11-18T09:55:29.54+00:00

    Thank you this works nicely.

    0 comments No comments