Scraping to Excel: Exporting the Results

Collecting the data is half the task. The other half is delivering it in a form people can work with, and there are non-obvious traps.

Choosing a format

A spreadsheet. For humans: viewing, sorting, filtering. Types survive, encoding is not a problem, and it can be formatted.

CSV. For passing between programs. Simple, opens everywhere, and responsible for most of the problems: encoding, separators, quotes inside values.

A database. For volume and ongoing use. Mandatory when data updates and history matters.

JSON. For nested structures that do not fit a flat table.

A common mistake: using a spreadsheet as storage. At tens of thousands of rows it becomes slow, and with regular updates, fragile. The right arrangement at volume is data in a database, spreadsheets generated on demand.

Common problems

Encoding. The classic: unreadable characters instead of letters. The file was saved in one encoding and opened in another. Fixed by specifying the encoding on import or saving to a spreadsheet format directly.

Product codes becoming numbers. Leading zeros disappear and long values turn into scientific notation. Such fields must be text - mark them on import or save directly to a spreadsheet format with types set.

Dates guessed wrongly. Anything resembling a date is read as one, sometimes in a foreign format. Particularly unpleasant with codes like “5-10”.

Separators. A value containing a comma breaks CSV unless escaped. Quotes inside a value do the same.

Line breaks inside a cell. A description with paragraphs turns one row into several.

Numbers with spaces and currency symbols. Convert at collection time rather than later in the editor.

The general rule: convert data to the right types at collection time rather than hoping the spreadsheet guesses. It will guess, and wrongly - see scraping in Python.

Export structure

What to include besides the data itself:

• Collection date and time. Without it, a month later nobody knows how fresh the data is.
• The source for every row, especially when collecting from several sites.
• Collection conditions where they matter: region, price type - see scraping products.
• A link to the original page. It makes manual verification possible and saves enormous time when investigating discrepancies.
• A problem flag. Rows where some fields failed to collect must be marked rather than look complete.

A separate summary sheet or file - record count, error count, run time - turns an export into a report that shows whether the data can be trusted.

Regular updates

If the export is not a one-off:

Do not overwrite the file. Keep versions or write to a database and generate the spreadsheet. An overwritten file cannot be compared with its predecessor.

Flag changes separately. What appeared, what disappeared, what changed is usually more valuable than a full snapshot.

Automate delivery. A file that must be fetched by hand will stop being fetched. Emailing it or dropping it into a shared folder on a schedule solves that - see workflow examples.

Notify on failure. A missing file must be noticeable. An empty report beats an absent one: it shows the system ran.

The overview is in the scraping guide. Getting a table straight off a page: scraping tables.