Converting an ERP export for a store import: decimal commas, day-first dates, codes and rejects

Why the same export gives different files

ERP systems export in their own conventions. A price may be written 1.234,56 with a decimal comma and a thousands dot, a negative stock figure as 15- with the sign at the end, a date as 03/04/2026 meaning 3 April, a product code as 00123 with leading zeros, a status as a single letter. A person converting by hand corrects these by feel and differently on different days. A one-off spreadsheet macro corrects the cases its author saw. The result is an import file that changes with who prepared it and with the particular values that day.

Write down a rule for every column

Treat the conversion as a table with one row per column: its type, its parse rule, what counts as invalid and what the output looks like. Text codes stay text, so 00123 is never turned into a number. Numbers are parsed with an explicit rule for the decimal and thousands marks. Dates are parsed with an explicit format, never guessed, because 03/04/2026 is valid in both orders. Status codes go through a mapping table, and an unknown code is a reject, not a default. The W3C tabular vocabulary takes the same approach, letting a column declare its date format and its null marker instead of leaving them to the reader.

• Include dates where day and month are both 12 or lower in the test data; they expose the wrong order.
• Decide in writing whether blank means unchanged, zero or invalid.

Exact decimal arithmetic, and explicit separators

Money should not pass through binary floating point. Python's decimal documentation says decimal numbers can be represented exactly, and shows that Decimal(3.14) built from a float carries the float's binary expansion, 3.140000000000000124 and more digits, while Decimal('3.14') built from a string is exactly 3.14. That is why we build them from strings and not floats. The documentation also describes quantize for rounding to a fixed number of places and gives half-even, which sends ties to the nearest even digit, as the default rounding. That may not be the rule your accountant expects, so state the rounding mode explicitly and test a tie such as 2.665: half-even gives 2.66 and half-up gives 2.67, as computed with Python's decimal module.

Decimal accepts only a dot as the decimal point in its documented grammar, so a decimal-comma price needs an explicit conversion step before it is parsed. Do not rely on the server's locale for that. Python's locale documentation says the setting is process-wide, not thread-safe on most systems, and available locale names vary by platform, so a conversion that works on one machine can fail or change behaviour on another.

Rejects and reconciliation

A row that fails a rule must go to a reject file with its record number, original text and the reason, not be fixed by guesswork or dropped. Then the counts must reconcile: rows read equal rows converted plus rows rejected. The conversion must be deterministic, so the same export always gives byte-identical output, which makes it testable against an approved expected file. Keep personal data out of the export entirely; this is a product, stock and price conversion.

A safe first investigation

In a copy of one export, sort each numeric column and look at the smallest, largest and any blanks. Look at each date column for day-first versus month-first. List every change a person currently makes by hand; each becomes a rule. Do not send real price lists to start. The two header rows, three invented rows and the list of hand edits are enough to scope the work.

What fits and what does not

Fits: one export layout converted to one import layout by a tested script that you can run. Not a fit: running it on a schedule on your server, changing the ERP's export, accounting or tax calculations, category mapping or exports with personal data. A schedule is a separate job, and the existing guides on pack and unit prices cover units rather than formats. If you only need the dates and decimal separators of one file fixed once, the fixed job for mixed dates and numbers in one spreadsheet or CSV is the closer and cheaper fit; this job is for an export you convert again and again, with status codes, text codes and a reject file.

How the paid job is accepted

The job etl-erp-export-to-store-import-transform starts from £595 for one ERP layout and one target layout up to 40 columns, of which up to ten need a conversion rule (one rule is the conversion for one column), quoted after we see the two header rows and the hand edits. The agreed synthetic export must convert to the approved expected file byte for byte; failing rows must appear in the reject file with reasons and the counts reconcile; decimal-comma, thousands and trailing-minus values must convert to the agreed values with the agreed rounding; and two runs must give identical files. Prices are untested proposals, and payment follows the agreed checks and your sign-off. Nothing is booked or charged by an enquiry.