JSON parsing error, unsupported encoding or multiline in Data Factory

1
votes

I am using Azure's Data Factory tool to read a text file with a JSON structure from a Data Lake Storage Gen2 to transform the data and pass it to an Azure SQL DB.

I can test the connection with "Source settings" and it works successfully.

Sadly, I am receiving the following error msg when trying to import a "projection":

"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?

Update:

Due to the special character "$", the file were encoded in UTF-8-BOM. Reading the data from inside a Data Factory from the Data Lake Storage Gen2 this leads to the parsing errors.

1
mongodbazure-sql-databaseazure-data-factoryazure-data-lake
I apologize the copy + paste error..I edited the beginning of the content. – Matzka
Could you add a bit more of your JSON? It is hard to really help you when we can't see the bulk of what is likely to be causing the error – iamdave
I just increased a bit the content. The data comes from a MongoDB. Some other field names also contain "[" or "]" characters. – Matzka
Based on the error message, have you looked at what the encoding of the file is and whether your JSON meets the format specs defined here? – iamdave
Hi@Matzka I am also researching this issue, do you mean that the parsing errors caused by the special character "$"? – Joseph Xu

1 Answers

0
votes

Congratulations that you solved it:

"I just remove the $ and everything works then".

Please see the question update.

I help you post it as answer and this can be beneficial to other community members.