Input from CSV

This script is extremely useful because it's purpose is to enable you to read inputs in from a csv list. For Example, if you wanted to input all 50 state abbreviations as input parameters for a scrape then this script would cycle through them all. Furthermore, this script truly begins to show the power of an Initialize script as a looping mechanism.

This particular example uses a csv of streets in Bristol RI. Each street in Bristol is seperated by commas and only one street per line. The "while" loop at the bottom of the example retrieves streets one by one until the buffered reader runs out of lines. These streets are stored as a session variable named STREET and used as an input later on. Each time the buffered reader brings in a new street it blasts the last one out of the STREET session variable.

Reading in from a CSV is incredibly powerful; however, it is not the only way to use a loop. For information on how to use an array for inputs please see the "Moderate Initialize -- Input from Array".

The next script (below) deals with input CSV files that have more than one piece of information per row (more than one column).

Read CSV

Sometimes a CSV file will use quotes to wrap data (in case that data contains a comma that does not signify a new field). Since it's a common thing to do, a script to read a CSV should anticipate and deal that that eventuality. The main workhorse of this script is the function. By passing a CSV line to it, it will parse the fields into an array.

Alternatively you can read the csv via the opencsv package that is included with screen-scraper. This may be more robust for different formats of csv

Input from array

The following script is really useful when you need to loop through a short series of input parameters. Using an array will allow you to rapidly develop a group of inputs that you would like to use; however, you will need to know every input parameter. For example, if you wanted to use the following state abbreviations as inputs [UT, NY, AZ, MO] then building an array would be really quick, but if you needed all 50 states it would probably be easier to access those from a csv (need to know how to use a csv input? check out my other post titled "Moderate Initialize -- Input from CSV").

Input from multiple files

Many sites requiring the user to input a zip code when performing a search. For example, when searching for car listings, a site will ask for the zip code where you would like to find a car (and perhaps distance from the entered zip code that would be acceptable). The follow script is designed to iterate through a set of input files, which each contain a list of zip codes for that state. The input files in this case are located within a folder named "input" in the screen-scraper directory. The files are named in the format "zips_CA", for example, which would contain California's zip codes.

Simply Set Variables

When a Scraping Session is started it can be a good idea to feed certain pieces of information to the session before it begins resolving URLs. This simple version of the Initialize script is to demonstrate how you might start on a certain page. While basic, understanding when a script like this would be used is pivotal in making screen scraper work for you.

The above code is useful where "PAGE" is an input parameter in the first page you would like to scrape.

Occasionally a site will be structured so that instead of page numbers the site displays records 1-10 or 20-29. If this is the case your Initialize script could look something like this:

Once again "DISPLAY_RECORD_MIN" and "DISPLAY_RECORD_MAX" are input parameters on the first page you would like to scrape.

If you feel you understand this one, I'd encourage you to check out the other Initialize scripts in this code repository.

U.S. Zip codes (CSV Files)

The following files contains zipcodes for the that state. The file "zips_US.CSV" contains all US zip codes within one file. If you wish to download all of the CSVs at once you may choose to download the file "zips_all_states.zip".

Note: If you've forgotten the state abbreviations please visit http://www.usps.com/ncsc/lookups/usps_abbreviations.html

Last updated 5/8/2008

Forms

The form class can be a life saver when it comes to dealing with sites that use forms for their inputs and have a lot of dynamic parameters

There are really only two cases in which using the form class is preferrable to doing the paramenters any other way. Those cases are:

1. The page is using a bunch of dynamic parameters (number of keys and/or names of keys changing)
2. This goes with the other, but if you get to a page that has data filled in already you just want to submit as-is, but it won't always be the same

In general though, it'll be easier for debugging if you can stick with the regular parameter tab

Memory Conscious Next Page

If you're scraping a site with lots of "next page" links, you are well advised to use the following script, instead of the other two listed here.

Conceptually, the problem with calling a script at the end of a scrapeableFile, which calls the same scrapeableFile over and over again, is that you're stacking the scrapeableFiles on top of one another. They'll never leave memory until the last page has completed, at which point the stack quickly goes away. This style of scraping is called "recursive".

If you can't predict how many pages there will be, then this idea should scare you :) Instead, you should use an "iterative" approach. Instead of chaining the scrapeableFiles on the end of one another, you call one, let it finish and come back to the script that called it, and then the script calls another. A while/for loop is very fit for this.

