DOCSEARCHbyAlgolia
Submit Docs

xlsx2csv

repository·master·Indexed 23 days ago

https://github.com/dilshod/xlsx2csv

A fast and efficient tool to convert XLSX files to CSV format, optimized for large files using an Expat SAX parser for XML processing. It provides both a command-line interface and a Python programmatic interface via the Xlsx2csv class. Features include support for specific sheet selection, custom date/time/float formatting, delimiter and quoting control, and the ability to handle hidden sheets or rows. Compatible with Python versions 2.4, 2.7, and 3.4 through 3.14.

Tokens
2.8K
Snippets
4
Records
13
Agent score
33%

What's inside xlsx2csv

  1. Install xlsx2csv

    master

    You can install xlsx2csv using pip or easy_install. It also works as a standalone script using the xlsx2csv.py file.

    Supported Python versions include 2.4, 2.7, and 3.4 through 3.14.

    pip install xlsx2csv

  2. Use the xlsx2csv CLI to convert XLSX to CSV

    master

    The xlsx2csv command-line tool converts Excel (.xlsx) files to CSV format. You can specify an input file and an optional output file. If no output file is provided, the result is sent to STDOUT. If the input is a directory, the tool will recursively convert all .xlsx files found within it.

    Basic Usage:

    xlsx2csv input.xlsx output.csv

    Convert from STDIN: Use - as the input file path to read from standard input.

    cat input.xlsx | xlsx2csv - output.csv

  3. Use xlsx2csv in Python code

    master

    You can import the Xlsx2csv class to perform conversions programmatically.

    Recommended approach: Use a context manager to ensure proper resource cleanup.

    Simple approach: Calling .convert() directly on the instance (may cause ResourceWarning in modern Python versions).

    from xlsx2csv import Xlsx2csv

# Recommended: using context manager for proper resource cleanup
with Xlsx2csv("myfile.xlsx", outputencoding="utf-8") as xlsx2csv:
    xlsx2csv.convert("myfile.csv")

# Simple usage (but may cause ResourceWarning in modern Python)
Xlsx2csv("myfile.xlsx", outputencoding="utf-8").convert("myfile.csv")

  4. Configure Xlsx2csv conversion options

    master

    When initializing Xlsx2csv(xlsxfile, **options), you can pass several options to control the conversion process.

    OptionDefaultDescription
    delimiter,CSV column delimiter symbol
    quotingcsv.QUOTE_MINIMALHow and if to quote fields
    sheetdelimiter--------Delimiter used when processing all sheets
    dateformatNoneOverride date/time format
    timeformatNoneOverride time format
    floatformatNoneOverride float format
    scifloatFalseWhether to support scientific notation for floats
    skip_empty_linesFalseSkip empty lines
    skip_trailing_columnsFalseSkip trailing columns
    hyperlinksFalseInclude hyperlinks in output
    include_sheet_pattern["^.*$"]List of regex patterns; only sheets matching these are included
    exclude_sheet_pattern[]List of regex patterns; sheets matching these are excluded
    exclude_hidden_sheetsFalseIf True, skip sheets marked as hidden
    skip_hidden_rowsTrueIf True, skip rows marked as hidden
    outputencoding"utf-8"Encoding for the output file

  5. Reference xlsx2csv CLI arguments

    master

    The following arguments are available for the xlsx2csv.py command line interface:

    ArgumentDescription
    -a, --allExport all sheets
    -c OUTPUTENCODING, --outputencoding OUTPUTENCODINGEncoding of output CSV Python 3 only (default: utf-8)
    -s SHEETID, --sheet SHEETIDSheet number to convert, 0 for all
    -n SHEETNAME, --sheetname SHEETNAMESheet name to convert
    -d DELIMITER, --delimiter DELIMITERColumn delimiter; use 'tab' or 'x09' for a tab (default: ,)
    -l LINETERMINATOR, --lineterminator LINETERMINATORLine terminator; \n, \r\n, or \r (default: os.linesep)
    -f DATEFORMAT, --dateformat DATEFORMATOverride date/time format (ex. %Y/%m/%d)
    --floatformat FLOATFORMATOverride float format (ex. %.15f)
    -i, --ignoreemptySkip empty lines
    -e, --escapeEscape \r\n\t characters
    -p SHEETDELIMITER, --sheetdelimiter SHEETDELIMITERSheet delimiter used to separate sheets (default: '--------')
    -q QUOTING, --quoting QUOTINGField quoting: 'none', 'minimal', 'nonnumeric', or 'all' (default: 'minimal')
    --hyperlinksInclude hyperlinks
    -I INCLUDE_SHEET_PATTERN, --include_sheet_patternOnly include sheets matching the pattern (affects -a)
    -E EXCLUDE_SHEET_PATTERN, --exclude_sheet_patternExclude sheets matching the pattern (affects -a)
    -m, --merge-cellsMerge cells
     xlsx2csv.py [-h] [-v] [-a] [-c OUTPUTENCODING] [-s SHEETID]
                   [-n SHEETNAME] [-d DELIMITER] [-l LINETERMINATOR]
                   [-f DATEFORMAT] [--floatformat FLOATFORMAT]
                   [-i] [-e] [-p SHEETDELIMITER]
                   [--hyperlinks]
                   [-I INCLUDE_SHEET_PATTERN [INCLUDE_SHEET_PATTERN ...]]
                   [-E EXCLUDE_SHEET_PATTERN [EXCLUDE_SHEET_PATTERN ...]] [-m]
                   xlsxfile [outfile]

  6. Use xlsx2csv via CLI

    master

    The CLI tool converts XLSX files to CSV format. You can specify a single file or a directory of files.

    Basic Syntax: xlsx2csv.py [options] xlsxfile [outfile]

    Directory Conversion: If you provide an input directory and an output directory, the tool will convert every .xlsx file in the input directory and save the resulting .csv files in the output directory.

    python xlsx2csv.py /path/to/input/dir /path/to/output/dir

  7. Use the Xlsx2csv class for programmatic conversion

    master

    The Xlsx2csv class provides a programmatic interface to convert XLSX files to CSV. It supports both context manager usage (recommended for proper resource cleanup) and simple direct usage.

    Key features:

    • Convert specific sheets by ID or name.
    • Customize formatting for dates, times, and floats.
    • Control CSV output (delimiters, quoting, line terminators).
    • Filter sheets using inclusion/exclusion patterns.
    • Handle hidden sheets and rows.
    • Include or exclude hyperlinks and merged cells.

  8. Convert specific sheets using convert()

    master

    The convert(outfile, sheetid=1, sheetname=None) method performs the actual conversion.

    • outfile: A path to the output file or a file-like object with an .open() method.
    • sheetid: The index of the sheet to convert (1-based). Use 0 to convert all sheets.
    • sheetname: The name of the sheet to convert. If provided, sheetid is ignored.

    If sheetid is 0 and outfile is a directory path, the method will create individual CSV files for each sheet named after the sheet (e.g., Sheet1.csv). If a sheetdelimiter is provided, it will be written between sheet outputs.

  9. Handle Xlsx2csv exceptions

    master

    When working with Xlsx2csv, be prepared to catch the following exceptions:

    • InvalidXlsxFileException: Raised when the input file is not a valid XLSX (zip) file.
    • SheetNotFoundException: Raised when the requested sheetid or sheetname does not exist.
    • OutFileAlreadyExistsException: Raised if the output file already exists when attempting to write to a file path.
    • XlsxValueError: Raised when encountering unknown or unhandled data formats.
    • XlsxException: Base exception for all library-specific errors.

  10. Reference: xlsx2csv CLI flags

    master

    Full list of available CLI arguments for xlsx2csv:

    # Positional Arguments
