dhk.csv2xlsx 2.2.0

Read a CSV file and write an XLSX file with optional formatting.

dhk.csv2xlsx

Introduction

dhk.csv2xlsx is a Python command-line tool for reading a CSV file and writing an XLSX file with optional formatting. It leverages the Python standard library csv module and the XlsxWriter package.

Simple Installation

A pedestrian command for installing the package is given below. Alternatively, for a more rewarding installation exercise, see section Recommended Installation.

pip install dhk.csv2xlsx

Usage

$ csv2xlsx --help
usage: csv2xlsx [-h] [--force] [--output OUTPUT_FILE] [--settings-file SETTINGS_FILE]
                [--verbose] [--version] [--generate-settings-file | --transform-csv]
                [CSV_FILE]

Read a CSV file and write an XLSX file.

The program exposes much of the formatting functionality of the XlsxWriter
packages Workbook class through the optional SETTINGS_FILE.  For details about
the Workbook class, see https://xlsxwriter.readthedocs.io/workbook.html.  For
details about the Worksheet class, see
https://xlsxwriter.readthedocs.io/worksheet.html.  For details about the Format
class, see https://xlsxwriter.readthedocs.io/format.html.

positional arguments:
  CSV_FILE              CSV file

options:
  -h, --help            show this help message and exit
  --force, -f           force; suppress prompts
  --output, -o OUTPUT_FILE
                        output file; default: CSV_FILE - .csv + .xlsx
  --settings-file, -s SETTINGS_FILE
                        settings file
  --verbose, -v         verbose
  --version, -V         show version
  --generate-settings-file, -g
                        generate settings file; file defaults to sample.settings.json
  --transform-csv, -t   transform CSV to XLSX; default True

examples:

    # See application information.
    csv2xlsx \
        -v

    # Generate a sample workbook settings file.
    csv2xlsx \
        --generate-settings-file

    # Transform the given CSV file, writing a corresponding XLSX file.
    csv2xlsx \
        CSV_FILE

    # Using the given workbook settings file, transform the given CSV file,
    # writing a corresponding XLSX file.
    csv2xlsx \
        --settings-file SETTINGS_FILE \
        CSV_FILE

    # Using the given workbook settings file, transform the given CSV file,
    # writing the XLSX file to the given output file.
    csv2xlsx \
        --settings-file SETTINGS_FILE \
        --output OUTPUT \
        CSV_FILE

execution details:

The Workbook class instance attribute `options` is set to `{'constant_memory':
True}` merged with the SETTINGS_FILE `workbook_options` dictionary.

The Workbook class instance attribute `formats[0]` stores a default Format
object.  The SETTINGS_FILE `workbook_format` dictionary can be used to set the
attributes of this default Format object.  The available dictionary keywords
correspond to the list of properties in the table at
https://xlsxwriter.readthedocs.io/format.html#format-methods-and-format-properties.

The Workbook class instance method `add_format()` is used to add Format class
instances to the Workbook class instance.  The program iterates over each of
the SETTINGS_FILE `cell_format_settings` dictionary key-value pairs.  The
SETTINGS_FILE `workbook_format` dictionary is merged with the value of the
`cell_format_settings` key-value pair, and this merged value is the argument to
`add_format()`.  The key-value pair formed by the key from the
`cell_format_settings` key-value pair and the `add_format()` return value is
stored in a `cell_formats` dictionary for later use when setting Worksheet
class instance columns and rows.

The Worksheet class instance method `set_column()` is used to set the width,
cell format, and options for specified columns.  The program iterates over each
of the SETTINGS_FILE `column_settings` list elements.  For each element, the
`first_col` and `last_col` arguments to `set_column()` can be specified in
absolute terms via a `columns` key-value pair with the string value specified
by ordinal number (`'1'`, `'1:2'`, etc.) or by `A1` style notation (`'A'`,
`'A:B'`).  Alternatively, if the `columns` key-value pair is not present, then
`first_col` is simply the next column, and `last_col` is either the same as
`first_col` or is set based on a `span` key-value pair.  The `width` argument
to `set_column()` is set to the value of the `width` key-value pair, defaulting
to 10 if not specified.  The `cell_format` argument to `set_column()` is set
based on the value of the `cell_format` key-value pair.  This value must
correspond to the key of one of the SETTINGS_FILE `cell_format_settings`
key-value pairs.  If a `cell_format` key-value pair is not provided, then the
argument defaults to the Workbook class instance attribute `formats[0]`.  The
`options` argument to `set_column()` is set to the value of the `options`
key-pair.  The value of the `data_type` key-pair is stored for each column for
later use when parsing CSV data.

