Converting CSV to an Excel Workbook With Formatting

A CSV is the right format for moving data between programs and a poor one for handing data to a person who will open it in a spreadsheet. A workbook carries what a CSV cannot: which columns are text and which are numbers, how to display them, a header that stays on screen, a filter and column widths. This article writes a workbook from a CSV with Python, checks the result by reading the file back cell by cell, and covers three ways the conversion can do damage without telling you: formula guessing, row limits and long strings.

The script

The conversion writes each column with the call that fits its meaning:

import csv, datetime as dt, sys
import xlsxwriter
from openpyxl import load_workbook

SRC = "/batches/csv-0001-0200/lab/data/conv/people.csv"
OUT = "/batches/csv-0001-0200/work/conv/people.xlsx"
rows = list(csv.DictReader(open(SRC, newline="", encoding="utf-8")))

wb = xlsxwriter.Workbook(OUT, {"strings_to_formulas": False})
ws = wb.add_worksheet("people")
head = wb.add_format({"bold": True, "bg_color": "#EEEEEE", "bottom": 1})
text = wb.add_format({"num_format": "@"})
money = wb.add_format({"num_format": "#,##0.00"})
day = wb.add_format({"num_format": "yyyy-mm-dd"})
wrap = wb.add_format({"text_wrap": True, "valign": "top"})

cols = list(rows[0])
ws.write_row(0, 0, cols, head)
for i, r in enumerate(rows, start=1):
    ws.write_number(i, 0, int(r["id"]))
    ws.write_string(i, 1, r["name"])
    ws.write_string(i, 2, r["zip"], text)                                   # an identifier: text, never a number
    if r["score"]:
        ws.write_number(i, 3, float(r["score"]), money)
    ws.write_boolean(i, 4, r["active"] in ("true", "yes")) if r["active"] else None
    ws.write_datetime(i, 5, dt.datetime.strptime(r["joined"], "%Y-%m-%d"), day)
    ws.write_string(i, 6, r["note"], wrap)
ws.freeze_panes(1, 0)
ws.autofilter(0, 0, len(rows), len(cols) - 1)
ws.set_column(0, 0, 6); ws.set_column(1, 1, 18); ws.set_column(2, 2, 8); ws.set_column(3, 3, 11); ws.set_column(5, 5, 12); ws.set_column(6, 6, 28)
wb.close()

The choices, column by column:

• **id**: write_number, since it is a count-like key.

• **name**: write_string.

• **zip**: write_string with the text format @. A zip code is an identifier, and a number in a cell would lose the leading zero of 02134. The text format also tells a person editing the cell to keep it text.

• **score**: write_number with the format #,##0.00, so 12.5 is displayed 12.50 and 1234.5 as 1,234.50. An empty CSV field leaves the cell empty, which is different from writing 0.

• **active**: write_boolean, and nothing for an empty field.

• **joined**: write_datetime with yyyy-mm-dd. Excel stores dates as numbers and the format decides how they look.

• **note**: write_string with a wrapping format, so the cell with a line break shows two lines.

After the data, freeze_panes(1, 0) keeps the header on screen, autofilter adds filter buttons and set_column sets widths. The workbook option strings_to_formulas: False is discussed below.

What the file stores

Reading the workbook back with openpyxl shows what is in each cell:

python lab/conv/t0149.py

written /batches/csv-0001-0200/work/conv/people.xlsx
sheet dimensions: A1:G10 | freeze panes: A2 | autofilter: A1:G10
cell  type  value                            number format
A2    n     1                                General
C2    s     '02134'                          @
C8    s     '00000'                          @
D2    n     12.5                             #,##0.00
D4    n     7                                #,##0.00
D8    n     1234.5                           #,##0.00
E2    b     True                             General
E7    n     None                             General
F2    d     datetime.datetime(2025, 3, 1, 0, 0) yyyy-mm-dd
G5    s     'line one\nline two'             General

• C2 is the string '02134', cell type s, number format @. The leading zero is there. C8 is '00000'.

• D2 is the number 12.5, type n, format #,##0.00. The cell stores 12.5 and the format makes it look like 12.50. If someone copies the cell into another program as a value, they get 12.5. The trailing zero is presentation only.

• E2 is a boolean (b); E7 is None, the empty cell for Ed's missing value.

• F2 is a datetime with format yyyy-mm-dd: stored as a date serial number, displayed as a date.

• G5 holds 'line one\nline two', the newline intact.

• The sheet has A1:G10 of data, a freeze at A2 and an autofilter over A1:G10.

The point of reading back is that "it looks right in my spreadsheet" is not a test. This is.

The formula trap

