error parsing json file to excel

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

[Deenuji]

@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 🚀

[VishnuReddy1997]

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

[Rahul_skl]

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 . 

[Rahul_skl]

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 .

[VishnuReddy1997 · Accepted solution]

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

[Rahul_skl]

Dear Vishnu,

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

[Rahul_skl]

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

[Rahul_skl · Accepted solution]

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.

[VishnuReddy1997]

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

[Deenuji]

@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 🚀