Answered

List to Data Table

(0) Share

Report

Posted on by Raju25

I have Some lists which have many row values, I want to add those lists to individual columns in data table and write in excel.(Please avoid looping).

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Raju25 ,

As per my understanding what you have elaborated, and I have created a solution.
Please find the attached solution.
Pre-requisite: Create an Excel Template with 2 sheets.

Code:
Variables.CreateNewList List=> List
SET List TO ['Vishnu', 'Vardhan', 'Reddy']
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\vr79540\\OneDrive - Cyient Ltd\\Desktop\\Power Automate Desktop\\Practice\\Text File\\Output.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: List Column: $'''A''' Row: 1
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Const xlUp = -4162
Const xlToLeft = -4159
Dim objExcel, objWorkbook, objSheet
Dim lastRow, rowData(), i
\' Create Excel objects
Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Text File\\Output.xlsx\") \' Update the path to your Excel file
Set objSheet = objWorkbook.Sheets(1)
Set objSheet2 = objWorkbook.Sheets(2)
\' Find the last used row in column A
lastRow = objSheet.Cells(objSheet.Rows.Count, 1).End(xlUp).Row
\' Resize the array based on the number of rows
ReDim rowData(lastRow)
\' Read data from Excel sheet (assuming data is in column A)
For i = 1 To lastRow
rowData(i) = objSheet.Cells(i, 1).Value
Next
\' Write data to Excel sheet in a single row
For i = 1 To lastRow
objSheet2.Cells(1, i).Value = rowData(i)
Next
\' Clean up
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing''' ScriptOutput=> VBScriptOutput2

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

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Raju25 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your fast reply, here i want to write two or more lists to the excel or data table without using looping and more than one write to excel action. Each list have one column and many row values.
My flow scenario: I have used run javascript action and collected lot of data in form of array but you know for run javascript action stores output variable in a string. I converted it to lists using split text action. Like that i have gathered many lists. I want to combine those lists into one data table (each list in one column). Please provide a solution without using looping and more than one write to excel action.

Was this reply helpful? Yes No
Verified answer

kinuasa 799 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
How about adding a column to a data table using the “Run .NET script” action, as shown below?

SET UserDataTable TO { ^['Name'], ['Cart'], ['Tammy'], ['Jimmy'], ['Aloisia'], ['Kearney'] } SET GenderList TO ['Male', 'Male', 'Male', 'Female', 'Male'] SET ColumnName TO $'''Gender''' Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $'''srcTable.Columns.Add(colName, typeof(String)); for (int i = 0; i < srcList.Count; i++) { srcTable.Rows[i][colName] = srcList[i]; }''' @'name:colName': ColumnName @'type:colName': $'''String''' @'direction:colName': $'''In''' @'name:srcTable': UserDataTable @'type:srcTable': $'''Datatable''' @'direction:srcTable': $'''InOut''' @'name:srcList': GenderList @'type:srcList': $'''List''' @'direction:srcList': $'''In''' @srcTable=> UserDataTable2

Was this reply helpful? Yes No
Raju25 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for providing the efficient solution which consumes less time than anything else, may i follow you in any social media.

Was this reply helpful? Yes No