Automating supplier spreadsheets from email to your ERP

Most purchasing and operations teams know the routine. A supplier emails an updated price list as an XLS attachment. Someone downloads it, opens it, deletes the logo rows at the top, renames three columns, fixes the dates, saves it as CSV in the format the ERP accepts and uploads it. Next week another supplier sends a file with a different layout, and the routine starts again with different steps.

This work is repetitive, error prone and invisible until it goes wrong, for example when a price lands in the wrong column and orders go out at the wrong cost. The good news is that it follows rules, and anything that follows rules can run as a pipeline. This article describes how to design one, from the inbox to the import.

What makes supplier files hard

The difficulty is not the conversion itself. It is that every supplier has its own idea of a spreadsheet.

• Header rows start on line 1 for one supplier and on line 6 for another, below a logo and contact details.
• The same field has many names, such as Item No, Art.-Nr., SKU and Product Code.
• Prices come with currency symbols, thousands separators or a decimal comma.
• Dates appear as US text, European text or Excel serial numbers.
• Some files are XLS from old systems, some are XLSX, a few are CSV with semicolons.
• Layouts change without warning when a supplier upgrades its own software.

Your ERP, on the other hand, wants one thing: a CSV with fixed columns in a fixed order, a specific delimiter, a specific encoding and a specific date pattern. The pipeline is the translator between many inputs and one output.

Step 1, a dedicated inbox per supplier

Start by separating the traffic. Instead of a shared mailbox where people forward attachments, each pipeline in XlsConverter has its own receiving address, such as [email protected]. You ask the supplier to send to that address, or you set a forwarding rule in your mail system for messages from that supplier.

One address per supplier means the pipeline always knows which mapping applies. It also gives you a clean audit trail of every file received, which helps when someone asks which price list was active on a given date. Attachments in XLS, XLSX, XLSM, ODS and CSV are accepted. If a supplier shares files through a cloud folder instead, the same pipeline can watch a Google Drive or Dropbox folder, or an S3 bucket.

Step 2, a mapping template for each layout

The mapping template is the heart of the pipeline. You build it once, using a real file from the supplier, and it describes how to turn their layout into yours.

1. Locate the header row, either by row number or by finding a row that contains a known column name. The second approach survives a supplier adding a line to the top.
2. Map source columns to target columns. Item No becomes sku, Net Price EUR becomes unit_price.
3. Drop columns the ERP does not need, and add constant columns it does, such as supplier_id.
4. Normalize values. Trim spaces, strip currency symbols, convert decimal commas, pad SKUs to a fixed length, write dates in the pattern the ERP expects.
5. Set the output order, delimiter, encoding and quoting rules.

When a supplier sends a new layout, AI column normalization in XlsConverter matches the new headers against your target schema and proposes a mapping, which you confirm before it is saved. Nothing changes in the template without that confirmation.

Step 3, validation before delivery

A pipeline that delivers wrong data quickly is worse than a person who delivers it slowly. Validation rules stop a bad file before it reaches the ERP. Useful checks include the following.

Check	Catches
Required columns present	Supplier removed or renamed a column
No empty SKUs or prices	Partial rows, subtotal lines
Price is a positive number	Text in numeric columns, shifted columns
Row count within the usual range	Truncated or wrong sheet
No duplicate SKUs	Repeated blocks in the file

When a check fails, the file is held and the pipeline owner gets a notification with the reason, instead of the ERP receiving half a price list. Deduplication can also run as a transform, keeping the first or last row per SKU, when duplicates are expected.

Step 4, delivery to the ERP

ERPs import files in a few common ways, and the destination should match.

• An SFTP folder the ERP polls on its own schedule, which is common for older systems.
• An S3 bucket, Google Drive or Dropbox folder read by an integration job.
• A webhook to your own middleware, signed with HMAC so the receiver can verify the file came from your pipeline.
• An email to the person who runs the import manually, for systems without any interface.

The output for most ERPs is CSV. The XLS to CSV and XLSX to CSV conversions behave the same way inside a pipeline as in the browser, so you can test a template on a single file before switching the pipeline on. If your ERP expects XML, the Excel to XML converter writes it with your element names.

Step 5, a schedule and a rhythm

Inbox pipelines run when a file arrives. Folder and URL sources can run on a schedule, for example every morning at 6:00, so the ERP has current prices before the purchasing team starts work. Some suppliers publish a price file at a fixed HTTPS URL, and a scheduled pull removes email from the process entirely.

Keeping the pipeline healthy

Three habits keep a supplier pipeline reliable for years.

Keep a sample file per template

Store the file each template was built on. When output looks wrong, compare the new file against it and the difference is usually obvious, a new column or a moved header.

Review the job log weekly

A short look at which files arrived, which were held and why takes a few minutes and shows early when a supplier is drifting.

Treat templates as configuration

Name templates after the supplier and the ERP import they feed. When the ERP import format changes, update one target schema and every mapped template follows.

Rolling out supplier by supplier

Do not try to automate every supplier in one week. Start with the supplier whose file takes the most time or causes the most errors. Build the template on its last three files, not one, so you see how much the layout varies. Run the pipeline in parallel with the manual process for two cycles and compare the outputs line by line. When they match, switch the ERP import to the pipeline output and move to the next supplier. After a handful of suppliers, the common patterns repeat, and new templates take minutes because most cleanup steps can be copied from an existing one.

Keep the manual route documented for the rare file that no template can handle, such as a scanned PDF price list. Those exceptions are easier to manage when everything else runs on its own.

Security of supplier data

Price lists are commercial information. Files are encrypted in transit and at rest, and they are deleted automatically after the job finishes, by default one hour later. More on retention and access is on the security page.

Pipelines, schedules, cloud sources and AI normalization are part of the Business plan, which is built for operations and finance teams. Once a supplier file is mapped, the weekly copy and paste becomes a log entry.