Here's a quick illustration of a comparison, so that you can properly visualize the difference. Script code to follow.

Much more effective.

So here's how to do it. When you get to the point where you need to start iterating search results, call a script which will be a little controller for the iteration of pages. This will handle page numbers and offset values (in the event that page iteration isn't using page numbers).

First, your search results page should match some extractor pattern which hints that there is a next page. This helps remove what the page number actually is, and reduces next pages to a simple boolean true or false. The pattern should match some text that signifies a next page is present. In the example code below, I've named the variable "HAS_NEXT_PAGE". Be sure to save it to a session variable. If there is no next page, then this variable should not be set at all. That will be the flag for the script to stop trying to iterate pages.

The script provides to you a "PAGE" session variable, and an "OFFSET" session variable. Feel free to use either one, whichever your situation calls for.

OFFSET will (given the default values in the script), be 0, 20, 40, 60, etc, etc.
PAGE will be 1, 2, 3, 4, 5, etc, etc.

Next Page Link

The following script is called upon completion of scraping the first page of a site's details. This script is useful when matching the current page number in the HTML is preferable or simpler than matching the next page number. Depending on how a site is coded, the number of the next page may not even appear on the current page. In this case, we would match for the word "Next", to simply determine if a next page exists or not. The regular expression used for the word next would be used as follows:

The regular expression for the lone token ~@NEXT@~ would be the text that suggests that a next page exists, such as Next Page or maybe a simple >> link.

The only change you should have to make to the code below is to set any variable names properly (if different than in your own project), and to set the correct scrapeableFile name near the bottom.

Simple Next Page

One of our fellow contributors of this site posted a Next Page script which can be very useful, but may be more code than what you might need. Because every site is constructed differently, iterating through pages can be one of the most difficult parts for a new screen-scraper to master. Indeed, the design of how to get from page to page typically takes some creativity and precision.

One initial word of warning about going from page to page. Occasionally a site will be designed so you can get to the next page at the top and the bottom of the current page. Everybody has seen these before. For example, you're looking through a site which sells DVDs and at the top and the bottom of the list there is a group of numbers that shows what page you are currently viewing, the previous page, the next page, and sometimes the last page. The problem occurs when your pattern matches for the next page before you get to the data you want extracted. If that is the case, your session begins to flip through pages at a very fast rate without retrieving any information at all! Do yourself a favor and match for the one at the bottom of the page.

After you have a successful match, the following script can be applied "Once if pattern matches".

We realize that it is only one line of code, but in many cases that is all that it needs to be.

Iterate over DataSets & DataRecords

Manual Data Extraction

A sub-extractor pattern can only match one element but manual data extraction allows you to give the same additional context information as using a sub-extractor pattern but allows you the ability to extract multiple data records.

This example makes use of the extractData() method.

The code and examples below demonstrate how to first isolate and extract a portion of a page's total HTML, so that a second extractor pattern may then be applied to just the extracted portion. Doing so can limit the results to only those found on a specific part of the page. This can be useful when you have 100 apples that all look the same but you really only want five of them.

The following screen shots show an example of when the script above might be used. In this example, we are only interested in the active (shown with green dots) COMPANY APPOINTMENTS, and not the LICENSE AUTHORITIES (sample HTML available at the end).

When applied to the all of the HTML of the current scrapeable file, the following extractor pattern will retrieve ALL of the html that makes up the COMPANY APPOINTMENTS table above. But, remember, we only want the active appointments.

Use the extractor pattern below to match against the HTML above. It will return two results: 21ST CENTURY INSURANCE COMPANY, and AIG CENTENNIAL INSURANCE COMPANY, since those are the only two active company appointments. Note that the "Appointment" Extractor Pattern includes the word "GREEN", so that the "RED"(Inactive) company appointments are excluded.

Be sure to check the box that says "This extractor pattern will be invoked manually from a script". This will ensure that the extractor pattern will not run in the sequence with the other extractor patterns.

Scrape Only Recent Information

This script is designed to check how recent a post or advertisement is. If you were gathering time sensitive information and only wanted to reach back a few days then this script would be handy. After evaluating the date there will be a section for calling other scripts from inside this script.

Hopefully it is evident that the above code is useful in comparing todays date against a previous one. Depending on your needs you might consider developing a script which will move your scraping session on after it reaches a certain date in a listing. For example if you were scraping an auction website for many terms you might want to move on to the next term after you have reached a specified date for the listings. What are some other ways this script could be useful?

