You've done the hard part. You've extracted data from your invoices, remittance advices, or purchase orders. The fields are populated, the numbers look right, and you have a spreadsheet ready to go. Then you try to import it into your ERP and it fails. Wrong date format. Missing required field. Column header doesn't match. The whole file gets rejected.
This is the last mile problem, and it's where a lot of the time savings from automated invoice processing quietly disappears. Teams spend 30 to 60 minutes reformatting a CSV before every import, even after successfully extracting data from their remittance advices because the extraction output doesn't match what the ERP wants.
This post covers what each major ERP requires, where teams typically get tripped up, and how to configure your extraction so the output goes straight into the ERP without a manual formatting pass in between.
ERP import engines are not forgiving. They are pattern matchers. If your column header says "Invoice Date" and the template expects "InvoiceDate" with no space, the import fails. If your amounts include a dollar sign and the system expects plain numbers, it fails. These are not edge cases. They're the norm.
The specific things that cause most import failures:
Date formats. QuickBooks Desktop expects MM/DD/YYYY. NetSuite's CSV importer wants dates as MM/DD/YYYY as well, but SAP Business One's Data Transfer Workbench wants YYYY-MM-DD. If your extraction outputs in one format and your ERP wants another, every single date field will error.
Currency formatting. Sage Intacct requires that amounts contain no commas and no dollar signs. So $1,250.00 needs to come in as 1250.00. A field that reads $1,250.00 will cause the row to fail or import as zero.
Vendor IDs with leading zeros. If your vendor ID is 00842 and you paste it into Excel, Excel strips it to 842. Your ERP then can't match it to a vendor record and the transaction either errors or imports unmatched. This happens constantly with vendor numbers, account codes, and item codes.
Required fields that aren't on the source document. NetSuite's CSV import for payment application requires the Internal ID of the invoice being applied, not the invoice number. That ID lives in NetSuite, not on the remittance advice. If you don't have a lookup process to find it, the payment import fails.
Column order and naming. Some ERP importers care about column order. Most care about exact column header names. A template that expects "VendorName" in column A and "InvoiceNumber" in column B will behave unpredictably if those are swapped or renamed.
Here's the practical breakdown for the ERPs that come up most often in finance and ops teams doing document-heavy work.
QuickBooks Desktop uses its own import toolkit with a plugin. Each transaction type (bills, invoices, payments) has its own set of required column mappings. The most reliable approach is to go to File > Import > Transactions to pull the blank template for the transaction type you're importing. The column names in that template are the ones your CSV must match. QuickBooks is also strict about the order some fields appear in for multi-line transactions where the header row and line item rows need to follow a specific pattern.
NetSuite is one of the more complex imports to get right. For basic AR invoices, it requires External ID and Customer as required fields. For payment application, which is where most remittance advice processing ends up, you can't use invoice numbers to identify which invoices a payment applies to. You need the Internal ID, which is NetSuite's own auto-generated record ID. This means your extraction process needs a way to look up Internal IDs before the import file is ready. The AR account on the payment must also match the AR account on the original invoice, or the application fails silently in some configurations. And if you're using undeposited funds, that account needs to be mapped correctly even if it's not obvious from the transaction.
SAP Business One uses the Data Transfer Workbench (DTW), and the structure here is different from most ERPs. Instead of a single flat CSV, DTW expects two files: a header file and a line items file. The header file contains one row per transaction (invoice number, vendor, date, total). The line items file contains one row per line item, linked back to the header by a key field. If your extraction produces a flat file with one row per line item and repeating header data, it needs to be restructured before DTW will accept it. The DTW tool itself provides the predefined templates. Download those before you build your extraction template.
Sage Intacct is fairly straightforward about its CSV format but has hard limits. Column headers must match the import template exactly, and it's case sensitive. Amounts cannot contain commas or dollar signs, as mentioned above. The system also has a limit of 1,000 invoices per payment batch and 5,000 line items per file. If you're processing a high-volume payment run from a remittance advice with hundreds of invoice applications, you need to know these limits before you build your workflow.
| ERP | Date format | Amount format | Import method |
|---|---|---|---|
| QuickBooks Desktop | MM/DD/YYYY | Plain numbers (no $ or commas) | Import toolkit plugin |
| NetSuite | MM/DD/YYYY | Plain numbers | CSV Import (requires Internal ID) |
| SAP Business One | YYYY-MM-DD | Plain numbers | Data Transfer Workbench (2 files) |
| Sage Intacct | MM/DD/YYYY | No commas, no $, max 2 decimals | CSV (max 1,000 invoices/batch) |
| Global Shop Solutions | Varies by instance | Varies by instance | Flat file, XML, REST API |
Global Shop Solutions, which is common in manufacturing environments, is more flexible than most ERPs on this list. It supports flat file imports, XML, REST API, and in some configurations direct database writes. The catch is that the integration approach is customized per customer and per implementation. What works for one Global Shop installation may not work for another. If you're on Global Shop, confirm the import format with whoever manages your instance. There's no single universal template.
The problem most teams run into is that they build their extraction template around the source document (what's on the invoice) rather than around the ERP import requirement. So the extraction might pull "Invoice Date" in whatever format it appears on the PDF, and then someone has to reformat it before the import.
The fix is to build the extraction template to match the ERP's import format from the start. That means:
Use your ERP's exact column header names. Download the blank import template from your ERP and use those field names as your extraction column headers. Not similar names. Identical names. If NetSuite's template says "External ID", your extraction output column should say "External ID" with the same capitalization and spacing.
Specify the exact output format in your extraction instructions. When you're setting up your document parsing template, use the extra instructions field to enforce formatting. "Output dates as MM/DD/YYYY." "Output amounts without dollar signs or commas." "Preserve leading zeros on vendor numbers." These instructions constrain the output format so it matches what the ERP expects, every time.
Use context documents for IDs that aren't on the source document. For situations like NetSuite's Internal ID requirement, where the ERP needs a reference that doesn't appear on the invoice or remittance, you can upload a vendor master or invoice register as a context document. The extraction can then look up the correct internal ID based on the invoice number or vendor name that does appear on the source document. This eliminates the manual lookup step that would otherwise happen in a spreadsheet before import.
Create separate templates for each document type and each ERP module. An invoice import into the AP module has different field requirements than a payment import into AR. A purchase order import has different requirements than either. Because each extraction sheet is its own template, you can have one template that outputs in the exact format for AP invoice import and a separate template that outputs in the exact format for AR payment application. No shared template that tries to serve both and ends up serving neither correctly.
The SAP DTW two-file structure deserves more attention because it's the most structurally different from what other ERPs want. Most teams try to get the extraction to output a single flat file and then split it manually, which adds back the manual step you were trying to eliminate.
A cleaner approach is to use two separate extraction sheets that run against the same document. One is configured to output only header-level data (one row per invoice) and the other outputs only line item data (one row per line item, with the linking key field included). You export both sheets, and you have the two files DTW needs without any manual reformatting.
The linking key between the header file and the line items file needs to be consistent. Whatever field you use in the header file to identify a transaction, typically the document number or a reference you generate, that same value needs to appear in every corresponding line in the line items file. Set this up in the template instructions and verify it on your first test import before you run production volume through it.
No matter how carefully you've configured the extraction template, test with a single document before you run a batch. ERP import errors on a 200-row file are much harder to diagnose than errors on a 5-row file.
The test process that works well: extract one document, export the CSV, import it into your ERP in test mode or against a test company file, check the result. If something is wrong, identify the specific field that's failing, update the extraction instruction, reprocess the document, and test again. Most extraction tools have a reprocessing window. With Lido it's 24 hours after initial processing, so you can iterate on the format without incurring additional cost per document.
Things to check after a test import: did the date import in the right format and display correctly in the ERP? Did amounts import with the right decimal precision? Did vendor IDs match to existing vendor records? Did line items link correctly to their header transaction? Did required fields that came from a context document (like Internal IDs) populate correctly?
Once a single document imports cleanly, run a small batch of 5-10 documents and verify those before scaling up. The failure modes that only appear at batch scale, like a vendor ID that works for most vendors but has a formatting issue for one specific case, show up here.
Getting the formatted CSV out of extraction and into the ERP is still a manual step if someone has to download the file and trigger the import. For teams processing high document volumes, that manual touchpoint adds up.
There are two ways to remove it. The first is direct API integration: use the extraction tool's API to receive the structured data and push it directly to the ERP's API. NetSuite, SAP Business One, and Sage Intacct all have REST APIs that accept transaction data. This approach requires development work to build and maintain the integration, but it removes the CSV file entirely from the process.
The second approach is file-based automation without custom development: configure the extraction to deliver formatted CSV files to a shared folder or SFTP location, and configure the ERP to watch that location and auto-import files on a schedule. Some ERPs support this natively; others need a scheduled task or a lightweight middleware tool. Combined with email-based document intake automation, this creates an end-to-end flow where documents arrive by email, get extracted, and the formatted output lands in a folder the ERP checks every hour, with no manual steps in between.
For teams not ready to build full API integrations, the shared folder approach is often the right intermediate step. It removes the daily download-and-import manual work while being straightforward to set up and maintain.
The 30-60 minutes of reformatting time per import batch that most teams report isn't random. It's specific tasks: reformatting dates in a column of 50 rows, removing dollar signs and commas from an amount column with find-and-replace, looking up vendor IDs in a separate spreadsheet, reordering columns to match the template, fixing the one row where a leading zero got stripped.
Each of those tasks is predictable and consistent, which means each one can be handled in the extraction configuration instead of in a manual spreadsheet step. When the extraction output already has dates in the right format, amounts without currency symbols, vendor IDs with leading zeros intact, and columns in the right order with the right names, the import is a single step: open the ERP importer, select the file, run. That's five minutes, not 45.
For teams processing invoices and remittances daily, that's meaningful. For teams doing accounts payable automation at scale across multiple document types and ERP modules, it's the difference between a workflow that works and one that creates new manual work every day while appearing automated.
Most teams focus on getting the extraction itself right. Getting the output format right is usually treated as an afterthought. It shouldn't be. It's where most of the friction lives.
ERPs validate CSV imports against strict formatting rules that are not visible in Excel. Common causes include column headers that do not match the template exactly, date formats that differ from what the ERP expects, amounts that contain dollar signs or commas when plain numbers are required, and vendor IDs where leading zeros were stripped by Excel.
NetSuite payment application imports require the Internal ID of the invoice being applied, not the invoice number. The AR account on the payment must match the AR account on the original invoice. External ID and Customer are required fields. If you use undeposited funds, that account must be mapped correctly in the import file.
SAP Business One uses the Data Transfer Workbench which requires two separate CSV files instead of one: a header file with one row per transaction and a line items file with one row per line item. The two files are linked by a key field. Single flat-file exports from extraction tools need to be restructured into this two-file format before DTW will accept them.
Configure your extraction tool to output vendor IDs as text with leading zeros preserved, and export to CSV rather than opening in Excel first. If you must use Excel as an intermediate step, format the vendor ID column as Text before pasting data. In Lido, add an instruction like Preserve leading zeros on vendor numbers to handle this automatically.
Yes. There are two approaches: direct API integration where the extraction tool pushes structured data to your ERP API, or file-based automation where formatted CSV files are delivered to a shared folder that the ERP watches and auto-imports on a schedule. The file-based approach requires no custom development and works with most ERPs.
Download your ERP blank import template first. Use those exact column header names as your extraction template columns. Then add formatting instructions to match the ERP requirements for dates, numbers, and IDs. Test with a single document before running bulk imports. Most extraction tools allow free reprocessing within 24 hours so you can iterate on formatting without additional cost.
NetSuite accepts dates in MM/DD/YYYY format for CSV imports. If your extraction outputs dates in YYYY-MM-DD or DD/MM/YYYY format, add a formatting instruction to your extraction template specifying the correct format. One wrong date field will cause the entire row to fail on import.
No. QuickBooks Desktop uses an import toolkit plugin with transaction-type-specific column mappings. QuickBooks Online has a different import interface with its own field names and format requirements. Always download the blank import template from whichever version you are using rather than assuming compatibility between the two.
%20(1).svg)