XlsxWriter's write() is a convenience: it looks at the value and chooses a call. For a string that begins with =, it chooses a formula. The next test writes the same five strings with write() and write_string():

python lab/conv/t0149b.py

input      write():               write_string():
'=1+1'     f  '=1+1'             s  '=1+1'
'+SUM(1,2)' s  '+SUM(1,2)'        s  '+SUM(1,2)'
'@cmd'     s  '@cmd'             s  '@cmd'
'-2+3'     s  '-2+3'             s  '-2+3'
'plain'    s  'plain'            s  'plain'

Limits
write at row index 1,048,575 (the last row): 0
write at row index 1,048,576 (one too many): -1
warning: []
write_string of 40,000 characters returns -2 (-2 means truncated to 32,767)
characters stored: 32767

The first block is the result. '=1+1' written with write() is stored as cell type **f**, a formula, and a spreadsheet will compute it. The same text written with write_string() is type s, a string. The other four (+SUM(1,2), @cmd, -2+3, plain) were strings either way, in this library. If any column of your CSV holds text that another person typed, write() turns their =... into a live formula, which is the formula injection the export guide warned about and now in a file you made. Two fixes: use write_string for text columns, as the script does, and set strings_to_formulas to False for the workbook, which makes write() stop guessing. I did not test what Excel does when a person later types into one of those cells.

The limits that fail quietly

Excel has hard limits, and the library enforces them in a way that is easy to miss. The second block of the same output:

• Writing to row index 1,048,575 (the last row of an Excel sheet) returned 0, success.

• Writing to row index 1,048,576 returned -1. There was no exception and no Python warning. A loop that ignores the return value drops every row past the limit without a trace. A CSV with more than 1,048,575 data rows cannot go into one sheet. Split it across sheets, or do not use a workbook.

• A string of 40,000 characters was written with write_string, which returned -2 and stored 32,767 characters. Excel's cell limit is 32,767 characters, so a longer field is truncated, again without an exception. Check the return codes, or measure the longest field before you convert; a long note in a CSV is not rare.

Size and speed at 193,709 lines

The big file has 12 columns and 193,709 lines. Written as text cells in the default mode:

/usr/bin/time -f "%e s, peak %M KB" python lab/conv/x_big.py work/val/customers.csv work/conv/c_normal.xlsx normal

193709 rows written
11.08 s, peak 409276 KB

11.08 s and a 409,276 KB peak. The library builds the workbook in memory. The constant_memory option writes rows as it goes, on the condition that you write them in order:

/usr/bin/time -f "%e s, peak %M KB" python lab/conv/x_big.py work/val/customers.csv work/conv/c_constant.xlsx constant

193709 rows written
9.07 s, peak 25612 KB

9.07 s and 25,612 KB. For a file near the row limit, that is the only reasonable mode. The sizes:

ls -l work/val/customers.csv work/conv/c_normal.xlsx work/conv/c_constant.xlsx | awk '{printf "%-34s %d bytes\n", $9, $5}'

work/conv/c_constant.xlsx          12931722 bytes
work/conv/c_normal.xlsx            13191370 bytes
work/val/customers.csv             17297141 bytes

The workbooks are 13,191,370 and 12,931,722 bytes against 17,297,141 for the CSV: an .xlsx is a zip archive of XML, so it is smaller than the text it holds. (This script wrote every cell as text for the benchmark. A real conversion would use numbers and dates for the columns that are numbers and dates, as above.)

Other routes

pandas.DataFrame.to_excel(engine="xlsxwriter") does this in one call, and it picks the cell types from the dataframe's dtypes, so the zip-as-float trap from the JSON article comes with it: read the CSV with dtype=str for identifier columns first. For formatting that pandas does not expose, the script above is the route; for very simple output, write the CSV and let the person open it, and read how to open a UTF-8 CSV in Excel for the encoding trap that route has.

How to tell it worked

Read the file back with a second library and check the cell types: the identifier columns are strings, the numbers are numbers, the dates are dates. Count the rows against the CSV, and check every write return code. Look for formulas: scan for cells of type f that you did not intend. Compare the longest field with 32,767 characters. Then open the workbook in the program the reader will use and look at it, which is the step I could not do here. The formats and the filter and the freeze are the parts most likely to differ.

What I could not check without Excel

How Excel displays a text-formatted cell next to a number, whether it flags numbers stored as text, how it treats the date serial number in a workbook made on a different platform, and what happens in older versions with the file are all outside what I tested. The .xlsx format is documented (ECMA-376), and XlsxWriter's documentation describes its calls. If the consumer is a program, skip the workbook: a CSV with a contract is simpler and loses less. If it is a person, the workbook is the better gift, and worth five minutes of looking at before you send it.