Prepare For Output--Fix Phone

The following script contains a method that you may instead wish to call from within your "Write to CSV" script. The purpose of the script is to put phone numbers into a standard format (123-456-7890 x 1234) prior to output. Note: Be careful when using this script to work with non-U.S. phone numbers, since other countries may have more or fewer digits.

Prepare For Output--Parse Full Name (including suffixes)

The following script proves useful in most cases when there is a need to separate a full name into first name, middle name, surname, and suffixes (if applicable). The suffixes include JR, SR, I, II, III, 3rd, IV, V, VI, VII. The script is also set up to work with names in the "LASTNAME, FIRSTNAME SUFFIX" format.

Prepare For Output--Parse Zipcode

The following code is used to split zip codes from a pattern match. The code below takes a zip code and assigns the first five digits to the variable "ZIP". If the zip code is in the longer format (12345-6789), as opposed to the shorter format (12345), then the second part of the zip code, which comes after the "-" character, is assigned to the "ZIP4" variable (so named for the 4 digits following the "-" character). This script would be useful in cases where zip codes must be standardized.

Prepare For Output--Strip non-numbers

This is a simple script used from removing all non-numerical characters from numbers. This is particularly useful when attempting to normalize data before insertion into a database.

Write to CSV

Probably the easiest way to write to a comma-seperated value (CSV) document is to use screen-scrapers included CsvWriter. If for some reason you can't/don't wish to use the CsvWriter the following code will also accomplish the task. CSV files are very useful for viewing in spreadsheets or inserting values into a database.

Also, you'll notice that the session variables are cleared out at the end of the script. This would be done when you don't want a session variable to persist into the next dataRecord. For more about scope and dataRecords please go here.

Writing extracted data to XML

Overview

Oftentimes once you've extracted data from a page you'll want to write it out to an XML file. screen-scraper contains a special XmlWriter class that makes this a snap.

This script uses objects and methods that are only available in the enterprise edition of screen-scraper.

To use the XmlWriter class you'll generally follow these steps:

1. Create an instance of XmlWriter in a script, storing it in a session variable.
2. Extract data.
3. In a script, get a reference to the XmlWriter object stored in step one, then call addElement or addElements to write out XML nodes.
4. Repeat steps 2 and 3 as many times as you'd like.
5. In a script, get a reference to the XmlWriter class, then call the close method on it.

The trickiest part is understanding which of the various addElement and addElements methods to call.

Examples

If you're scripting in Interpreted Java, the script in step 1 might look something like this:

In subsequent scripts, you can get a reference to that same XmlWriter object like this:

You could then add elements and such to the XML file. The following three examples demonstrate the various ways to go about that. Each of the scripts are self-contained in that they create, add to, then close the XmlWriter object. Bear in mind that this process could be spread across multiple scripts, as described above.

Example 1

This script would produce the following XML file:

Example 2

This script would produce the following XML file:

Example 3

This script would produce the following XML file:

Working with MySQL databases

Consider using the SqlDataManager as an alternative way to interact with your JDBC-compliant databases.

This example is designed to give you an idea of how to interact with MySQL, a JDBC-compliant database, from within screen-scraper.

You will need to have MySQL already installed and the service running.

To start, download the JDBC Driver for MySQL connector Jar file and place it in the lib/ext folder where screen-scraper is installed.

Next, create a script wherein you set the different values used to connect to your database. It is recommended that you call this script from your scraping session before scraping session begins.

Create another script to set up your connection and perform queries on your database. Note, it is necessary to include the connection to your database within the same script as your queries.

You will be calling this script after you have extracted data. Typically this will either be after a scrapeable file runs or after an extractor pattern's matches are applied.

Writing extracted data to a database

Overview

Oftentimes once you've extracted data from a page you'll want to write it to a database. Screen-scraper contains a special SqlDataManager class that makes this easy.

This script uses objects and methods that are only available in the professional and enterprise editions of screen-scraper.

To use the SqlDataManager class you'll generally follow these steps:

1. To start, download the appropriate JDBC Driver connector Jar file for your particular database and place it in the lib/ext folder where screen-scraper is installed.
2. Create an instance of SqlDataManager in a script.
3. Build the database schema and any foreign key relations.
4. Store the SqlDataManager in a session variable.
5. Extract data.
6. In a script, get a reference to the SqlDataManager object stored in step 3, then call addData to build the rows in your database tables.
7. Once all data for a row has been added to the SqlDataManager object, call the commit method.
8. After committing all data related to a record - which can include multiple rows across multiple tables, call the flush method to write the record to the database.
9. Repeat steps 4 - 7 as many times as you'd like.
10. In a script, get a reference to the SqlDataManager object, then call the close method on it.

The trickiest part is understanding when to call the commit method when writing to related tables.

Examples

If you're scripting in Interpreted Java and using a MySQL database, the script for steps 1-3 might look something like this:

Note that if you are using a database other the MySQL, the only change to this script will be the String passed to the setUrl method of the BasicDataSource.

In subsequent scripts, you can get a reference to that same SqlDataManager object like this:

You could then add data to the data manager. The following examples demonstrate various ways to go about that. Each of the scripts assume you already created an SqlDataManager object in a previous script and saved it to the session variable _DBMANAGER.

Saving to a single table using a data record

If the data record saved above had key-value pairs:
NAME = John Doe
AGE = 37
WEIGHT = 160
and the table 'people' had columns 'name', 'age', and 'gender', the script above would produce the following row in the people table.

Saving to a single table manually

If the session variable GENDER had the value male and the table structure was the same as in the example above, this script would produce the following rows in the people table.

Note that you can mix the two methods shown above. Data can be added from multiple data records and/or manually for the same row.

Saving to multiple tables that are related.

This example assumes that you have a table in the database named people with fields 'id' (primary key/autoincrement), 'name', and 'address', and another table named phones with fields 'person_id', 'phone_number'.

Also, there is a foreign key relation between person_id in phones and id in people. This can be setup either in the database or when setting up the datamanger and calling the addForeignKey method.

In order to make it easier to see inserted values, all calls to addData in this example will enter data manually. In many cases, however, adding a data record is much easier.

Also, remember that data does not have to be added and committed all at once. Usually tables with a parent/child relation will have one script called after each pattern match of an extractor pattern that adds and commits a row of child data, and then a separate script called elsewhere to add and commit the parent data.

Note the order in which tables were committed. All data in child tables must be committed before the data in the parent table.

This script would produce the following rows in the database:

The SqlDataManager takes care of filling in the data for the related fields. We never had to add the data for the person_id column in the phones table. Since id in people is an autoincrement field, we didn't have to add data for that field either.

Close the data manager

Once all data has been written to the database, close the data manager like this:

Automatically link many to many relations (Advanced)

The SqlDataManager can be set to automatically link data connected in a many-to-many relation. To enable this feature, use the following code:

When this setting is enabled, the data manager will attempt to relate data across multiple tables when possible. For example, if there is a people table, an address table, and a person_has_address table used to relate the other two tables, you would only need to insert data into the people and addresses tables. The data manager would then link the person_has_address table in since it has foreign keys relating it to both people and addresses. See the example below.

This would produce the following result:

Filtering Duplicate Entries (Advanced)

When extracting data that will contain many duplicate entries, it can be useful to filter values so that duplicate entries are not written to the database multiple times. The data manager can use a duplicate filter to check data being added to the database against data that is added, and either update or ignore duplicates. This is accomplished with an SqlDuplicateFilter object. To create a duplicate filter, call the SqlDuplicateFilter.register method, set the parent table it checks for duplicates on, and then add the constraints that indicate a duplicate. See the code below for an example of how to filter duplicates on a person table.

Duplicate filters are checked in the order they are added, so consider perfomance when creating duplicate filters. If, for instance, most duplicates will match on the social security number, create that filter before the others. Also make sure to add indexes into your database on those columns that you are selecting by or else performance will rapidly degrade as your database gets large.

Duplicates will be filtered by any one of the filters created. If multiple fields must all match for an entry to be a duplicate, create a single filter and add each of those fields as constraints, as shown in the third filter created above. In other words, constraints added to a single filter will be ANDed together, while seperate filters will be ORed.

Scrape Profiler

If a scrape is taking a long time, using the scrape profiler can help you see which scrapeable files and/or scripts are using all the time, so you could optimize their runtimes.

Another reason to consider using the scrape profiler is that there is a function to breakpoint when you overwrite a session variable, so similar to a breakpoint on variable change. Using this you can determine when a session variable is being overwritten when you don't expect it to be.

Example Resource Closer

General Use

Modify Last Response