Start (re-start) processing Excel on Row# (Prompt User)

Help Studio
,
1

I think this may be of interest to many people if it gets a clean solution :slight_smile:

I have several attended processes that loop through rows of a datatable extracted from large excel sheets (doing data entry). The process prompts the user for the spreadsheet and environment to apply it to.

Sometimes the process needs to be stopped, or it is interrupted.

Manual restart currently involves:

    1. Copy spreadsheet.
    1. Manually remove processed rows.
    1. Process the copy.

Desired improvement:

  • Prompt for startingRow : “Start processing on row: (Default is 2)”

So when the process starts, it would skip all previously processed rows, and begin where the user directs. I’d love to get this functionality working because I would apply it to several tools.

I tried “ExcelReadRange” where I specify the range as

“A” + startingRow

That gets me all the correct data rows BUT it does not retain the column names in the datatable.

I tried reading the spreadsheet once and assigning the

dataTable = dataTable.Clone()

…to get the headers, then using DataTableMerge to re-combine the headers with the content. That has yielded similarly bad results.

I tried coming up with a FilterDataTable model - no luck.

I tried coming up with a creative “range” statement for the original ExcelReadRange to pull in the 1st row for headers, and then include “A” + startingRow again, but had no luck formatting a working range statement model.

I have to believe this is not an obscure need, I’m just looking at it wrong. Can someone share if they have had luck with something like this previously?

1 Like
2

Hi,

If we can know number of rows to skip, the following will work.

First, read whole data with header as datatable using ReadRange activity (Let’s say dt)

Then, if we want to skip 5 rows, for example,

dt = dt.AsEnumerable.Skip(5).CopyToDataTable

Hope this helps you.

Regards,

3 Likes
3

Yoichi, I cannot begin to tell you how much this helped me. THANK YOU!
In setting up my test of your solution, I also found that MY TEST was actually invalid!
I was using ExcelWriteRange which apparently does not output the header!

So while I was initially dismayed at my results, I reconsidered my test and made a more realistic test with a ForEach loop and message box displaying the row contents and SUCCESS.

I have included screenshot of success, and will upload my test code for anyone to re-use.

TestData_and_Successful_outcome
TestData_and_Successful_outcome427Ă—944 27.8 KB

Main.xaml (11.2 KB)

4

You could just store the last processed row number in an Asset and then read it before starting. Then you don’t have to prompt the user.

5

Paul,
That is a very nice addition/evolution of this solution now that the basic need is addressed.

The basic fix as described was super easy to incorporate into my code and is already working/tested - I’m thrilled.

I do like the added idea of storing the value to change the default, but since some sheets might need to be processed starting on a specific row, I do still like prompting.

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.