Notes
Supplier CSV and Excel files: twelve ways they break an integration
A supplier file breaks on three levels: what is in a cell, where the columns are, and whether today's file arrived complete. Each of the twelve failures below has a fixed rule that catches it, as long as the integration checks the file before it writes anything to the store or the ERP.
Updated
What goes wrong, in one table
| # | Failure | Level | What happens without a check |
|---|---|---|---|
| 1 | Commas inside numbers | Cell | 1,250 splits into two columns |
| 2 | Inch marks and quotes in descriptions | Cell | 1/2" shifts every column after it |
| 3 | Windows-1252 instead of UTF-8 | Cell | Jalapeño arrives as Jalapeño |
| 4 | Leading zeros dropped by Excel | Cell | UPC 012345678905 matches nothing |
| 5 | Long codes in scientific notation | Cell | GTIN-14 becomes 1.00123E+13 |
| 6 | Header rows above the header | Columns | Company name read as a product |
| 7 | A renamed or moved column | Columns | Price read as quantity |
| 8 | Worksheets in a different order | Columns | The price list imported as stock |
| 9 | Fixed-width text | Columns | Positions off by one character |
| 10 | A half-written file | Delivery | Half the products read as missing |
| 11 | Yesterday's file again | Delivery | Stock a day old, nobody notices |
| 12 | A product missing from the file | Delivery | Zero or unknown, decided by accident |
What is in a cell?
1. Commas inside numbers
US exports write thousands with a comma: 1,250. In a comma-separated file that only works when the field is quoted. RFC 4180, the informational RFC that documents the common CSV format, says fields containing commas should be enclosed in double quotes. Many exports do not quote numbers, and the row gets one column too many.
2. Inch marks and quotes in descriptions
US product names use the double quote as an inch mark. RFC 4180 allows a double quote inside a field only when the field is quoted and the quote is doubled: "Elbow 1/2"" PVC". A supplier export that writes it bare breaks the row:
sku,upc,description,qty,price
HX-2210,012345678905,Elbow 1/2" PVC,1,250,4.10
HX-2211,012345678912,"Elbow 3/4"" PVC","1,100",4.65
The second line is correct and has 5 fields. The first has 6: the comma in 1,250 adds one, and a parser that treats the bare " as the start of a quoted field can swallow the rest of the line or the next line with it. The check is a field count per row against the header; a row that does not match goes to review with its line number.
3. Windows-1252 instead of UTF-8
ñ is one byte in Windows-1252 and two in UTF-8. A UTF-8 file read as Windows-1252 shows ñ; a Windows-1252 file read as UTF-8 shows a replacement character. The same applies to ½, ° and ™, which are common in US product names. The encoding is declared per supplier and checked: a file declared UTF-8 that contains invalid UTF-8 sequences is held.
4. Leading zeros dropped by Excel
Microsoft's own support article says Excel removes leading zeros automatically. A UPC-A code has 12 digits, and those starting with 0 come back with 11 after someone opens the file in Excel and saves it. Supplier codes such as 00123456 lose theirs too, and so do ZIP codes in New England.
5. Long codes in scientific notation
Excel stores numbers with 15 digits of precision and shows large numbers in scientific notation, such as 1.23E+15. Saved back to CSV, a 14-digit GTIN can come out as 1.00123E+13, and every digit after the sixth is gone. A part number with an E between digits, such as 12E40, is read as a number too. Microsoft lists four automatic conversions in all: leading zeros, 15-digit precision, numbers around the letter E, and strings of letters and numbers converted to dates.
The rule for 4 and 5: identifiers are text, read as text, and checked for their length and check digit. A UPC with 11 digits or a value with E+ in the code column means the file went through Excel, and the file is held. The structure of these codes is covered in SKU, UPC and GTIN.
Columns, headers and worksheets: where is what?
6. Header rows above the header
Some exports start with the company name, the export date and an empty line before the real header. The integration finds the header row by its column names, not by assuming it is line 1.
7. A renamed or moved column
A supplier adds a msrp column between qty and price, and a mapping by position now reads MSRP as price. Columns are mapped by name. A required name that is missing holds the file; a new unknown column is reported and ignored.
8. Worksheets in a different order
An XLSX file with the tabs Stock and Prices arrives one day as Prices and Stock. Read by position, prices of 4.10 go live as quantities of 4. Sheets are read by name.
9. Fixed-width text
Older distribution systems export fixed-width files: the SKU in positions 1 to 12, the quantity in 13 to 20. A layout change of one character shifts every field. The check is a type test per field: a quantity that contains letters means the layout moved.
Delivery: is this today's file, and is it complete?
10. A half-written file
A supplier's system writes stock.csv to the SFTP folder over 40 seconds. A poll that starts at second 20 reads half the rows, and the other half look like missing products. The integration only reads a file whose size has not changed for a set time, or one the supplier marks as finished with a separate .done file or a rename.
11. Yesterday's file again
The supplier's export job fails and the folder still holds yesterday's file, or an email arrives with last week's attachment. Nothing is wrong with the file, and the stock in the store is a day older every day. The check compares the export date in the file name or the header with today's date and raises an alert when no new file has arrived within, for example, 26 hours.
12. A product missing from the file
A product absent from the file of the day can mean out of stock, discontinued, or a row lost in the export. Treated as zero, a lost row takes a product offline. Treated as unknown, a discontinued product stays for sale. The rule is set per supplier, as described in the FAQ below.
How to check a file before it touches the store
The checks run on the whole file before any value is written. A worked example: a supplier sends 18,000 rows a day, and 2,700 of its UPCs start with a zero. Someone opens the file in Excel to look up a price and saves it.
| Check | Value today | Limit | Result |
|---|---|---|---|
| Row count against yesterday | 18,000 against 18,040 | more than 10% fewer | pass |
| Field count per row | all rows 5 fields | every row | pass |
| Rows that match a product | 15,300 of 18,000, 85.0% | at least 98% | hold |
| UPC length | 2,700 codes of 11 digits | 12 digits | hold |
| File age | 6 hours | 26 hours | pass |
Without the checks, 2,700 products match nothing. If the feed treats a missing product as zero, 2,700 products go out of stock in the store at the next sync, 15% of the catalog. With the checks, the file is held, yesterday's stock stays, and the report says why: 2,700 UPCs with 11 digits. The limits are set per supplier; a supplier whose row count moves 20% a day by nature gets a wider band.
Every processed file is archived under a unique name, so a question about a stock level three weeks ago is answered from the file that set it. That and the other transport options, from SFTP to email attachments, are listed on supported sources. For systems that only export files, the wider picture is in integrating a system without an API.
What this means for your integration
We read each supplier file with its own declared layout: encoding, delimiter, header row, columns by name, identifiers as text, and its own rule for a missing product. Every file passes the checks above before a single value reaches your store or ERP, and a file that fails is held with the reason in the dashboard.
You approve the first files in a dry run and see per product which stock and delivery dates would change. The supplier side of stock is on supplier feeds, and the mapping method on field mapping. Send us a recent file from your supplier, and we show you which of the twelve it has.
Frequently asked questions
Is a CSV file a standard format?
Only loosely. RFC 4180 from 2005 describes the format most programs follow: comma-separated fields, double quotes around fields that contain commas, quotes or line breaks, and a doubled quote inside a quoted field. The RFC itself notes that no formal specification exists, so every supplier file is checked against its own declared layout.
Can we ask the supplier to fix the file?
Ask for three things: UTF-8 encoding, every field quoted, and the file written by their system rather than saved from Excel. Many suppliers can change an export setting. The integration still checks every file, because a setting changes back after the supplier upgrades its system.
What happens when a supplier file fails the checks?
The file is held, yesterday's values stay in the store, and the problem is reported with the rule that failed: 2,700 rows without a match, a missing column, a file older than 26 hours. Nothing from that file reaches the store until it passes or someone approves it.
Is XLSX safer than CSV?
XLSX keeps a cell's type, so a UPC stored as text stays text. It adds its own failure: several worksheets, and a supplier who reorders them. The integration reads a sheet by its name and checks its header row, never the first tab by position.
Should a product missing from the file be set to zero?
It depends on the supplier, and the rule is set per feed. If the file always lists every product, a missing product is discontinued or out of stock. If the file lists only what is in stock, missing means zero. If the file sometimes drops rows, missing means unknown and the old value stays, with a review entry after a set number of days.
Tell us what you need
Tell us which systems you use and what needs to work better. We reply personally within one working day. We help you define the work, then agree the price and timing before you commit.
- You know the price and the timeline before you commit to anything
- We work out the connection that takes the work off your hands
- We handle the build, hosting, monitoring and maintenance
- Your team has less information to retype and fewer discrepancies to chase