xlsxfile             xlsx file path, use '-' to read from STDIN
outfile              output CSV file path (optional)

# Options
-a, --all            export all sheets
-c, --outputencoding encoding of output CSV **Python 3 only** (default: utf-8)
-d, --delimiter      delimiter - column delimiter in CSV, 'tab' or 'x09' for a tab (default: comma ',')
--hyperlinks         include hyperlinks
-e, --escape         escape \r\n\t characters
--no-line-breaks     replace \r\n\t with space
-E, --exclude_sheet_pattern exclude sheets with names matching the given pattern
-f, --dateformat     override date/time format (ex. %%Y/%%m/%%d)
-t, --timeformat     override time format (ex. %%H/%%M/%%S)
--floatformat        override float format (ex. %%.15f)
--sci-float          force scientific notation to float
-I, --include_sheet_pattern only include sheets with names matching the given pattern
--exclude_hidden_sheets exclude hidden sheets from the output
--ignore-formats      ignore format for specific data types
-l, --lineterminator line terminator in CSV, '\n' '\r\n' or '\r' (default: \n)
-m, --merge-cells    merge cells
-n, --sheetname      sheet name to convert
-i, --ignoreempty    skip empty lines
--skipemptycolumns   skip trailing empty columns
-p, --sheetdelimiter sheet delimiter used to separate sheets (default: '--------')
-q, --quoting        quoting - field quoting in CSV, 'none' 'minimal' 'nonnumeric' or 'all'
-s, --sheet          sheet number to convert
--include-hidden-rows include hidden rows
--continue-on-error   continue processing remaining files when an error occurs during batch processing
--ignore-percentage  ignore percentage formatting and output raw values

  11. Configure sheet selection and patterns

    master

    You can control which sheets are exported using several flags. This is particularly useful when using the --all flag to export all sheets in a workbook.

    • -s, --sheet <id>: Convert a specific sheet by its index (default: 1).
    • -n, --sheetname <name>: Convert a specific sheet by its name.
    • -a, --all: Export all sheets in the workbook.
    • -I, --include_sheet_pattern <pattern>: Only include sheets matching a regex pattern (used with --all).
    • -E, --exclude_sheet_pattern <pattern>: Exclude sheets matching a regex pattern (used with --all).
    • --exclude_hidden_sheets: Exclude hidden sheets (used with --all).

  12. Configure data type formatting

    master

    Override how specific data types are represented in the output:

    • -f, --dateformat <format>: Override date/time format (e.g., %%Y/%%m/%%d).
    • -t, --timeformat <format>: Override time format (e.g., %%H/%%M/%%S).
    • --floatformat <format>: Override float format (e.g., %%.15f).
    • --sci-float: Force scientific notation for floats.
    • --ignore-percentage: Output raw decimal values instead of formatted percentages.
    • --ignore-formats <types>: Ignore specific format types (e.g., date, time, float, percentage).