Connecting Excel to ERP: options and limits

Getting data from the ERP (the business system for orders, stock and finance) into Excel is simple: with an export or with Power Query, if your ERP has a documented entry point for it. The other direction, from Excel to the ERP, needs more care. Then use an import function of the ERP, an Excel add-in from the supplier, an integration or a software robot (RPA, software that takes over an employee's actions on screen), and check the data before it goes into the ERP. If a lot of data goes back and forth between Excel and the ERP every day, Excel is often no longer the right place for that data.

Two directions, two kinds of risk

In practice, "connecting Excel to ERP" means one of two things. Either you get data out of the ERP into Excel, for example for a report or an analysis. Or you put data from Excel into the ERP, for example orders, prices, items or a bill of materials (BOM: the parts and quantities of a product). Engineering exports such a BOM, for example, as an Excel file from the CAD package (the drawing software) or the PDM system (drawing management).

The first direction carries little risk. If something goes wrong, your report is incorrect, but the ERP stays correct. The second direction is riskier. An error in an Excel file then ends up in the ERP, and from there in order confirmations, invoices and the planning. That is why this article is mainly about checks in that second direction.

The example: orders as an Excel file

Worked example of Excel orders (assumptions: 20 orders of 4 minutes, 230 working days, 15% exceptions, 15 minutes of checking per day)
Item	Per day	Per year
Manual work now: 20 × 4 minutes	80 minutes	approx. 307 hours
Exceptions stay manual work: 3 × 4 minutes	−12 minutes	approx. −46 hours
Daily check of the automation	−15 minutes	approx. −58 hours
Time actually freed up	53 minutes	approx. 203 hours

The 80 minutes do not count in full as savings, because the exceptions and the check stay work for people. With a small flow, that fixed checking time weighs heavily: here it is almost a fifth of the manual work. Building, licences and maintenance are costs that you calculate separately. For all 80 orders together, PDF and Excel, the same worked example comes to approx. 985 hours per year. The rest of this article shows which route gets such an order file into the ERP, and where Excel causes surprises along the way.

Six ways to connect Excel and ERP

1. Exporting and importing through CSV

Almost every ERP can export a list to Excel or CSV. CSV is a text file with columns, separated by a comma or semicolon. Many ERP packages can also import a file, if it has exactly the layout that the package expects. This is the simplest route: little to build, and the ERP runs its own checks during the import.

Watch the details. Which separator Excel uses when you save as CSV depends on the regional settings of Windows. Microsoft warns that changing that setting affects all programs on the computer. If you open a CSV file by double-clicking it, Excel applies its default formatting and, for example, leading zeros disappear. So agree on a fixed layout and test with a real file, including the date and the decimal separator.

2. The standard import functions of the ERP

Many packages have an import function for fixed types of data, such as items, prices, business relations or journal lines. There is often a template with it, and after the import you get a report with the lines that were rejected. Look in the documentation of your package for the import functions, and ask the supplier which fields and checks they include. Do not assume that there is also an import for orders: check per package which types of data the import can handle.

The risk is in what happens after the import. A report with an error at line 312 of 400 is easy to miss. Agree on who reads the report and what happens to rejected lines.

3. Power Query: reading data from the ERP

Power Query is the part of Excel that you use to get and transform data, on the Data tab. Among other things, it can read an OData feed: a standardised way in which a system makes tables available. Business Central, the ERP from Microsoft, offers such an entry point, and Microsoft names Power Query in Excel as one of the programs that can read OData. Microsoft mentions the separate Business Central connector of Power Query only for Power BI and Power Query Online. So in Excel, you use the OData feed.

Use this route only if your ERP documents the entry point itself. Power Query signs in to the ERP with an account, for example your own work account. It refreshes the data in Excel: with the Refresh All button, when you open the file or every so many minutes. Important: Power Query writes nothing back to the ERP. It is a route for reports and checks, not for input.

4. An Excel add-in from the ERP supplier

Some suppliers offer an add-in for Excel. Microsoft Dynamics 365 Business Central has one. With Open in Excel, you can view and edit a list only in the file; changes do not go back. With Edit in Excel, you can change records in Excel and then publish them to Business Central. Changes go back only when you publish.

The limitations according to Microsoft: Edit in Excel is available on most lists, but not on all of them. The add-in works in Excel for the web and in Excel for Windows, not on the Mac. And if OneDrive is set up for system features, Business Central limits the file to 100 columns. If you use a different package, ask whether the supplier has a similar add-in, and what it checks.

5. An integration

With an integration, an automation reads the Excel file and puts the data into the ERP through its API. An API (application programming interface) is a fixed entry point for software. The ERP replies to each order with an order number or an error message, so you see immediately what failed.

You can also do this with Power Automate (the Microsoft software for automating tasks) and the Excel connector (the ready-made link to Excel), if the file is in OneDrive for Business or SharePoint. Know the limits: the data must be in an Excel table, files can be at most 25 MB, and listing rows returns 256 rows by default, unless you turn on pagination. Microsoft also does not support a flow and an employee writing to the same file at the same time; that can lead to inconsistent data.

6. A software robot

A software robot works through the screen, like an employee. It reads the Excel file and types the data into the ERP. This is the route if the ERP has no usable import and no API. But a robot is slower than an integration and sensitive to screen changes after an update. You can read how to weigh this up in RPA or API integration: which is the best choice?

The six routes compared
Route	Direction	Strong in	Limitations
Export and CSV import	Both	Simple, the ERP checks during the import	Separator, data types, reports that nobody reads
Import function of the ERP	Excel to ERP	Master data and prices in batches	Fixed types of data, not always for orders
Power Query (OData)	ERP to Excel	Reports and checks that update themselves	Read only, only with a documented entry point
Add-in from the supplier	Both	Editing lists in Excel and sending them back	Not with every package, not on every list
Integration (API)	Excel to ERP	Daily flows, immediate error message	Needs a usable API and management
Software robot	Excel to ERP	ERP without import or API	Slower, maintenance after screen changes

The limitations of Excel in this chain

The route is rarely the biggest problem. Excel itself is more often the problem, because it is made for working freely. Six points deserve attention.

Excel as the source of truth

If the agreed prices are only in an Excel file and not in the ERP, there are two versions of the truth. The ERP calculates with one price, and the salesperson promises the other. Decide per type of data which system is the source of truth. Usually that should be the ERP, and Excel only reads along. The same applies to a BOM that process planning keeps in Excel next to the ERP: then nobody knows for sure which revision the shop floor gets.

Versions and copies

A file that goes around by email gets copies: "price list final", "price list final 2". Nobody knows for sure which version was imported. Put the file in one fixed place, share a link instead of a copy, and record which version was put into the ERP and when.

Validation

With Data Validation in Excel, you can guide the input, for example with a drop-down list or a minimum and maximum. That helps, but it does not check whether an item exists in the ERP, whether a price matches the agreement, or whether an order was already processed. Those checks belong just before the import, against the current data in the ERP.

Two people at the same time

Working in one workbook at the same time (co-authoring) is possible with Microsoft 365, if the file is in OneDrive or SharePoint Online and AutoSave is on. According to Microsoft, in a conflict the change that was saved last wins. SharePoint on your own server does not support co-authoring, and one colleague with an old Excel version can lock the file for everyone. For a file that an automation reads from, that is a risk.

Data types and leading zeros

Excel automatically changes data that you enter or open. Microsoft names the main cases: Excel removes leading zeros, sets the last digits to zero in numbers of more than 15 digits, and turns combinations of letters and numbers such as JAN1 into a date. A customer item number 004512 then becomes 4512. In the best case, the automation does not find the item. In the worst case, it finds a different item.

In Excel for Microsoft 365 and Excel 2024, you can turn off those automatic conversions, through File, Options, Data. That setting also applies when you open a CSV file, but according to Microsoft not to Power Query. So in Power Query, set the data type of such columns to Text yourself.

Macro security

A macro in VBA (Visual Basic for Applications) looks like a quick way to send a file to the ERP. But by default, Office on Windows blocks macros in files from the internet, such as email attachments. So a macro file that you send around by email does not work for the recipient without extra steps. Among the solutions, Microsoft names a trusted location and macros with a digital signature from a trusted publisher. If one colleague builds the macro, nobody knows exactly what the file does any more once that colleague leaves.

The example worked out

Example (illustrative)

The manufacturing company chooses the simplest route that works for each step. In this example, the ERP has a documented API for sales orders.

Collect. Order files arrive in a fixed folder, also when a customer sends them by email.
Convert. For each customer, it is recorded which column means what. The automation reads the file and converts the columns to one fixed order layout. It reads item numbers as text, so leading zeros stay.
Translate. Customer item numbers are translated with a cross-reference table: a list with the customer's number and the company's own item number. That table is in the ERP, not in a separate Excel file.
Check. Do the customer and the items exist? Is the price within the margin of the agreement? Has the customer order number not been processed yet? Is the delivery date feasible?
Create. If everything is correct, an integration creates the order through the API. The ERP returns an order number or an error message.
Exceptions. The three orders with an exception per day go to a review queue, the list of cases that an employee reviews, with the original file and the reason. The inside sales team decides.

If the ERP had no API but did have an order import, step 5 would create an import file in the layout of the ERP. If the ERP had neither, a software robot would enter the approved orders through the screen. Steps 1 to 4 stay the same in all cases. You can read how the whole order process works, including PDF orders, in automating manual order processing.

When Excel is no longer the right place

Excel is strong in calculating, analysing and quickly trying something out. As a central place for data that a business process runs on, it is weaker. These are signs that the work belongs in the ERP or another system:

Every day, you retype data from Excel into the ERP, or the other way round.
The Excel file is the only place where an important piece of data is kept, such as the agreed prices, the current BOM or the real stock.
Several people work in the same file at the same time and overwrite each other's work.
You must be able to show who changed what, and when.
The file gets slow. A worksheet has a limit of 1,048,576 rows, but problems usually start much earlier.
An error in the file costs money directly, for example in a price or a bank account number.

Then put the data in the system where it belongs, and let Excel only read it for analysis. First look at what the ERP can do itself, such as price agreements per customer or a cross-reference table for customer item numbers. More examples of work that you can move this way are in the guide to automating business processes.

Decision framework

Questions to ask internally

Which direction is it: from the ERP to Excel, or from Excel to the ERP?
How often does it happen, and how many lines each time?
Who makes the Excel file: a colleague (for example in engineering), a customer or a supplier? And does the layout ever change?
Which route does our ERP offer: an import function, an add-in, an API? Ask the supplier.
What must happen to a line that is not correct, and who decides about it?

Which route fits

Situation and logical route
Situation	Logical route
Report or check on ERP data	Export, or Power Query if the ERP has a documented entry point
Now and then a supplier price list or a set of items	Import function of the ERP, with checks in advance
A BOM from CAD or PDM, after each release	Import function of the ERP if it can handle BOMs; otherwise an integration with the PDM
One employee updates many records in the ERP	Add-in from the supplier, if there is one
Orders or other files from outside every day	Integration, or an order import if there is no API
ERP without import and without API	Software robot, with monitoring and a test after every update

The factors that weigh most are the direction (reading is much less risky than writing), how often it happens, and what your ERP officially supports. Outside help makes sense if data goes into the ERP every day, if files come from many different customers, or if an API is needed. The checks and the exceptions make the difference between a handy file and a reliable process. You can read more about integrations with an ERP on the services page, under ERP integrations.

Conclusion

Excel and the ERP can work well together, as long as the ERP stays the source of truth. Reading from the ERP is simple and low-risk. Writing to the ERP needs a route that the ERP officially supports, checks before the import and a fixed place for exceptions. Many errors do not come from the route, but from Excel itself: lost leading zeros, wrong copies and two people in the same file. If you see those signs every week, the work probably no longer belongs in Excel.