format-csv-to-excel

This Python3 utility reads a named .csv file and outputs a correspondingly named .xlsx file with formatting.

Use

The script, main.py, uses a Python VENV (Virtual ENVironment) and requires one command-line argument, the path to the .csv file that is to be processed. An example command-line on my Mac might be:

/Users/mcfatem/GitHub/format-csv-to-excel/venv/bin/python /Users/mcfatem/GitHub/format-csv-to-excel/main.py /Users/mcfatem/Downloads/ihcQ_grinnell_FULL-Feb-1-2022_ih.csv

Advanced Use

The script now accepts the following optional arguments:

Argument Purpose
–verbose Provides additional diagnostic output
–dg Runs special DG specific processing

Output

The script will attempt to produce a new .xlsx file with the same path as the input .csv file. For example, the output from the above example command-line was:

/Users/mcfatem/Downloads/ihcQ_grinnell_FULL-Feb-1-2022_ih.xlsx has been created.

The named output file was indeed created as indicated.

CSV with Formatting

Formats in the .csv are expressed as %CODE% prefixes in each cell. A simple example excerpt from a prepared .csv file might be:

%ORANGE%31911 %BOLD%grinnell:31911 Sin for a Sign? [1] sp_pdf [2] grinnell:student-scholarship application/pdf [0] None
31910 %BOLD%grinnell:31910 Divinity and Power [1] sp_pdf [2] grinnell:student-scholarship application/pdf [0] None”

Valid CODEs

The following %CODE% values are valid and transform into ExcelWriter.workbook.add_format definitions as shown below.

Code Cell Format
%BOLD% { ‘bold’: True }
%ORANGE% { ‘bold’: True, ‘bg_color’: ‘#FFB347’ }
%YELLOW% { ‘bold’: True, ‘bg_color’: ‘#FFFFBF’ }
%RED% { ‘bold’: True, ‘font_color’: ‘red’, ‘bg_color’: ‘#FFFFBF’ }
%SOFT% { ‘bold’: False, ‘font_color’: ‘gray’ }
%GREEN% { ‘bold’: False, ‘font_color’: ‘green’ }
%NORMAL% No cell formatting is applied.

Sources

GitHub

View Github