You’re offline. This is a read only version of the page.
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 .
Even you JSON don't have index we can still achieve your goal using .net scripts.
Please follow the below approach.
Share
Report
All responses (
Answers (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 🚀
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
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
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 .
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 .
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
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 🚀
#1
WarrenBelz
146,776
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,093
Most Valuable Professional
