cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeffGimo
Resolver I
Resolver I

Convert csv file to JSON format

Is there a way to convert csv files to JSON format?

I have tried searching for same question but no luck.

Please advise.

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

The flow below produces the following output:

 

[
  {
    "SSID": "test1",
    "sessionCount": "90 (54.22%)",
    "clientCount": "26 (48.15%)",
    "duration": "1d:11h:35m (62.06%)",
    "totalUsage": "939.09MB (50.69%)",
    "usage": "814.94MB/124.15MB"
  },
  {
    "SSID": "-",
    "sessionCount": "36 (21.69%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:2h:55m (5.09%)",
    "totalUsage": "0.00B (0.0%)",
    "usage": "0.00B/0.00B"
  },
  {
    "SSID": "test2",
    "sessionCount": "21 (12.65%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:8h:35m (14.97%)",
    "totalUsage": "538.12MB (29.05%)",
    "usage": "500.54MB/37.58MB"
  },
  {
    "SSID": "test3",
    "sessionCount": "14 (8.43%)",
    "clientCount": "8 (14.81%)",
    "duration": "0d:8h:0m (13.95%)",
    "totalUsage": "25.28MB (1.36%)",
    "usage": "20.60MB/4.68MB"
  },
  {
    "SSID": "test4",
    "sessionCount": "3 (1.81%)",
    "clientCount": "2 (3.7%)",
    "duration": "0d:0h:20m (0.58%)",
    "totalUsage": "55.76KB (0.0%)",
    "usage": "26.93KB/28.84KB"
  },
  {
    "SSID": "test5",
    "sessionCount": "2 (1.2%)",
    "clientCount": "1 (1.85%)",
    "duration": "0d:1h:55m (3.34%)",
    "totalUsage": "350.14MB (18.9%)",
    "usage": "338.73MB/11.41MB"
  }
]

 

