Adding text to all Excel rows in a column

[garthh]

Hi

I am using Loops 'For each' to add the same text to each excel row in a given column but as there are many rows loops 'for each' takes quite some time.

Is there a quick method to write to excel via List or Datatable?

If so how?

[VJR]

HI @garthh 

In the Write to Excel action you can directly pass a List or a Datatable and it will write its entire contents to the specified cell.

[Kaif_Siddique]

Hi @garthh ,

Here is another possible solution. Use excel as database and run SQL query. SQL queries can retrieve and manipulate significant amounts of Excel data more efficiently.

Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<<File Path>>;Extended Properties="Excel 12.0 Xml;HDR=YES";

Want to learn how to Run SQL queries on Excel files. Refer below link

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

Let me know if any queries.

If you like my response, please give it a Thumbs Up.
If this helps, please Accept as solution.

Regards

Kaif

[garthh]

VJR

Thanks, I'm not that advanced! The number of rows vary each time a spreadsheet is made, how can a list be made to fit the rows?

[garthh]

Kaif

Thank you

Perhaps beyond my level, but I will take a look

[VJR]

Hi @garthh 

In the original post it said "Is there a quick method to write to excel via List or Datatable?"

For this use the "Write to Excel worksheet" action. You dont have to worry about how many rows the list or datatable has. All the rows of the list/datatable will be written to those many rows of the Excel sheet.

But now I think you are asking "how can a list be made to fit the rows?"

"The number of rows vary each time a spreadsheet is made"

"Get first free column/row from Excel worksheet" will give you the total number of rows on a sheet but I am not clear why do you need this when you are writing a list or a datatable to a sheet.

[Kaif_Siddique]

Let me know if that works for you or have any queries. 

[garthh]

VJR

Thanks. Yes I understand "Get first free column/row from Excel worksheet" 

However Write to Excel doesn't provide from/to row parameters?

Theworksheet is a utility that writes to an ERP SQL database. All columns need to have the same number of rows. The other columns are entered by copy and paste from other worksheets

The column causing the issue needs a P in each row based on the row values pasted into other rows 

[garthh]

VJR

Thanks. Yes I understand "Get first free column/row from Excel worksheet" 

However Write to Excel doesn't provide from/to row parameters?

Theworksheet is a utility that writes to an ERP SQL database. All columns need to have the same number of rows. The other columns are entered by copy and paste from other worksheets

The column causing the issue needs a P in each row based on the row values pasted into other rows 

[garthh]

Kaif, thanks

How are the number of rows determined? From/to

The worksheet is a utility that writes to an ERP SQL database and all columns must have the same number of rows

The rows are 9 to "Get first free column/row from Excel worksheet" 

Where can they be set in your suggestion?

[VJR]

Hi @garthh 

There is no directly available action to write something into multiple rows, without making use of loops.

However you can check the below post for copy pasting to multiple cells. 

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/FILLING-DOWN/m-p/1650425/highlight/true#M...

Bascially, it as about writing P to the first cell only and then pasting it to the desired range of cells.

[Kaif_Siddique]

Hi @garthh ,

In my above solution, i am using Excel as database so no need to find the last row.

Below is the sample table in excel which i used in my above solution. Here i am using SQL UPDATE query to update the 'Comments' column. 

Modify the query as per your requirements.

Regards

Kaif

[garthh]

Kaif, thank you

Not as elegant but this works also - and with formulas

[garthh]

VJR

This works also - and with formulas

[VJR]

@garthh, that is the exact approach I have shared with you above in the link as well as in the screenshot :). I have myself tried it out before submitting it to you.

[garthh]

VJR, thanks again and my apology. When looking at many places for solutions this can happen - with me anyway!

[VJR]

@garthh Cool. no worries. So you can now close this thread.