Here we see the File List box. This is where we start to define the site navigation to get to our web pages that contain our data to be extracted.
We will now use the Excel Spreadsheet option.
Select Excel Spreadsheet [recommended] (Intermediate).
When Excel Spreadsheet is selected the "Next" button will become available.
The Get List from Excel box allows us to create a new spreadsheet or browse to an existing spreadsheet.
When creating a new spreadsheet from this box, notice the check box options. They are:
· Load List with Sample Data – This option populates a few rows in column A of the spreadsheet with sample URLs. The navigation URL needs to reside in column A. These are only samples to give you an idea how to build these URLs in Excel. They are not relevant to your particular extraction and should be replace after you review them.
a. Include Sample Formulas – This option will populate other column in the Excel spreadsheet with data that would be variables in a URL, like page numbers for next page links or sample wildcards. This will also add formulas to the sample URLs in column A to show you how to write a formula to concatenate URLs with wildcards.
· Include Column Headers – This will include Column Headers for your reference in Row 1 of the spreadsheet.
· Launch the Spreadsheet in Excel Now – This will launch the spreadsheet for you to edit immediately. If you do not select this box, the example above would save an Excel spreadsheet with Sample Data based on the other check boxes selected. You would need to go back an edit it later before your Package would work properly.
After selecting your check boxes, click "Save As" and browse to the file path you want to put the Excel spreadsheet in and name the spreadsheet. Be sure to include the .xls extension in the name. If you checked Launch the Spreadsheet in Excel Now, the spreadsheet will open for you to edit and save.
We are going to use the ‘…or connect to an existing Excel spreadsheet’ option.
Select the "Browse" button.
An explorer window will open and allow you to browse to an existing Excel file. You can pause here a moment and build an Excel spreadsheet on your own now if you want to follow along. Look below for the exact example of our Excel spreadsheet.
You can also find a copy of the spreadsheet in C:\Program Files\Web Scraper Plus+\Resources\Samples.
Our spreadsheet is located in a folder we created C:\Program Files\Web Scraper Plus+\Downloads\Yahoo Finance\TickerURL list.xls. Create this folder for your copy and place the spreadsheet there.
Browse to the folder, select the spreadsheet, and click "Open".
This a brief note about the Excel spreadsheet we are using for this example.
The File Name is TickerURL list.xls.
The Sheet name is YAHOO_FINANCE.
We have headers in Row 1 Column A and Column B named URL and Ticker Symbol, respectively.
In Row 2 we have the base URL and Query String minus the variable character of our ticker symbols in Column B in a formula that is:
="http://finance.yahoo.com/q/cq?d=v1&s="&(B2)
This shows that Column A, Row 2 has a formula that = (EQUALS) " (open QOUTE, TEXT CHARACTERS are inside QUOTES) http://finance.yahoo.com/q/cq?d=v1&s= (BASE URL plus static part of QUERY STRING) " (close QOUTE, END of TEXT CHARACTERS) & (& is the ADDITION, or JOIN OPERATOR for TEXT) (B2) (represents the TEXT CHARACTERS in COLUMN B, ROW 2).
You could continue with more text and variables if needed. You would need another & after (B2) to join it with text that follows and the text would need to be in "QOUTATIONS". This can get as large as needed.
In Column A, Row 3 and Row 4, we are showing how you can make a series of these URLS. This could be done to keep you from having a very larger results set on any one page, could be used for a page counter variable, or in an example where you wanted one product detail page each having its own unique URL, for multiple products.
Now that we have an understanding of Excel spreadsheets being used as File Lists, let’s continue.
You will notice that when we click "Open" after browsing to our file, the wizard brought us back to this box. Here we want to check the file path and make sure it is correct.
We also want to decide on "The existing file’s first row is column headers" check box. In the spreadsheet we just completed we have headers, so we will check this box.
Click "Finish"