Can't access txt file with json format from Data Lake Gen2 inside a Data Flow

Question

I have stored a txt file with json format (coming from a MongoDB) in my Data Lake Storage Gen2 and inside my Data Flow I would like to access that file, convert it into a column structure, transform the data and shift it to my Azure SQL DB.
Currently I am facing the problem, that I can't read the data.

Under "Source settings" I can test the connection and it works successfully. When trying to import "projection" I am facing with the following error:

"JSON parsing error, unsupported encoding or multiline"

Under "Source options"->"JSON settings" I also tried it by activating the field "Single document". Without any success.

[{"_id":{"$oid":"xxxxxxx"},"uid":"xxxxxxxxxx","test-uid":"xxxxxxxxxxxx","url":"https://test","info":"Mozilla/5.0 (Windows NT 10.0; Win64; x64)","test":"","dimension":"2560x1343",.....

The Data Preview, of course, shows the same error message.

I can't detect any json encoding errors. Any idea what I can do?
Any tutorial you can recommend for shifting json from Data Lake Gen2 into Azure SQL DB?

Thanks

Answer 1

Hello ,
Thanks for posting the question and welcome to Microsoft Q & A .

This is JSON i took to work on this is pasted below ( it is what you had pasted ) . My understanding is that there are some part of the JSON which you did shared and
may be that was the reason it was failing as in my case it just worked fine . Please check the images below

[
{"_id":{"$oid":"xxxxxxx"},"uid":"xxxxxxxxxx","test-uid":"xxxxxxxxxxxx","url":"https://test","info":"Mozilla/5.0 (Windows NT 10.0; Win64; x64)","test":""}
,{"_id":{"$oid":"yyyy"},"uid":"yyyy","test-uid":"xxxxxxxxxxxx","url":"https://test","info":"Mozilla/5.0 (Windows NT 10.0; Win64; x64)","test":""}

]

Please do paste the dummy data and we will try to work on that and see we can repro the issue . Let me know how it goes .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Thanks, for your reply. The reason that due to the special character $, the file were stored with UTF-8-BOM encoding. This leads to parsing errors when trying to read the data inside Azure Data Factory.