Answered

error parsing json file to excel

(0) Share

Report

Posted on by Rahul_skl

Hello everyone,

I have a json file in which i have value as key value pairs. I want the key to be the columns in excel file and the values for that key should be in rows. I am getting the error while parsing the json file .Can someone please help me in solving my issue. Please find the attached json file, and the workflows where i am getting error. i successfully extract the keys from json file and put it as columns in excel file but while writing the values for each keys in row i am getting an error . It seems like i cant upload the json file here so i took the screenshot of .jsonfile for reference in text format .

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (2)

Sort by

Deenuji_Loganathan_ 6,250 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Rahul_skl

Please follow the below link

Convert JSON to Excel in Microsoft Power Automate for Desktop (youtube.com)

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No
VishnuReddy1997 2,656 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @Rahul_skl ,

I think your trying to create a Config file flow. For this i have simpler solution than this.
Please find the below solution. Just change the config path.

4 th action is showing how you can call Config variables.

Please place the things in excel as shown below.

Code:
Please copy the below code to your flow.
SET Config_Path TO $'''C:\\Users\\Desktop\\Power Automate Desktop\\Config\\Config File.xlsx''' @@copilotGeneratedAction: 'False' Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\") Set objWorkbook = objExcel.Workbooks.Open(\"%Config_Path%\") Set objSheet = objWorkbook.Worksheets(\"Constants\") intRow = 2 \'start at row 2 since row 1 has column headers strOutput = \"\" Do While objSheet.Cells(intRow, 1).Value <> \"\" strKey = objSheet.Cells(intRow, 1).Value strValue = objSheet.Cells(intRow, 2).Value strValue = Replace(strValue, \"\\\", \"\\\\\") \'replace \\ with \\\\ strOutput = strOutput & \"\"\"\" & strKey & \"\"\": \"\"\" & strValue & \"\"\",\" intRow = intRow + 1 Loop strOutput = Left(strOutput, Len(strOutput) - 1) \'remove the trailing comma strOutput = \"{\" & strOutput & \"}\" WScript.Echo strOutput \'output to the console objWorkbook.Close False objExcel.Quit''' ScriptOutput=> ConfigAsJSON ScriptError=> ScriptError Variables.ConvertJsonToCustomObject Json: ConfigAsJSON CustomObject=> ConfigAsCustomObject Web.InvokeWebService.InvokeWebService Url: ConfigAsCustomObject['API Endpoint'] Method: Web.Method.Get Accept: $'''application/json''' ContentType: $'''application/xml''' ConnectionTimeout: 30 FollowRedirection: True ClearCookies: False FailOnErrorStatus: False EncodeRequestBody: True UserAgent: $'''Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6''' Encoding: Web.Encoding.AutoDetect AcceptUntrustedCertificates: False ResponseHeaders=> WebServiceResponseHeaders2 Response=> WebServiceResponse2 StatusCode=> StatusCode2

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Rahul_skl 2 on at

Like (0)

Report
Copy link

Link copied!

Dear Deenuji,

I exacrly implemented my workflow watching this video only..you can even see all the steps are same but in my scenario i have only one curly braces in json file thats why the value under jsonascustomobject has no index values and its in tabular format so thats why i get this issue .I hope you understand what i meant and the error is because of that because it cant read the currentitem[currentkey] in line no.19 and in video at 8.30 .

Was this reply helpful? Yes No
Rahul_skl 2 on at

Like (0)

Report
Copy link

Link copied!

Dear Visnu, Thanks for your response but i think my scenario is different and the procedures you suggested seems to be too complex for me. I didnt understand .All when i just etxract key from json file i am able to put those keys as columns in excel file but when i start writing the value for that key i could not as its give me an error and thats valid also because jsonobject is not a datatable. But I dont undertand how to read value for those key in second row in their respective columns. you can see the entire workflow of mine .

Was this reply helpful? Yes No
Verified answer

VishnuReddy1997 2,656 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @Rahul_skl ,

You can read value for those key in second row in their respective columns as shown in below image.
For example i have taken display message.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Rahul_skl 2 on at

Like (0)

Report
Copy link

Link copied!

Dear Vishnu,

Yes it can read the value for the key . I tried it .Then there is some issue with my flow it seems.

Was this reply helpful? Yes No
Rahul_skl 2 on at

Like (0)

Report
Copy link

Link copied!

All I think there is some logic related to iterating through the key in jsonascustomobject which seems to be wrong for me.

Was this reply helpful? Yes No
Verified answer

Rahul_skl 2 on at

Like (0)

Report
Copy link

Link copied!

Dear Vishnu, Anyway i was able to get the output but i found the similar scenario posted by one of our member here regarding the same issue.

Was this reply helpful? Yes No
VishnuReddy1997 2,656 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @Rahul_skl ,

That sounds great.The solution provided by me also similar.
Please close the issue by marking it as a solution.

Regards,
Vishnu Reddy

Was this reply helpful? Yes No
Deenuji_Loganathan_ 6,250 Moderator on at

Like (0)

Report
Copy link

Link copied!
@Rahul_skl
Even you JSON don't have index we can still achieve your goal using .net scripts.
Please follow the below approach.

SET StrJsonstring TO $'''{ \"Invoice\": \"982309823-Rev.1\", \"Customer\": \"WWW>AVA-ALMS.DE\", \"Item\": \"skldjslkdl;sdjklsjd\" }''' Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance Variables.CreateNewDatatable InputTable: { } DataTable=> DataTable Scripting.RunDotNetScript Imports: $'''System System.Collections.Generic System.Data''' Language: System.DotNetActionLanguageType.CSharp Script: $''' // Remove whitespace and {} jsonData = jsonData.Trim().Trim(\'{\', \'}\'); // Split by commas string[] parts = jsonData.Split(\',\'); // Create a DataTable dataTable = new DataTable(); // Add columns for each key foreach (string part in parts) { // Split key and value by colon string[] keyValue = part.Split(\':\'); // Remove quotes and trim string key = keyValue[0].Trim().Trim(\'\"\'); dataTable.Columns.Add(key, typeof(string)); } // Add a row with values DataRow newRow = dataTable.NewRow(); foreach (string part in parts) { // Split key and value by colon string[] keyValue = part.Split(\':\'); // Remove quotes and trim string key = keyValue[0].Trim().Trim(\'\"\'); string value = keyValue[1].Trim().Trim(\'\"\'); newRow[key] = value; } dataTable.Rows.Add(newRow);''' @'name:jsonData': StrJsonstring @'type:jsonData': $'''String''' @'direction:jsonData': $'''In''' @'name:dataTable': $'''''' @'type:dataTable': $'''Datatable''' @'direction:dataTable': $'''Out''' @dataTable=> DataTable Excel.WriteToExcel.WriteNamedCell Instance: ExcelInstance Value: DataTable.ColumnHeadersRow CellName: $'''A1''' Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataTable Column: $'''A''' Row: 2

How to copy/paste the above code into your PAD ?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No