Edit

Table scraping workflow

Script overview

On the high level the script consists of the following actions.

1. Open the website.
2. Get the table data line by line.
3. Create an .xlsx file and save the table.

Let's explore these steps in detail.

Script workflow

Scrape table from website and save to Table variable

This reusable item will help you to quickly retrieve data from a table on a web page and save this data in the .xlsx format. You can use this usage scenario in your project, for example, to collect data from a website, save tables in an Excel file and compare the data.

1. The Open Website action opens a website or a page with a table for cleaning, but it uses exception blocks to check the browser installed on your computer, and if it cannot find the first browser, it launches the next one. The URL must be specified in the str_website variable.

2. The Switch to Browser action switches to an open browser.

3. Scrape table from the website and save to a variable of Table type (reusable). The reusable element gets the text data from the table on the web page and copies it into a variable of the Table type. The group must be inside the the Open Website or Switch to Browser action.

note

Input data:

• str_table_xpath – the XPath to the table element on the page
• str_btn_next_xpath (optional) – the XPath to the next page button
• num_how_pages– the number of pages you want to scrap; if you want to scrap only one page, set the value "1" for the variable and you cannot set the value str_btn_next_xpath

Output data:

• table_result – the type of the Table variable with data from table or tables

4. The Web Element action gets the table headers and saves them as a list in the list_headersvariable. The str_table_xpath variable contains the xpath of the table on the page.

5. The condition checks whether the list with headers is empty. We do this because some tables may contain headings in the tbody element, and not in thead.

6. The Expression Value action adds the list with headers to the result table as a row.

7. The While cycle repeats the actions until the last row in the table on the last page is processed. When the expression inside the loop returns false, it will go to the next step after the loop.

8. The Exception Handling block is needed to catch an error while retrieving data about a row.

9. The Web Element action gets the information about the current row and saves them as a list in the list_rowvariable. The str_table_xpath variable should contain the XPath of the table on the page.

10. The Expression Value action increments the num_count variable by one to work with the next line in the next loop step.

11. The condition checks whether the list with row data is empty. If the expression inside the IF block returns true, we go to the next page or finish the work. If it returns false, we add a new row to the table.

The condition checks whether this page was the last. If it is true, we finish the work, but if it is false, we go to the next page.

12. The Constant Value action sets the opposite value to the boolean_last_row variable and, as a result, the while loop stops its operation.

13. If the current page was not the last, we increase the variable num_page_count by 1. The num_page_count variable contains the number of the current page.

The action updates the num_count variable, since on the next page, we start processing the page from the first line.

14. The Exception Handling block is necessary to catch the error when you click the button to go to the next page. If we catch this error, the boolean_last_row variable is changed to negative to stop the cycle.

15. The Click Mouse action is required to click a button to go to the next page. The str_btn_next_xpathvariable should contain the XPath of the next page button. If the button does not exist, leave this variable empty.

16. The Constant Value action sets the opposite value to the boolean_last_rowvariable and, as a result, the while loop stops its operation.

17. The condition checks whether the table is empty. In case the table is empty, it adds the current row to it. If it is not empty, the next check is started.

When scraping two or more pages, you have to check the lines for a match with the line with headings. Without this check, there will be more than one line with headings in the result table.

18. The Expression Value action adds the list with row data to the result table as a row.

Create Excel file and save results

Description: In this part, we create an Excel file to save the table in it. You can use this algorithm in your projects, add steps between creating an Excel file and saving the result, and use these parts separately.

1. Create an empty .xlsx file (reusable). The action creates an empty .xlsx file. This reusable element will be useful in your own projects, as you cannot create a file with the .xslx format using cmd. Also, it allows you not to create an .xlsx file before each example run. The example will help solve the problem when in some situations you don’t know whether to create a file. Before starting projects with this case, Excel should be closed.

note

Input data:

• str_xlsx_file_path – the path to the directory where you want to create a new .xlsx file or open an existing one, for example: C:\TablesFolder (without a slash at the end)
• str_xlsx_file_name – the file name; if a file with that name does not exist, it will be created, for example: testFile (without a format at the end)

2. The Check Existence action checks if the file you want to create exists, and stores the result in the boolean_xlsx_file_existvariable. The Path to file or folder field specifies the path to the file from the directory path and file name.

3. The condition compares the boolean_xlsx_file_exist variable with false. If the expression returns true, a new .xlsx file is created.

4. The Create File/Folder action is necessary to create a folder if the path to the folder in which you want to create a new Excel file does not exist. In the “If file or folder already exists” field, select “skip”, because if the folder exists, we must proceed to the next step.

5. The Enter Keystrokes combination allows you to switch to the desktop, as afterwards the combination Win + R is used.

The action calls the Run window, types the command to open Excel with an additional parameter and presses Enter.

• / p"directory path" – the default path to save the file

6. The Wait action sets the waiting period to 5 seconds (5,000 ms), providing time to start Excel.

7. The Enter Keystrokes combination Ctrl + n opens the table if it is not created. If Excel is opened on the home page, the combination does nothing, so we press the Escape button to exit the table initial page.

8. Press the Enter Keystrokes combination to open the Save As window and wait for 3 seconds to open this window.

9. The Window action switches to the Save As window using the Select an open window mode.

10. The Enter Keystrokes actions type the file name (as we set the default path at startup) and press Enter.

11. The Window action switches to the window that has the file name in the title, using the Enter window title or part of title mode.

12. The Wait and Close Window actions are waiting to switch to the Excel window and close it.

13. The Open Spreadsheet action opens the .xlsx file where you want to save the scraped table. Do not forget to check the Save file after the last action option.

14. The Set Range action sets the type of the Table variable in the range that you specified in Options. In this example, we set the table_resultvariable as the variable contains the scraped data. If the To cell field is empty, the value is set automatically using the table dimensions.