This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 0%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EWW%3C/text%3E%3C/svg%3E)
Dear reader,
In Azure Data Factory I can't seem to load a JSON file. In the Data Flow source I selected the JSON file and tried all possible options (Single document, Document per line and Array of documents). It tried other options as well, but I can't get a data preview on the final tab. The error message is always the same:
Malformed records are detected in schema inference. Parse Mode: FAILFAST. It could be because of a wrong selection in document form to parse json file(s). Please try a different 'Document form' (Single document/Document per line/Array of documents) on the json source.
The goal is to upload the variables ("ID", "Geslacht", "Leeftijd", "Onderwijssoort" ,"Migratieachtergrond", "RegioS", "Perioden","Gediplomeerden_1") with their data into a SQL database. Below shows how the JSON file is when extracted from the source.
Thank you for your help!
{"value":{"115141000":{"ID":115141000,"Geslacht":"3000","Leeftijd":"11800","Onderwijssoort":"A025284","Migratieachtergrond":"T001040","RegioS":"GM0034","Perioden":"2016SJ00","Gediplomeerden_1":"53"},
"115141001":{"ID":115141001,"Geslacht":"3000","Leeftijd":"11800","Onderwijssoort":"A025284","Migratieachtergrond":"T001040","RegioS":"GM0034","Perioden":"2017SJ00","Gediplomeerden_1":"45"},
"115141002":{"ID":115141002,"Geslacht":"3000","Leeftijd":"11800","Onderwijssoort":"A025284","Migratieachtergrond":"T001040","RegioS":"GM0034","Perioden":"2018SJ00","Gediplomeerden_1":"42"}}}
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
@Wai-Wah Liu Hello and welcome to Microsoft Q&A.
I understand you are having trouble getting ADF Dataflow to read a JSON file. I take it the sample you shared is the file, or close enough?
So far, I uploaded your sample to data lake, set up a dataflow with inline source, and was able to preview it without doing a projection. I used "single document" form, as your sample showed only a single object, containing a subobject 'value' with the records as properties of 'value'.
This particular form will not be pleasant to work with, assuming you want each ID on a separate row.
After importing projection I was still able to preview.
So given this worked for me inline, with your sample, I must speculate the root of your problem lies elsewhere.
Are you using a dataset source, or inline source? If dataset source, is there a schema defined in the dataset?
Have you verified the projection matches the data? Maybe an outdated projection or schema looks different from the document.
Lasltly, is there more to your data than the simple example? Maybe later in the file it has a second root object?
Hi Martin,
Is the text below possible in Data Factory? I was able to transform the JSON to a database format in PySpark but not in Data Factory.
Thank you for your time!
The goal is to upload the variables ("ID", "Geslacht", "Leeftijd", "Onderwijssoort" ,"Migratieachtergrond", "RegioS", "Perioden","Gediplomeerden_1") with their data into a SQL database. Below shows how the JSON file is when extracted from the source.
Hello Martin,
Thanks for answering. I have been able to display the preview. The original file was encoded UTF-8 with BOM, changing this to regular UTF-8 solved the problem.
Ahh Thanks for sharing the solution. That was a sneaky one.