Ciaren

Blog

How to Check an Excel to Parquet Conversion

Check that an Excel to Parquet conversion preserves leading zeros, keeps the right rows, and writes the types your downstream joins and reports expect.

About this post

Published
Reading time
2 min
Author
Ciaren team
excelparquetdata-quality

A warehouse sends you an Excel file every week. The Shipments sheet shows a store code of 000317. After conversion, the Parquet file opens and the unit total still matches, but the code reads 317. The inventory system joins on six-character store codes, so that shipment will miss its match.

Check how Excel stored the identifier

Use a small, known slice of the Shipments sheet as a reference. Each line_id identifies one shipment line. Several lines can belong to the same store.

line_idstore_codeunits
L-0010003172
L-0020000421
L-0030003174

Excel can display 000317 while storing the number 317 under the custom format 000000. Parquet stores the column values your reader hands it. If the reader produced 317, casting to text produces "317". Check the imported value before writing. Excel's guide to keeping leading zeros

In Ciaren, open Import options when uploading the workbook on Datasets and enter Shipments in the Excel sheet field, then select that dataset in File Input. Preview the imported values and check store_code against your reference. If the source defines every code as exactly six digits and the imported values are intact integers, use String Transform on store_code with operation pad, width 6, side left, and fill character 0. If the source uses variable-width codes, get the exact strings from the source system. For readers that infer types, verify the text survives the import too. Excel to Parquet recipe · String Transform

Check the grain as well as the count

For this three-line sample, put Assert Row Count before File Output and set both bounds to 3. On a weekly file, use the sender's record count for the selected sheet, excluding the header and any footer. Add Assert Not Null for line_id and store_code and Assert Unique for line_id. The repeated 000317 is valid. A duplicated L-002 can replace L-003 and leave the count at three, so the key check catches a failure the count would miss. Use error mode to stop the flow when a check fails. Assert Row Count · Assert Not Null · Assert Unique

Read the written file back

Set File Output to Parquet, name it shipments, and download shipments.parquet from the run. For the sample above, read the file back with pandas and PyArrow and compare the complete rows to the values from the sheet. File Output

python
import pandas as pd

expected = [
    ("L-001", "000317", 2),
    ("L-002", "000042", 1),
    ("L-003", "000317", 4),
]
rows = pd.read_parquet("shipments.parquet")
actual = rows[["line_id", "store_code", "units"]].sort_values("line_id")

assert list(actual.itertuples(index=False, name=None)) == expected
assert pd.api.types.is_integer_dtype(rows["units"])

This comparison also catches swapped quantities, which can preserve the count, the keys, and the total. On a recurring feed, compare the output against an independent source extract where one is available. For a spot check, include a leading-zero code and a store with multiple shipment lines.