Here is the flow (you can also view it here, https://ibb.co/DDG5z7F )

CSVtoJSON.jpg

Because this flow may be difficult for you to copy, I have exported it so you can import it into your environment:

https://www.tachytelic.net/wp-content/uploads/CSVtoJSON_20210121194739.zip

No need to remove the first two lines, the flow handles it just the way it is.

View solution in original post

17 REPLIES 17
abm
Super User
Super User

Could you please post a sample format of your CSV file? Is that got a header?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Please see picture as I can't attach the csv file.

JeffGimo_0-1611169091267.png

 

Hi @JeffGimo 

 

Please see this post

 

Microsoft Flow (advanced) tutorial : creating a CSV to JSON converter from scratch | Serge Luca aka ...

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

The sample examples are not working as per my testing. Will wait for another response. There might be some work around here.

Paulie78
Super User
Super User

You really should be able to post a sample of the CSV here, even if it is only a few lines. If you would like a compelling response it would be really helpful. You can fictionalise the data if it is sensitive.

Here's the sample data:

Session Data by ID     
SSIDSession Count (%)Client Count (%)Duration (%)Total Usage (%)Usage (In/Out)
test190 (54.22%)26 (48.15%)1d:11h:35m (62.06%)939.09MB (50.69%)814.94MB/124.15MB
-36 (21.69%)13 (24.07%)0d:2h:55m (5.09%)0.00B (0.0%)0.00B/0.00B
test221 (12.65%)13 (24.07%)0d:8h:35m (14.97%)538.12MB (29.05%)500.54MB/37.58MB
test314 (8.43%)8 (14.81%)0d:8h:0m (13.95%)25.28MB (1.36%)20.60MB/4.68MB
test43 (1.81%)2 (3.7%)0d:0h:20m (0.58%)55.76KB (0.0%)26.93KB/28.84KB
test52 (1.2%)1 (1.85%)0d:1h:55m (3.34%)350.14MB (18.9%)338.73MB/11.41MB
Paulie78
Super User
Super User

But that isn't a CSV. If you want a good result, you should post the CSV that you are going to use in Power Automate.

Sorry. Can you teach me how to upload a csv file? I get this error below upon trying.

JeffGimo_1-1611251097713.png

 

Paulie78
Super User
Super User

Just open the CSV on your machine, copy and paste a few lines, ideally into the code window:

2021-01-21_17-48-16.gif

Thank you so much.
here it is

Session Data by SSID,,,,,
SSID,Session Count (%),Client Count (%),Duration (%),Total Usage (%),Usage (In/Out)
test1,90 (54.22%),26 (48.15%),1d:11h:35m (62.06%),939.09MB (50.69%),814.94MB/124.15MB
-,36 (21.69%),13 (24.07%),0d:2h:55m (5.09%),0.00B (0.0%),0.00B/0.00B
test2,21 (12.65%),13 (24.07%),0d:8h:35m (14.97%),538.12MB (29.05%),500.54MB/37.58MB
test3,14 (8.43%),8 (14.81%),0d:8h:0m (13.95%),25.28MB (1.36%),20.60MB/4.68MB
test4,3 (1.81%),2 (3.7%),0d:0h:20m (0.58%),55.76KB (0.0%),26.93KB/28.84KB
test5,2 (1.2%),1 (1.85%),0d:1h:55m (3.34%),350.14MB (18.9%),338.73MB/11.41MB

. Thanks for the instruction. 

Hi @JeffGimo 

 

Generally it a good practice to have one header for a csv file. It will be easier to manage. Otherwise you will struggle with selecting the rows and define the fields.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Thanks for the tip. Is there a way to convert this csv file to json format?

Hi @JeffGimo 

 

You still need to truncate the first line of your CSV file and save it. It may or not possible. Then use the  post I provided earlier should work for you. 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
Paulie78
Super User
Super User

The flow below produces the following output:

 

[
  {
    "SSID": "test1",
    "sessionCount": "90 (54.22%)",
    "clientCount": "26 (48.15%)",
    "duration": "1d:11h:35m (62.06%)",
    "totalUsage": "939.09MB (50.69%)",
    "usage": "814.94MB/124.15MB"
  },
  {
    "SSID": "-",
    "sessionCount": "36 (21.69%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:2h:55m (5.09%)",
    "totalUsage": "0.00B (0.0%)",
    "usage": "0.00B/0.00B"
  },
  {
    "SSID": "test2",
    "sessionCount": "21 (12.65%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:8h:35m (14.97%)",
    "totalUsage": "538.12MB (29.05%)",
    "usage": "500.54MB/37.58MB"
  },
  {
    "SSID": "test3",
    "sessionCount": "14 (8.43%)",
    "clientCount": "8 (14.81%)",
    "duration": "0d:8h:0m (13.95%)",
    "totalUsage": "25.28MB (1.36%)",
    "usage": "20.60MB/4.68MB"
  },
  {
    "SSID": "test4",
    "sessionCount": "3 (1.81%)",
    "clientCount": "2 (3.7%)",
    "duration": "0d:0h:20m (0.58%)",
    "totalUsage": "55.76KB (0.0%)",
    "usage": "26.93KB/28.84KB"
  },
  {
    "SSID": "test5",
    "sessionCount": "2 (1.2%)",
    "clientCount": "1 (1.85%)",
    "duration": "0d:1h:55m (3.34%)",
    "totalUsage": "350.14MB (18.9%)",
    "usage": "338.73MB/11.41MB"
  }
]

 

Here is the flow (you can also view it here, https://ibb.co/DDG5z7F )

CSVtoJSON.jpg

Because this flow may be difficult for you to copy, I have exported it so you can import it into your environment:

https://www.tachytelic.net/wp-content/uploads/CSVtoJSON_20210121194739.zip

No need to remove the first two lines, the flow handles it just the way it is.

View solution in original post

Thank you so much @Paulie78 tried and tested it and it is working as I need it. You the best!!

Paulie78
Super User
Super User

Although you have already accepted the solution, here is an even more basic version which does the same thing:

https://ibb.co/wgkrWtr

CSVtoJSON-Simple.jpg

Download it here:

https://www.tachytelic.net/wp-content/uploads/CSVtoJSON-Simple_20210121200115.zip

Paulie78
Super User
Super User

In case you are interested, I have just produced a video using your example for how this flow was built:

https://youtu.be/0RqSco9Til0

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,150)