cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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
Super User III
Super User III

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
Super User III
Super User III

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 blog

Power 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 blog

Power 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.

Super User III
Super User III

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
Super User III
Super User III

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

 

Super User III
Super User III

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 blog

Power 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 blog

Power Automate Video Tutorials
Super User III
Super User III

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!!

Super User III
Super User III

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

Super User III
Super User III

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
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (3,249)