search and replace text from multiple excel rows and columns into an html template

[Tonygets]

Hi,

I am trying to create an automation that copies and pastes the product data contained in excel from my website into an HTML email template. The automation will match text data from a cell in excel to text data in an HTML template. The automation will then copy a specific cell from the active excel row over the matching text variable in the html template.

I have attached the sample excel file and the html template file.

Step 1) match text data in Column A starting at row 2 of excel session to text data in HTML template file and if there is a perfect match then past text data from Column B of the active row over the matching text data in the HTML file.

Step 2) for the same active row, match text data in Column C of excel session to text data in HTML template and if there is a perfect match then past text data from Column D of excel over the matching data in the HTML file.

Step 3) for the same active row, match text data in Column E of excel session to text data in HTML file and if there is a perfect match then past text data from Column F of excel over the matching data in the HTML file.

Step 4) for the same active row, match text data in Column G of excel session to text data in HTML file and if there is a perfect match then past text data from Column H of excel over the matching data in the HTML file.

Step 5) for active row, match text data in Column I of excel session to text data in HTML file and if there is a perfect match then past text data from Column J of excel over the matching data in the HTML file.

Step 6) Loop through all rows until there is a blank row in the excel document.

Additional Conditions: If there is any blank data in an active excel row/column or in the HTML file (i.e. no match) write the data ‘no match’ in Column K for the active row in the excel session and continue to the next column or the next row (it does not matter if there are 1 or more failed matches within the row, we just need to write ‘no match’ once in Column K and we will investigate the entire row).

Thanks and regards.

[forumsupport]

Hello,

Can you please tell precisely where do you need to insert the data in html template file and how?

[Tonygets]

Hi,

I am not sure what part of the explanation I posted needs clarification.

I have therefore created and attached an image file that shows what I am talking about.

Please read my post below and follow what is going on in the image file.

Here is another summary that may also help:

We would like to exactly match the text in Column A with the exact same text string in the HTML template code. If a perfect match is found then the automation will replace the text string in the HTML (that was matched with Column A) with the text in Column B (of the same row).

The automation will then move to the next column, Column C and repeat until the relevant columns have been matched and the relevant matches have been replaced.

If the text cannot be matched then the automation will write the text 'no match' in column K of that row and move to the next column. If there are multiple 'no matches' the automation will only write the 'no match' once for the row. In addition the automation will keep running through the columns and the rows even if there are multiple 'no matches' until there is a blank row in the excel document.

Please let me know if you have any further questions?

Kind regards.

[forumsupport]

Hello,

Attached is the sample as per the requirement.

To run this task,

. Please copy .atmn under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.
. Copy .xls and .html file under C:\.

Hope that helps.

[Tonygets]

Hi,

I have the automation working now however there are a couple issues.

1) Issue 1 is that if a value is not found there is a windows popup saying 'cannot find xxx value' and this is halting the automation and causing an error.

2) Issue 2 is that when the value in 1) cannot be found the automation is writing the corresponding value next to the previous value instead of moving to the next column and writing 'No match' in column K.

3) There is a 'pause' entered at the end of each lookup - does this need to be there?

4) The automation is writing a 1, 2, 3 etc in column K and not the 'no match value' - I am not sure what these numbers are for?

Thanks and regards,

Anthony

[forumsupport]

Hello,

Please copy attached amended task under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder and run it.

Hope that helps.

[Tonygets]

Hi,

Thank you for the automation. It is almost working perfectly.

The last issue I have found is that the 'find' feature searches either 'down' or 'up' within the notepad. The HTML is written in a 'non logical order' so the automation is not finding many of the cells to write.

Can you please modify the automation to search with the find function both 'up' and 'down' within the HTML before it moves to the next cell?

Thanks and regards.

Tony

[forumsupport]

Hello,

What we can do is, move the cursor to the beginning of file every time the search begins so that way task can search keyword in entire html file.

We have modified the task as per your requirement.

Hope that helps.