Today, the support for files in XLS, XLSX and CSV format has been added to the Diggernaut platform. The way it was implemented is same as for other supported file types. You load a file into the digger using the walk command, the digger gets the file, determines its type and converts it to XML. Next, you can traverse the DOM structure, extract the necessary data and create your data set.
Let’s see how exactly it works by an example. To do it, we uploaded 3 files to our sandbox:
https://www.diggernaut.com/sandbox/sample.csv – CSV data file
https://www.diggernaut.com/sandbox/sample.xls – XLS data file (binary version)
https://www.diggernaut.com/sandbox/sample.xlsx – XLSX data file (XML version)
We’ll code a straightforward digger configuration that loads the file and show us the source code of the converted data in debug mode.
---
config:
debug: 2
agent: Firefox
do:
- walk:
to: https://www.diggernaut.com/sandbox/sample.csv
do:If we run the digger in debug mode, then in the log we can see the following XML source page with data:
First
Last
Pcode
Political Party
Smith
Fred
A
Democratic
Robbins
Terry
1
Green
O'Neill
Susan
B
Republican
Parker
Scott
D
American Independent
Perkins
Ralph
D
American Independent
Talbot
Angie
7
Middle Class Pty
Since there is only one sheet in CSV, we have a single sheet element in the resulting structure. In XLS / XLSX, there can be many sheets, and all of them are kept in the corresponding sheet elements. It’s quite easy to parse this structure, go through the sheets, then go through the rows row and extract the data from the columns column. The values in the classes correspond to the row and column number in the original file.
Let’s now see how the XLS resource will be converted:
---
config:
debug: 2
agent: Firefox
do:
- walk:
to: https://www.diggernaut.com/sandbox/sample.xls
do:We get the following source code:
First
Last
Pcode
Political Party
Smith
Fred
A
Robbins
Terry
1
O'Neill
Susan
B
Parker
Scott
D
Perkins
Ralph
D
Talbot
Angie
7
PARTY CODE
NAME
1
Green
2
Reform
3
Whig
4
Islamic Political Party of America
5
Rock & Roll
6
Natural Law
7
Middle Class Pty
8
Humanist
9
Pragmatic
10
Conscious American African Party
11
Parliament Party
12
United Conscious Builders of the Dream Party
13
The Egalitarian Party
14
The Humanitarian Party
15
Scientifically Evolving University Party
16
God, Truth & Love Party
17
Superhappy Party
18
Working Families Party
A
Democratic
B
Republican
C
Decline to State
D
American Independent
E
Citizen Party
F
Communist
G
Conservative
H
Environmentalist
I
Ind. Progressive
J
Liberal
K
Peace & Freedom
L
Prohibition
M
New Economy
N
Socialist
O
Socialist Labor
P
Pot Party
Q
Libertarian
R
Amer. Natl. Socialist
S
Poor People’s Party
T
Free
U
National
V
Constitution Party
W
Vision
X
Puritan
Y
Federal
Z
Misc.
As you can see, in this file we have 2 sheets, and the rest is the same structure as in the case of CSV. If we load XLSX, we get precisely the same result as with XLS, so we omit this test.
How can you use this functionality, except for the actual parsing of the final data? Alternatively, you can use spreadsheets as a feed with the resources your digger should scrape. For example, you add a list of links to products in the store to the sheet. Your scraper reads the sheet, picks up the list of URLs, puts them into the pool, and then the main logic of the scraper is used to collect the data about the goods. Alternatively, imagine that you have a spreadsheet with data that must be extended with the data from the web. Your scraper reads the sheet, go through it line by line and form a new dataset, for each line it can visit some page and extract some additional information to keep in the new dataset. This way you will have data from the spreadsheet and the product page merged in a single entry. There are other options for using the spreadsheets, but we can talk about it next time.
One Reply to “Extract data from XLS, XLSX and CSV files”