The Worksheet class instance method `set_row()` is used to set the cell format
for specified rows.  The program iterates over each of the SETTINGS_FILE
`row_settings` list elements.  For each element, `first_row` and `last_row` can
be specified in absolute terms via a `rows` key-value pair with the string
value specified by ordinal number (`'1'`, `'1:2'`, etc.).  Alternatively, if
the `rows` key-value pair is not present, then `first_row` is simply the next
column, and `last_row` is either the same as `first_row` or is set based on a
`span` key-value pair.  For each `row` in the sequence determined by
`first_row` and `last_row`, `set_row()` is called with `row` as the first
positional argument and the argument `cell_format` set based on the value of
the `cell_format` key-value pair.  This value must correspond to the key of one
of the SETTINGS_FILE `cell_format_settings` key-value pairs.  If a
`cell_format` key-value pair is not provided, then the argument defaults to
the Workbook class instance attribute `formats[0]`.  The value of the
`data_type` key-pair is stored for each row for later use when parsing CSV
data.

Each row of the CSV file is read.  The value of each column of the row is
handled.  If there was a `data_type` specified in the SETTINGS_FILE for the
row, then that is used.  Otherwise, an attempt is made to get a `data_type`
that was specified in the SETTINGS_FILE for the column.  Valid values for
`data_type` are: `decimal`, `bool`, `date`, `datetime`, `formula`, and
`string`.  If `data_type` was not specified or an exception occurs when
attempting to parse the CSV value in terms of the given `data_type`, then the
CSV value is written as a `string`.

The Worksheet class instance method `autofilter` is called with arguments based
on the `autofilter` object in the SETTINGS_FILE `workbook_settings` dictionary.
Both row-column notation (`(first_row, first_col, last_row, last_col)`) and
`A1` style notation (`'A1:D11'`) are supported.  If row-column notation is
used, then a negative number is replaced with the maximum row or column
available in the data.

The Worksheet class instance method `freeze_panes` is called with arguments
based on the `freeze_panes` object in the SETTINGS_FILE `workbook_settings`
dictionary.  Both row-column notation (`(row, col[, top_row, left_col])`) and
`A1` style notation (`'A2'`) are supported.

files:

This application uses Python package `platformdirs` to determine under which
directory configuration files are stored.  Method `PlatformDirs` is called with
arguments `appname='csv2xlsx'` and `appauthor='dhk'`.  You can see the location
of configuration files by executing the command `csv2xlsx -v`.

default.settings.json
    If no settings file argument is provided for a transformation and this
    configuration file exists and is readable, then the application will
    attempt to read default workbook settings from it.

Recommended Installation

uv is a tool for managing various aspects of the Python environment, including Python versions and Python virtual environments.

Install Package Using uv

To install the package hosted at PyPI using uv, execute the command below. For command options, see https://docs.astral.sh/uv/reference/cli/#uv-tool-install. Note that there is a --python PYTHON option to select a particular Python version. Note that there is a --overrides OVERRIDES option to override dependency package versions specified in the requirements.txt file in the package and instead use the given OVERRIDES file.

uv \
    tool \
    install \
    dhk.csv2xlsx

If an "invalid peer certificate: UnknownIssuer" error is thrown, one can force uv to use system certificates by setting environment variable UV_SYSTEM_CERTS to true. E.g.,

UV_SYSTEM_CERTS=true \
uv \
    tool \
    install \
    dhk.csv2xlsx

Old Recommended Installation

pyenv is a tool for installing multiple Python environments and controlling which one is in effect in the current shell.

pipx is a tool for installing and running Python applications in isolated environments.

Assuming these have been installed correctly...

Install Python Under pyenv

The version of Python under which this package was last developed and tested is stored in .python-version.

To capture this Python version to a shell variable, execute the commands below. PYTHON_VERSION should be set to something like 3.13.3.

PYTHON_VERSION="$(
    wget \
        -O - \
        https://raw.githubusercontent.com/DavidKiesel/python-csv2xlsx/refs/heads/main/.python-version
)"

echo "$PYTHON_VERSION"

To determine if the .python-version version of Python has already been installed under pyenv, execute the command below. If it has not been installed, then a warning message will be displayed.

PYENV_VERSION="$PYTHON_VERSION" \
python --version

If it has already been installed, then proceed to section Install Package Using pipx.

Otherwise, to install the given version of Python under pyenv, execute the command below.

pyenv install "$PYTHON_VERSION"

If the install was successful, then proceed to section Install Package Using pipx.

If instead there is a warning that the definition was not found, then you will need to upgrade pyenv.

If pyenv was installed through a package manager, then consider upgrading it through that package manager. For example, if pyenv was installed through brew, then execute the commands below.

brew update

brew upgrade pyenv

Alternatively, you could attempt to upgrade pyenv through the command below.

pyenv update

Once pyenv has been upgraded, to install the given version of Python under pyenv, execute the command below.

pyenv install "$PYTHON_VERSION"

Install Package Using pipx

Only proceed from here if the instructions in section Install Python Under pyenv have been completed successfully.

At this point, shell variable PYTHON_VERSION should already contain the appropriate Python version. If not, execute the commands below.

PYTHON_VERSION="$(
    wget \
        -O - \
        https://raw.githubusercontent.com/DavidKiesel/python-csv2xlsx/refs/heads/main/.python-version
)"

echo "$PYTHON_VERSION"

To install the package hosted at PyPI using pipx, execute the command below.

pipx \
    install \
    --python "$(PYENV_VERSION="$PYTHON_VERSION" pyenv which python3)" \
    dhk.csv2xlsx