NuAlphaMan
Reputation: 713
Azure Data Flow: Malformed records are detected in schema inference
I'm trying to create a Data Flow in ADF that will read my JSON file and eventually put the data into a DB. However, I keep getting the following error:
at Source '': 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.
So, I've scoured the internet, including StackOverflow, and have not found an answer to this problem. Everything I've seen states that you need to select the Document form type and I choose Array of Documents. And I still get the error.
Here is my JSON:
[
{
"DriverGroups": [
{
"id": "b27A9"
},
{
"id": "b278A"
}
],
"Keys": [],
"LicenseNumber": "xxx1",
"LicenseProvince": "OH",
"ViewDriversOwnDataOnly": false,
"AcceptedEula": 19,
"ActiveDashboardReports": [],
"ActiveDefaultDashboards": [],
"ActiveFrom": "2023-02-01T16:08:40.618Z",
"ActiveTo": "2050-01-01T00:00:00.000Z",
"AuthorityAddress": "an address 1",
"AuthorityName": "company name 1",
"AvailableDashboardReports": [],
"Bookmarks": [],
"CannedResponseOptions": [],
"CarrierNumber": "thenumber",
"ChangePassword": false,
"Comment": "",
"CompanyAddress": "adress 1",
"CompanyName": "company name",
"CountryCode": "",
"DateFormat": "MM/dd/yy HH:mm:ss",
"CompanyGroups": [
{
"id": "b27A9"
},
{
"id": "b278A"
}
],
"DefaultGoogleMapStyle": "Roadmap",
"DefaultHereMapStyle": "Roadmap",
"DefaultMapEngine": "MapBox",
"DefaultOpenStreetMapStyle": "MapBox",
"DefaultPage": "helpGuide",
"Designation": "",
"DisplayCurrency": "Cad",
"DriverGuideVersion": 0,
"ElectricEnergyEconomyUnit": "MPGEUS",
"EmployeeNo": "empno",
"FeaturePreview": "",
"FirstDayOfWeek": "Sunday",
"FirstName": "firstname",
"FuelEconomyUnit": "MPGUS",
"HosRuleSet": "America8DayBig",
"Id": "b2053",
"IsAdverseDrivingEnabled": true,
"IsDriver": true,
"IsEulaAccepted": true,
"IsEmailReportEnabled": true,
"IsExemptHosEnabled": false,
"IsLabsEnabled": false,
"IsMetric": false,
"IsNewsEnabled": true,
"IsPersonalConveyanceEnabled": false,
"IsServiceUpdatesEnabled": false,
"IsYardMoveEnabled": true,
"JobPriorities": [],
"Language": "en",
"LastName": "lastname",
"MapViews": [
{
"name": "North America",
"viewport": {
"x": -500,
"y": 129,
"width": 145.5,
"height": -52
},
"highlightGroups": []
}
],
"MaxPcDistancePerDay": 0,
"MediaFiles": [],
"Name": "a name",
"Password": null,
"PhoneNumber": "",
"PhoneNumberExtension": "",
"PrivateUserGroups": [],
"ReportGroups": [],
"SecurityGroups": [
{
"id": "12365"
}
],
"ShowClickOnceWarning": true,
"TimeZoneId": "America/New_York",
"WifiEula": 0,
"ZoneDisplayMode": "Default",
"Version": null
},
{
"DriverGroups": [
{
"id": "b27A4"
},
{
"id": "b278A"
}
],
"Keys": [],
"LicenseNumber": "bbbbb3",
"LicenseProvince": "PA",
"ViewDriversOwnDataOnly": false,
"AcceptedEula": 19,
"ActiveDashboardReports": [],
"ActiveDefaultDashboards": [],
"ActiveFrom": "2022-01-13T15:42:41.318Z",
"ActiveTo": "2022-01-16T15:54:00.000Z",
"AuthorityAddress": "auth address",
"AuthorityName": "auth comp name",
"AvailableDashboardReports": [],
"Bookmarks": [],
"CannedResponseOptions": [],
"CarrierNumber": "xxxxx",
"ChangePassword": false,
"Comment": "",
"CompanyAddress": "company address",
"CompanyName": "Company1",
"CountryCode": "",
"DateFormat": "MM/dd/yy HH:mm:ss",
"CompanyGroups": [
{
"id": "b27A4"
},
{
"id": "b278A"
}
],
"DefaultGoogleMapStyle": "Roadmap",
"DefaultHereMapStyle": "Roadmap",
"DefaultMapEngine": "MapBox",
"DefaultOpenStreetMapStyle": "MapBox",
"DefaultPage": "helpGuide",
"Designation": "",
"DisplayCurrency": "Cad",
"DriverGuideVersion": 0,
"ElectricEnergyEconomyUnit": "MPGEUS",
"EmployeeNo": "emplno",
"FeaturePreview": "",
"FirstDayOfWeek": "Sunday",
"FirstName": "firnst",
"FuelEconomyUnit": "MPGUS",
"HosRuleSet": "America8DayBig",
"Id": "b81F",
"IsAdverseDrivingEnabled": true,
"IsDriver": true,
"IsEulaAccepted": true,
"IsEmailReportEnabled": true,
"IsExemptHosEnabled": false,
"IsLabsEnabled": false,
"IsMetric": false,
"IsNewsEnabled": true,
"IsPersonalConveyanceEnabled": true,
"IsServiceUpdatesEnabled": false,
"IsYardMoveEnabled": true,
"JobPriorities": [],
"Language": "en",
"LastName": "lastname 2",
"MapViews": [
{
"name": "North America",
"viewport": {
"x": -66,
"y": 125,
"width": 145.5,
"height": -52
},
"highlightGroups": []
}
],
"MaxPcDistancePerDay": 0,
"MediaFiles": [],
"Name": "his/her name",
"Password": null,
"PhoneNumber": "",
"PhoneNumberExtension": "",
"PrivateUserGroups": [],
"ReportGroups": [],
"SecurityGroups": [
{
"id": "b27C9"
}
],
"ShowClickOnceWarning": true,
"TimeZoneId": "America/New_York",
"WifiEula": 0,
"ZoneDisplayMode": "Default",
"Version": null
}]
Has anyone else encountered this even after choosing Array of documents? If so, how did you solve the problem? Any help would be greatly appreciated.
Upvotes: 0
Views: 501
Answers (2)
NuAlphaMan
Reputation: 713
In case anyone else runs into this issue. I've found a workaround to this problem. I took the list of objects that I had and wrote them out to a file like this:
{"DriverGroups":[{"Id":"b27A9"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"VD123456","LicenseProvince":"NC","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2020-07-01T16:08:40.618Z","ActiveTo":"2050-01-01T00:00:00.000Z","AuthorityAddress":"555 Some Rd, A City, CA 12345","AuthorityName":"Company 1","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"123456","ChangePassword":false,"Comment":"","CompanyAddress":"555 Some Rd, A City, CA 12345","CompanyName":"Company 1","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b27A9"},{"Id":"b278A"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"US","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP1","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FIRSTNAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DayBig","Id":"A123","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":false,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LASTNAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-121,"Y":59,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
{"DriverGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"24681012","LicenseProvince":"CA","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2021-10-13T15:42:41.318Z","ActiveTo":"2032-01-16T15:54:00.000Z","AuthorityAddress":"333 Any ST, Another City, TX 22222","AuthorityName":"Company 2","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"246810","ChangePassword":false,"Comment":"","CompanyAddress":"333 Any ST, Another City, TX 22222","CompanyName":"Company 2","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"EUR","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP2","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FNAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DayBig","Id":"B246","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":true,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LNAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-200,"Y":49,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME1","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
{"DriverGroups":[{"Id":"b27A4"},{"Id":"b278A"}],"Keys":[],"LicenseNumber":"36912150","LicenseProvince":"MI","ViewDriversOwnDataOnly":false,"AcceptedEula":19,"ActiveDashboardReports":[],"ActiveDefaultDashboards":[],"ActiveFrom":"2022-11-26T17:46:58.000Z","ActiveTo":"2045-05-19T12:00:00.000Z","AuthorityAddress":"4444 Random Ave, Yet Another City, IA 33333","AuthorityName":"Company 3","AvailableDashboardReports":[],"Bookmarks":[],"CannedResponseOptions":[],"CarrierNumber":"369121","ChangePassword":false,"Comment":"","CompanyAddress":"4444 Random Ave, Yet Another City, IA 33333","CompanyName":"Company 3","CountryCode":"","DateFormat":"MM/dd/yy HH:mm:ss","CompanyGroups":[{"Id":"b278A"},{"Id":"b27A4"}],"DefaultGoogleMapStyle":"Roadmap","DefaultHereMapStyle":"Roadmap","DefaultMapEngine":"MapBox","DefaultOpenStreetMapStyle":"MapBox","DefaultPage":"helpGuide","Designation":"","DisplayCurrency":"Cad","DriverGuideVersion":0,"ElectricEnergyEconomyUnit":"MPGEUS","EmployeeNo":"EMP3","FeaturePreview":"","FirstDayOfWeek":"Sunday","FirstName":"FIRST_NAME","FuelEconomyUnit":"MPGUS","HosRuleSet":"America8DaySleeper","Id":"C369","IsAdverseDrivingEnabled":true,"IsDriver":true,"IsEulaAccepted":true,"IsEmailReportEnabled":true,"IsExemptHosEnabled":false,"IsLabsEnabled":false,"IsMetric":false,"IsNewsEnabled":true,"IsPersonalConveyanceEnabled":false,"IsServiceUpdatesEnabled":false,"IsYardMoveEnabled":true,"JobPriorities":[],"Language":"en","LastName":"LAST_NAME","MapViews":[{"HighlightGroups":[],"Name":"North America","Settings":null,"Viewport":{"X":-171,"Y":19,"Width":145.5,"Height":-52}}],"MaxPcDistancePerDay":0,"MediaFiles":[],"Name":"NAME2","Password":null,"PhoneNumber":"","PhoneNumberExtension":"","PrivateUserGroups":[],"ReportGroups":[],"SecurityGroups":[{"Id":"b27C9","Name":null}],"ShowClickOnceWarning":true,"TimeZoneId":"America/New_York","WifiEula":0,"ZoneDisplayMode":"Default","Version":null}
I then created a Dataset using the file. I then created a Data flow and pointed to my Dataset and selected for my Document form (JSON settings) >> Document per line. Using this format allowed me to preview my data.
I hope this helps!
Upvotes: 0
Rakesh Govindula
Reputation: 11454
I tried the same JSON of yours in ADF dataflow and first got this error when I select Document per line.
When I changed the JSON settings to Array of documents, It is working fine for me and you can see I got the data preview of the above JSON.
It suggest you to try with new JSON dataset or new dataflow. If the issue still persists, then its better to raise a Support ticket on your issue.
Upvotes: 0
Related Questions
- Getting "Malformed records are detected in schema inference" when trying to flatten JSON using a data flow in ADF
- Azure Data factory Data Flow Activity
- Update existing records for deltatable using Azure Data flow
- Malformed records are detected in schema inference parsing json
- Azure Data Factory Data Flow Joins condition Duplicate Records
- AZure Data Factor - Data flow - aggregate
- Malformed records are detected in schema inference while using azure synapse analytics
- Azure Data Factory Data Flow - Parquet, Schema and Data Type "Any"
- Azure Data flow new branch how data flow