question

PulipatiVedanth-8342 avatar image
0 Votes"
PulipatiVedanth-8342 asked PulipatiVedanth-8342 commented

I have my json file in Azure Blog Storage i am trying it to import the data from json file to Azure sql database.I am trying to create sql script to import the data

The script i have used to import the data is

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS Token';
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'Location',
CREDENTIAL= MyAzureBlobStorageCredential);

SELECT *
FROM OPENROWSET(BULK 'generated.json', DATA_SOURCE = 'MyAzureBlobStorage',
FORMATFILE='product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data

facing the error like

![118230-image.png]

I knew i was doing something wrong in the query but i don't get it .can someone please help me on this.
And also i am confused about how to write the format file for the table. Any leads will be appreciated


MY format table script

12.0
4
1 SQLCHAR 0 50 '","' 1 Name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 '","' 2 Gender SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 '","' 3 Company SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\t" 4 Email SQL_Latin1_General_CP1_CI_AS


Thanks in advance..!!

[1]: /answers/storage/attachments/118230-image.png

azure-sql-database
image.png (7.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered PulipatiVedanth-8342 commented

Hi @pulipativedanth-8342,

Thanks for using Microsoft Q&A !!
If you do not have any specific needs to create a FMT file then you can use OPENROWSET with SINGLE_CLOB to import JSON data from a Blob Storage using a query like below -

 SELECT id,[name],author FROM OPENROWSET(
    BULK  'input/books.json',
    DATA_SOURCE = 'storageadftutorial',
     SINGLE_CLOB
     ) AS DataFile
 CROSS APPLY OPENJSON(BulkColumn)
 WITH (id NCHAR(1000),
 [name] NCHAR(500),
 author NCHAR(500)) as book

Please note here that the data from JSON file is returned as BulkColumn which you can join with OPENJSON which returns each JSON array object as a single row, which you can insert into the required table.

books.json sample content

[
{
"id" : "978-0641723445",
"cat" : ["book","hardcover"],
"name" : "The Lightning Thief",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 1,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 12.50,
"pages_i" : 384
}
,
{
"id" : "978-1423103349",
"cat" : ["book","paperback"],
"name" : "The Sea of Monsters",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 2,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 6.49,
"pages_i" : 304
}
]

SQL Table:
Create table books( Id NCHAR(1000), [Name] NCHAR(500), Author NCHAR(500) )

Use below to insert into books table

 INSERT INTO books with (TABLOCK) (id,[name],author)
 SELECT id,[name],author FROM OPENROWSET(
    BULK  'input/books.json',
    DATA_SOURCE = 'storageadftutorial',
     SINGLE_CLOB
     ) AS DataFile
 CROSS APPLY OPENJSON(BulkColumn)
 WITH (id NCHAR(1000),
 [name] NCHAR(500),
 author NCHAR(500)) as book

118409-image.png

Please refer to Import JSON documents into SQL Server for details.

Hope this helps.

Thanks
Saurabh


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.


image.png (81.4 KiB)
· 11
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @SaurabhSharma-msft
Thank you for your inputs it is working for me but i am getting very minor issue while storing the values into SQL Database it is storing like below screenshot. It tried multiple ways to remove it but couldn't able to do it. Can you please help me with that.
118420-image.png



0 Votes 0 ·
image.png (12.7 KiB)

Hi @pulipativedanth-8342,
It may be due to the encoding of the file. Can you download the data file to your local and check the encoding of your file using Notepad++ (using Encoding Menu) or any other text editor. If file is not in UTF-8 format then convert to UTF-8 using "Convert to UTF-8" option. (See screenshot below).
118831-image.png

Alternatively, you can take the sample json file from here and use the same to insert the records in your Azure SQL.
Please let me know if you still see any issues.

Thanks
Saurabh

0 Votes 0 ·
image.png (120.6 KiB)

Hi @SaurabhSharma-msft Tried to encoding and uploaded the file and ran the script still facing same error and also even tried to to download the .json file from the link you have given and tried using that way also but no luck still facing the same error.
118931-image.png


118932-image.png


0 Votes 0 ·
image.png (19.2 KiB)
image.png (89.9 KiB)
Show more comments