Enter JSON into CSV - Error: "from" is object needs to be "array" - Change JSON to array

[Kastore]

Hello everyone,

I am trying to create a flow where a newly created Excel-File gets converted into a CSV.txt file with no empty lines and just specific values.

I am stuck with JSON right now since that was the only way I was able to get to the different values. The output also works (so far) but i cant put the information into the CSV as the JSON is an "object" and the CSV needs an "array". 

Since i got no clue about JSON i am stuck and would appreciate any help

1) When file is created

2) get rows 

3) Parse JSON

-> 

{

"type": "array",

"items": {

"type": "object",

"properties": {

"@@odata.etag": {

"type": "string"

},

"ItemInternalId": {

"type": "string"

},

"Value1": {

"type": "string"

},

"Value2": {

"type": "string"

},

"Value3": {

"type": "string"

},

},

"required": [

"@@odata.etag",

"ItemInternalId",

"Value1",

"Value2",

"Value3"

]

}

}

4) create CSV

-> Creates an error... 

the output itselfs does show that values come in from the parse json but i cant get the implemented

Error being "from" is an "object" and needs to be an "array"

[Kastore]

So i did some try and error and made it so far by using a variable and adding an array to the variable.

However now i get the array with the brackets and the " in the array which i cant use in the CSV. I only need value1,value2,value3 .. and i get the values "twice" in row .. so: "[""value1,value2,value3"",""value1,value2,value3""]"

[abm]

Hi @Kastore 

I need to understand bit more about your data. Check your email. 

Thanks

[eliotcole]

Hi, @Kastore, it would really help:

1. For us to see your flow visually (you can obfuscate / blank anything private)
2. For you to say what you need the files in CSV for.
3. For you to say if you need to do anything with the CSV files afterwards.

For the first one, please edit your initial post with the screenshots.

The problem is that excel files can contain *so* much information, that simply making CSVs out of them isn't always an easy solution, not least because interacting with Excel is such a pain.

Additionally, to use basic Power Automate functions, like the CSV creation tool, it needs a table in the excel sheet with which to make the CSV from.

So you might think that's fine, I'll just automate adding a table around the data in the excel sheet, right? Wrong, because you have to check to see if the excel sheet already has a table on it.

At this point you're already a few layers deep in logic and you haven't even started to do anything, yet.

---

Separately, and not related to any resolution here, I would also advise one thing above all ... consider what the original usage of the excel file is. Can whomever or whatever is making this file be made to output the information to a different endpoint?

This is because excel sheets are really not supposed to be data storage, they are complicated calculators. At their most simple, they're a useful visual tool for some information, but still it's often best off being interpreted better and more succinctly for a given purpose.

So if you can have the data be input into a SharePoint list, or access an API endpoint that is creating the excel file, or if the file is being made by a person, can they input their data into a Microsoft Form? All of these will capture data more accurately and allow for better processing down the line.

---

Anyway, if you have a premium power automate subscription, I would recommend looking at some of the connectors that do all the hard work for you, like cloudmersive. Their xlsx to csv works really well.

However you'd be wise to look at Graph calls, and in particular the /usedRange endpoint.

https://graph.microsoft.com/v1.0/sites/%7BSITE_ID%7D/drives/LIBRARY_ID/items/FILE_ID/workbook/worksheets(%27%7BSHEET_ID%7D%27)/usedRange

I'd add that some of those IDs are non-trivial to obtain.

[Kastore]

Thanks for your feedback. I try to answer everything 🙂

0. Goal

Orderform in excel sheet gets sent to me -> gets uploaded to OneDrive -> Data from the fixed table, with fixed column names gets copied into a .txt file in the style of CSV.

Final file should have:

Avalue1,Avalue2,Avalue3

Bvalue1,Bvalue2,Bvalue3

.

.

.

How to get there does NOT have the be via CSV. It was just the easiest way i got the data from an existing Excel and therefore used it thinking i can use the same commands 

0.5 Why Excel?

We use excel since we create a datapool in the backend to have the orderform spill out the "name of the product" and the "price". 

I do however in the same file create a table since I saw that PA needs a handle of "where to look" which works only with a table in excel. The table is just a =a1 =b1 =c1 and so on (well it is bit more complex.. but you get the idea)

Means/Attempts:

1. Screenshot of the output from Excel. I deleted the others since i got stuck and could get my head around what i was trying to do and couldnt explain it afterwards

Next steps had been

2. and 3.)

The CSV will be used to import to another program (not MS) and requires to have a CSV-Textfile. 

The final data needs to be:

Avalue1,Avalue2,Avalue3

Bvalue1,Bvalue2,Bvalue3

.

.

Excel Sheet:

The excel sheet is used to replace an old order form. It would be send to people, they put in the numbers and send it back to us. At that point the file should get uploaded and then processed.  

Therefore: the excel sheet has a fixed table with fixed names which gets filled out. So the data is there and is also accessable as seen above

Since i CAN create a CSV straight out of a Excel Table (get rows -> create CSV -> use the table values in that step) i thought this would "simply" be a case of manually using the outputs since that is the exact same thing i am getting from a "get row" regardless of an already existing or an newly created file.

e.g. I am able to use the "table1" as custom value in the "get row" step - hence i thought i can use "outputs('get_rows')(['body/value1'])"* in the CSV step.. which unfortunately does not work.

*(not sure about the syntax but you know what i mean)

JSON (never heard before.. so no idea what i am doing here)

So i went over to JSON and thought - hey if i get the parse JSON then the values are "defined" by JSON. That led to the issue of getting a file with ["value1","value2","value3",...] since JSON has strings in it... The workaround here would be to somehow replace the " and the [] with nothing but that I was unable to do so as well.

[Kastore · Accepted solution]

Finally got it to work...

worked with one variable that starts of empty. (the 2nd variable is just a new line so the file gets put out as a new line for each excel-row)

The JSON values then get input into the "string value" seperated by comma and then added to the variable that started off empty (var1)  -> value1,value2,value3

I then replace every " with nothing get the final result of the txt file with comma seperated values in a new line for each row in excel in the var1

(the last compose was there just to check the information - got deleted in the final step)