Getting Scraped Data into Excel

A surprising number of scraping projects end in a spreadsheet, because the consumer of the data works in Excel. That is a perfectly good outcome, and it has its own set of traps.

The problems are rarely about writing the file. They are about types, encoding and refresh mechanics.

CSV: the default and its pitfalls

CSV is the simplest route and the source of most import problems.

import csv

with open("products.csv", "w", newline="", encoding="utf-8-sig") as handle:
    writer = csv.DictWriter(handle, fieldnames=["name", "price", "currency", "url"])
    writer.writeheader()
    writer.writerows(rows)

Three details in that snippet matter more than they look.

encoding="utf-8-sig". The byte-order mark makes Excel on Windows detect UTF-8 correctly. Without it, accented characters arrive mangled, which is the single most common CSV complaint.

newline="". Required on Windows to prevent blank rows appearing between records.

DictWriter with explicit field names. Explicit headers avoid the silent column-shift that occurs when records have inconsistent keys.

Even done correctly, CSV loses type information. Excel will interpret values on import, and that interpretation is often wrong.

The type problems

These cause real damage because the data looks fine and is wrong.

Leading zeros stripped. A product code of 00789 becomes 789. Postal codes and ISBNs are common casualties. Prefix with a tab or set the column as text on import, and document the issue for whoever uses the file.

Long numbers converted to scientific notation. Large SKUs and identifiers become 1.23E+11, losing precision irreversibly.

Dates reformatted. 2026-01-15 may become a locale-specific date, and a value like 3/4 may become March 4th or the fourth of March depending on the reader's locale.

Currency symbols interfering. A price exported as $12.99 may import as text, so sums silently return zero.

The dependable approach is to export raw, unformatted values with the type implied by the column header, and let the consumer apply formatting. A price column containing 12.99 and a separate currency column is far safer than a single $12.99 string. The normalisation discipline behind this is in Parsing HTML and JSON Reliably.

Writing XLSX directly

If the consumer wants a formatted workbook, generate it rather than exporting CSV and asking them to import it. You control the types, which eliminates the whole class of problems above.

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

workbook = Workbook()
sheet = workbook.active
sheet.title = "Products"
sheet.append(["Name", "Price", "Currency", "URL"])

for row in rows:
    sheet.append([row["name"], float(row["price"]), row["currency"], row["url"]])

# Text format for identifier columns keeps leading zeros.
for cell in sheet["A"]:
    cell.number_format = "@"

for index, width in enumerate([40, 12, 10, 60], start=1):
    sheet.column_dimensions[get_column_letter(index)].width = width

workbook.save("products.xlsx")

The number_format = "@" line is what preserves leading zeros and long identifiers as text. It is a small detail that removes a whole category of support complaints.

Two practical notes: write numbers as numbers so the consumer can sum them, and keep dates as dates with an explicit format rather than as strings, so sorting works.

Power Query for a refreshable workbook

If the consumer wants a spreadsheet they can refresh rather than a snapshot, point Excel at a stable location: a CSV on a share, an Azure blob, a Google Sheet, or an API endpoint.

Power Query then handles the import and can be refreshed on demand. The advantages:

• The transformation is recorded, so column types, date parsing and filtering are applied consistently every refresh.
• The consumer is self-sufficient, which removes you from the loop.
• The raw file stays untouched, so a mistake in Excel does not corrupt the source.

The cost is that a refresh pulls the whole file, and a very large CSV is slow in Excel. Beyond a few hundred thousand rows, a database with a query connection is the more sensible architecture, and the consumer can still use Excel as the front end.

Encoding and locale

Two settings cause most remaining problems.

Encoding. Always write UTF-8, and use the BOM when the file is destined for Excel on Windows. Document the encoding in the filename or a companion note if the consumer uses another tool.

Decimal and list separators. Excel's CSV parser uses the system locale's list separator. A file with commas as both field and decimal separators will not parse correctly in a locale that uses semicolons. If the consumer's locale differs from yours, XLSX avoids the issue entirely, which is a good argument for generating it directly.

Where proxies fit

They do not, at this stage. Proxies affect collection, not export. If you are hitting blocks during collection, the decision is in Choosing a Proxy for Web Scraping, and the diagnosis in Fixing 403 Forbidden Errors When Scraping.

Worth noting, though, that a spreadsheet is often a consumer's view of data rather than the system of record. If the data matters beyond the next meeting, land it in a database and export from there. You keep history, which lets you distinguish a real change from a scrape glitch, as argued in Residential Proxies for Price Monitoring.

A practical default

For a one-off deliverable, generate XLSX with correct column types and identifier columns formatted as text. For a recurring one, publish a stable CSV or an endpoint and let the consumer build a Power Query refresh against it.

Either way, export raw values and document the units. The most common failure in this area is not a technical one: it is a column of prices whose currency nobody recorded.

For the collection side that feeds this, see Excel-friendly exports from a Python scraper, and for scheduling the refresh, Scheduling Scrapers with CI.