Excel data row for LARGE file

Hey all.

I have an excel document which is from a web scrape of all our competitors on pricing for each product we sell. On each row, it lists the product, our price (marked GRENN or RED depending on if we are better priced or worse) and then across the row: all the competitors and thier pricing for that item. But it only lists prices that it can find from their sites (many of them don’t stock what we do so the scrape has returned a blank cell for that competitor). So a large number of the columns in each row are blank.

I have got an activity already to find lines/cells with RED color (our price is worse than competitor) and an IF activity to filter it.

I’m trying to write that (red color filtered row) to a new DT…Problem is that the rows in 1st DT have massive amounts of columns (We have many competitors, so columns go all the way to letters ET). I don’t want to “Build Data Table” and then add 250’ish columns (i.e column1, column2…column250) and have to add/remove them as competitors open/close thier businesses.

Is there another way to get the entire row into a new DT but not have to build it first? And/or, many of the columns are blank/null, is there some way to only extract the columns (in each red color row) that have data? (competitors the do sell the item we do). This may cut down on the 250’ish columns…but i need to know which column the data was retrieved from (competitors name is header) as well as the data (the price they are selling it at)

Or does anyone have a better idea than building a DT? I need the data to them go onto other workflows of adjusting prices, recording competitor name & price, etc…So it does not NEED to be a DT as the output per se.

Thanks all.

Is it an option to manipulate the data table rather than the Excel sheet? For example, “red” rows literally are items where your prices are worse than your compentitor’s. You could either copy the data table in an Invoke Code activity and manipulate it, or use the Select method to filter the datatable.

1 Like

@redlynx82 Hmmm. I like the idea of Filtering…so stupid i did not think of that…Is it Friday yet!!!

I think i will For each row, filter for color, then another sub “for each item” in row filter for data (null or string is the result).

Do you happen to know the coding to obtain the header value for each column in the row that does have data? (i.e they sell it too)

Any other issues/ideas you think may be helpful good Sir? :slight_smile:

Thanks!

With regard to filtering, here’s what you can do. Imagine this simple workbook:
image

The first assign activity filters items. Here, I drop everything that has an Id smaller than 2:
dtItems.Select("Id > 1")

Since select returns an array of DataRow objects, the second activity is for copying them into another DataTable:
filteredRows.CopyToDataTable()

So, we’ll end up with a filtered DataTable object that you could write back to another range. Read more about selecting items here.

With regard to looping - since the Columns object won’t change (there are no jagged items in a DataTable), you could just iterate over each row, and then over each column to get to the name (you can also access items by name). Here’s an example that outputs each individual cell of a DataTable object:

image

@redlynx82- Hey ya, thanks for that answer. I might have missed something though sorry.

almost every cell in each row has no data (those competitors don’t stock our item). On the few that do have data, i need to get that Column header only, and the data in the specific cell (their price)

Here is an example of DT:
111

EG 1:
Item 1 i want the $80 and Competitor 2’s name. I dont need Competitors 1,3 & 4’s names as they dont qualify.
EG 2:
Item 5 i want the $900 and Competitor 2 & 4’s name’s. I dont need 1 or 2’s…additionally i don’t need the data from comp 3, as it is higher than our price (but i will use an IF for that (ourPrice<thiers).


I have a for each row in DT, and inside there i have the filtering to get only the rows i want (correct color). If they qualify, i then have another for each (datarow typeargument) with a row.itemarray

If cell is null / “” then it will move to next one. If it is not blank, that’s where i need the value of cell and the column name this cell is in…I’m just have trouble figuring that part out sorry.

@MikeBlades

As my understanding, you want the column name which has Specific value.

Inside for each row have an if a condition that,
Row(1).tostring=“specific value” if exits then get the column name
and do the same for next columns.

hope this will help you.

Thanks,
Suresh J