Converting Column Data to Row Format in Excel Using Power Automate: Seeking Help

[PAD_Noob]

I have extracted some data using web scraping and it is currently written to an Excel file in a column format like this:

Example

1

2

3

4

However, I want to convert this data into a row format so that it looks like this:

Example, 1, 2, 3, 4

What settings in the "Write to" step can I do to achieve this?

Thank you for your help! 

[Steve48]

Write  a loop to iterate through the columns

[UshaJyothi20]

Try by following this 

Launch excel sheet

Read excel sheet 

Loop through it by for each

And then use write to excel sheet inside cureach

Give value to write currentitem[columnname or index]

This is the basic method 

However you can use linq also 

Hope this helps

Usha

[UshaJyothi20]

I have created a flow as per your requirement

please follow the steps

Hope this helps

Usha

[UshaJyothi20]

This is how to give write to work sheet activity

Hope this helps

Usha.

[Srini007]

Hi @PAD_Noob 

Check below link for your reference

https://www.youtube.com/watch?v=4eTsTJzuWUc

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Regards,

Srini

[Agnius]

This is extremely inefficient. If you then end up writing it as a single data row anyway, there is no point to add those items into a list in the first place. You could simply target each of the values in the Write to Excel worksheet. Also, this approach assumes there is a finite number of values to write, which is not robust at all.

The correct approach would be building a loop that writes each value individually in a separate cell and incrementing a column index. Somewhat like this:

1. Loop from 0 to %DataFromWebPage.RowsCount-1%, increment by 1, store the index into %LoopIndex%
   1. Write to Excel worksheet the value %DataFromWebPage[LoopIndex][0]% (as it is probably a data table with a single column) into row 1 (or whichever row you want to write it to) and column %LoopIndex+1% (because LoopIndex is 0-based, but Excel columns are 1-based).
2. End loop

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[UshaJyothi20]

As @Agnius  Said I tried that method too

Hope this helps

Usha

[PAD_Noob]

Okay, this has worked in terms of writing my web data to Excel in a row form. However, I have a loop going through different links scraping the same data, and it is overwriting row one. How can I make sure that once a row is done, it goes on to the next available row to write? Thanks 

[PAD_Noob]

@Agnius @UshaJyothi20 
Here are my steps that are reading from an Excel workbook where I have links stored in it. I have a for each going through each link and grabbing the specific data elements for each web page I want. Now I am just curious about how to not overwrite the first row and to make it add a new row for each instance of data pulled for each URL.

[UshaJyothi20]

Before you use write to excel sheet 

Use first free row/firstfree coloumn activity and get that first free row and free coloumn in variables give these varaiables in write to excel sheet coloumn and row values.

Hope this helps 

Usha

[Agnius]

You can either use Get first free row/column from Excel worksheet as suggested by @UshaJyothi20 above, or use another index for the row. If you were using a simple Loop instead of For each for the parent loop, just like you did with the nested loop, you would have two loop indexes and could use the one from the parent loop to indicate a row to write to - the same as what you did with the column index.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[PAD_Noob]

Can one of you show me a picture example? I have tried to do get first free column/row from Excel worksheet and changing the variables in the Write to Excel worksheet to those and still nothing 😞 

@Agnius @UshaJyothi20 

[Steve48]

Can you post a screenshot of your existing flow

[Agnius]

My suggestion follows this route:

You essentially use a different loop to iterate through your Excel data. You then need to get the URL in a different way, but you can also use the index as a row index in Write to Excel worksheet.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[PAD_Noob]

I am looping through each URL in ExcelInstance1 scraping the data then trying to store the scraped data in a new row for each URL

[PAD_Noob]

Okay, this works to an extent, but it is only going out to my first URL in "ExcelData" and returning the values from that URL, and it is not looping back through the other URLs that are in "ExcelData." So how can I do this so that it goes out and returns the scraped data from the other URLs? @Agnius 

[Agnius]

That shouldn't be the case. Are you sure you followed it exactly the way I showed? Can you share a screenshot of your flow?