Scrapy导出CSV时字典中ID值前导零丢失问题求助

Hey, I’ve dealt with this exact frustration before—CSV viewers like Excel love to auto-convert numeric-looking strings to integers, which strips off those critical leading zeros. Let’s break down how to keep your 0606130001 intact:

Option 1: Wrap the ID in Quotes Directly in Your Scraper #

The simplest quick fix is to wrap your ID value in double quotes when yielding the item. This tells CSV parsers to treat it as text, not a number:

def parse_on_save(self, response):
    rows = response.xpath('//table[2]/tr')
    for row in rows:
        id_val = row.xpath('td[1]/font/text()').extract_first()
        # Wrap the ID in double quotes to force text format
        yield {
            "id": f'"{id_val}"'
        }

When you open the CSV later, tools like Excel will respect the quotes and keep the leading zero. If you’re using a plain text editor, you can easily remove the quotes if needed, but most tools will handle them automatically.

Option 2: Customize Scrapy’s CSV Exporter (Cleaner for Large Projects) #

If you don’t want to hardcode quotes into your data, you can create a custom CSV exporter in Scrapy that automatically quotes non-numeric fields. This is better for maintainability:

1. Add this to your project’s settings.py:

from scrapy.exporters import CsvItemExporter
import csv

class TextFriendlyCsvExporter(CsvItemExporter):
    def __init__(self, file, include_headers_line=True, join_multivalued=',', **kwargs):
        super().__init__(file, include_headers_line, join_multivalued, **kwargs)
        # Quote all non-numeric fields to preserve text formatting
        self.quoting = csv.QUOTE_NONNUMERIC

FEED_EXPORTERS = {
    'csv': 'your_project_name.settings.TextFriendlyCsvExporter'
}

2. Run your spider as usual—now all string fields (including your ID) will be quoted, and leading zeros won’t get stripped.

Option 3: Fix It During CSV Import (If You Already Have the File) #

If you’ve already exported the CSV without fixing it first, you can still recover the leading zeros when opening it:

• In Excel: Go to Data > From Text/CSV, select your file, and in the import wizard, set the ID column’s data type to Text before finishing the import.
• In Google Sheets: When uploading the CSV, uncheck the box for Convert text to numbers, dates, and formulas (or manually format the ID column as text afterward).

Why This Happens #

CSV files themselves are just plain text—there’s no built-in data type info. The issue comes from tools like Excel automatically guessing data types: when it sees a string that looks like an integer, it converts it, which drops leading zeros. The fix is to explicitly mark the ID as text, either when writing the CSV or when reading it.

内容的提问来源于stack exchange，提问作者smviswa