Data cleaning/import

[rugge78]

Hi,

I am trying to clean and import the data from the attached text file but couldn't conclude as i am unable to split the rows and columns and sort/align the data into appropriate columns. Could you please assist/advise.

I am trying to use power query to import and transform the data.

Thanks.

 

[rugge78]

oops...sorry couldn't upload the source data file in the earlier thread....here is some of the content from it.

{"intersectionId":1,"intersectionDescription":"Dorchester Road and Huron Church Road","traffic":[{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Light","entrance":"E","exit":"N","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Light","entrance":"E","exit":"S","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"ArticulatedTruck","entrance":"N","exit":"S","qty":3,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Light","entrance":"N","exit":"S","qty":6,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Light","entrance":"S","exit":"E","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"ArticulatedTruck","entrance":"S","exit":"N","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Bus","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"Light","entrance":"S","exit":"N","qty":3,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"SingleUnitTruck","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:00:00","vehicleType":"ArticulatedTruck","entrance":"W","exit":"S","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"Light","entrance":"E","exit":"S","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"ArticulatedTruck","entrance":"N","exit":"S","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"Light","entrance":"N","exit":"S","qty":4,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"Light","entrance":"S","exit":"E","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"ArticulatedTruck","entrance":"S","exit":"N","qty":3,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"Bus","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"Light","entrance":"S","exit":"N","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:01:00","vehicleType":"ArticulatedTruck","entrance":"W","exit":"S","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"E","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"SingleUnitTruck","entrance":"E","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"E","exit":"S","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"E","exit":"W","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"N","exit":"E","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"S","exit":"E","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"ArticulatedTruck","entrance":"S","exit":"N","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:02:00","vehicleType":"Light","entrance":"S","exit":"N","qty":4,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"Light","entrance":"E","exit":"W","qty":3,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"Light","entrance":"N","exit":"E","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"Light","entrance":"N","exit":"S","qty":3,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"SingleUnitTruck","entrance":"N","exit":"S","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"ArticulatedTruck","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"Light","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"WorkVan","entrance":"S","exit":"N","qty":1,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-31T12:03:00","vehicleType":"Light","entrance":"W","exit":"E","qty":2,"isDaylightSavingsTime":true},{"timeStamp":"2020-03-
.
.
.
.
.
..
.
.
.
..
.

 

[rugge78]

Just an FYI on cross posting for this post : I have also posted this query on another forum seeking for answers.

Below mentioned is the URL.

Issues while importing the unsorted data into excel

Hi, I am trying to clean and import the data from the a text file(below mentioned is some of the content extract from the text file) but couldn't conclude as i am unable to split the rows and columns and sort/align the data into appropriate columns. Could you please assist/advise. I am...

www.excelforum.com

Thanks.

 

[billszysz]

oops...sorry couldn't upload the source data file in the earlier thread....here is some of the content from it.

So, this is only part of the file, isn't it?. I think Your file is a JSON file.
If I'm right, we can use this code below.

let
  Source        = Json.Document(File.Contents("PathToYourTXTFile")),
  ToTable       = Table.FromRecords({Source}),
  Headers       = Record.FieldNames(ToTable{0}[traffic]{0}),
  traffic       = Table.TransformColumns(ToTable, {{"traffic", each Table.FromRecords(_)}}),
  ExpandTraffic = Table.ExpandTableColumn(traffic, "traffic", Headers)
in
  ExpandTraffic

 

[Alex Blakenburg]

I believe that a Power Query solution has been provided on the cross posting.
Issues while importing the unsorted data into excel