Faster Data Migration in Business Central with Import Export PowerTool

Welcome and Introduction

Good morning or afternoon, everyone. This is Mark, and today I’m going to take you through our Import-Export Power Tool and how it can simplify your life when you’re dealing with volumes of data in Business Central, or with data errors in Business Central that you have no other way to correct. That’s what this little tool is for.

We’re going to start with a bit about who we are, then dive into what the Import-Export Power Tool is, who should use it, and those sorts of things. After that, we’ll spend some time working with it directly.

A little different than usual: if there are specific things you’d like to see imported or exported, just throw them into the question box in the GoToWebinar panel. I saw some questions on the signup sheet about issues importing, or wanting to see a particular type of import done, so maybe we’ll cover off some of those examples during the session and keep it interactive. And of course, if you have any questions as we go, drop them in the question panel and I’ll do my best to answer them.

About Us and the Free Apps

We work mostly in manufacturing and distribution, and we’ve been around for about 10 years. We work mostly through our reseller partners, so if you have questions on any of the solutions we talk about today, or on the stack of products we have, check with your reseller or your BC partner. They should be able to help you out, and otherwise you can contact us directly.

The Import-Export Power Tool is free. You don’t need a partner to engage with us or to get it; you can just install it off AppSource. If you’re on-premises, you can get the Import-Export Power Tool if you have an existing subscription with us, just to simplify the deployment.

We’re focusing on the Import-Export Power Tool today, but a subset of our products are free apps, and there’s a whole bunch of other things you can get for free. There are more than these, but these are the key ones:

• Planning and forecasting, available for free, which makes a big difference in warehousing. For warehousing and manufacturing you can use the enhanced planning worksheet instead of the regular one, and there’s enhanced forecasting as well.
• The more technical apps (shown in green) for cloud printing and generating barcodes on reports.
• Graphical scheduling, for drag-and-drop scheduling of production orders or projects. You can configure it to show any data you like and then drag and drop it. The free tool is manual; we also have automated scheduling.
• Document management, with drag-and-drop storage in SharePoint and a whole bunch of goodies associated with it.
• WMS Express, free warehouse management. If you just need the core warehouse tasks, receiving, picking, shipping, inventory count, and bin movements, this is probably all you need.
• Order Ship Express, for domestic parcel shipments. You can generate shipping labels and get your tracking and cost information, all within Business Central. It takes about 10 minutes to set up, and then you can ship from your sales orders in Business Central.

What Is the Import-Export Power Tool?

It’s a data migration tool like RapidStart. RapidStart is the module name, but configuration packages is what you look at in Business Central, and that’s really the tool Microsoft gives you to do generic import and export across any tables. The problem is it’s clunky, can be hard to use, and is typically quite slow. The Import-Export Power Tool makes that a very fast operation and makes it easy to create your files and import them. It works with text files, though most people, myself included, edit them in Excel and then save or copy them to a text file.

It’s fast. Where RapidStart might take a minute and a half to import a thousand records, or 10 minutes to import 5,000 records, we’ll do that in a minute or less. So there’s a significant difference in speed.

The other thing we can do is delete and modify data within Business Central. That’s extremely handy when you hit an error message you can’t otherwise fix. Microsoft has done a lot to clean up error messages, but you’ll still get something like “some field must be zero” on a journal line, with no way to fix it in the user interface. It’s usually some underlying remnant of data causing the issue, like a reservation entry, and it’s very difficult to fix; you often have to get a developer in to clean it up. With the Import-Export Power Tool, you can go in and delete or modify that data directly in the table, even if there’s no user interface for that table.

It’s also extensible. There are published events, so if you want to do something custom, like importing BLOB fields such as images that we don’t support directly, you can add that capability and import it.

Who Should Use It?

The first thing you’re drawn to is the big exclamation point, and for good reason. If you set up the manager permissions, that gives you almost developer-like access to the underlying data in Business Central, which means you can do almost anything: import, update, or delete any data. That includes ledger entries, and in the current version, general ledger entries and item ledger entries. You can actually modify and delete those. So this is scary stuff, and you don’t want to give it to end users. You don’t want an accounting person deciding something doesn’t balance and just changing a number in an item or general ledger entry.

There are two levels of permissions: manager and user. The manager can have access to the protected tables, like item ledger entries and GL entries, while users don’t. So you can control, based on role, who’s allowed to get at that data.

It also uses the built-in Business Central user permissions. Even if you give someone the ability to change data within the Import-Export Power Tool, if they don’t have the underlying Business Central permissions to that data, they won’t be able to change, update, or delete it. So it’s based on both the Business Central permissions and what you grant in the Power Tool.

It can also disable the change log, which is another scary capability. The reason that option exists is that it can dramatically speed up imports. Maybe you just want to add one attribute, like a posting group or price group, to all your customers for informational purposes, and you don’t want to generate thousands of change log entries. The point is that you have the ability to disable the change log, which means people can import data without any record of the change in Business Central. So again, this is a power tool, meant for power users, not for the general user.

That said, we have lots of customers using it for regular imports of sales prices, items, and even bills of materials, with specific users following a specific, locked-down process. That’s fine. Just don’t open up the entire tool to everybody. I don’t want a phone call saying the Power Tool wrecked your system because you deleted all your GL entries. Yes, it lets you do that. Don’t do that.

Exporting Data

The export capability lets you export essentially anything in the system, and it can export a whole bunch of different tables to a single text file. Unlike RapidStart or config packages, where you get one sheet per table, you’ll get one big text file with all those tables. You can also use a configuration package: one of the nice things about RapidStart is that you can configure your export, choosing the tables, the fields, and the order. You can build up those export details in a config package and then export them using the Import-Export Power Tool, faster and with more options.

You can also export record notes and record links, which is handy if you’re coming from NAV. For example, if your server name changes and you want to update all your stored links, you can export them, make the change in Excel with search and replace, and re-upload them. Spoiler alert: you can import record notes and record links as well.

First-Time Setup

The first time you run this, it pops up and asks you to do the setup. There are two permission sets: one allows you to edit the setup, the other only allows you to run the tool. That’s how a manager can control what people are allowed to do, while the end user can only do what’s been set up for them.

For update and delete, there are three options: not allowed, unrestricted, or enhanced confirm, which gives you a secondary prompt that forces you to type in some characters to prove you’ve read the message and want to proceed. There’s also the disable change log option, and the protected tables setting, which really only applies to inserts and updates. The protected tables are the GL entry, item ledger entry, fixed asset entry, and all the other entry-type tables you normally can’t edit within Business Central. The setting controls whether people are allowed to touch them.

Export Options

If you just want a single table, you can find it or enter its number. For example, the item table is 27, so you can export the item table, and you can also list other tables, like customers, to export them together. If you know the table IDs, or want to look them up, you can do that.

The other option is to use a configuration package. In a config package you might add the item table but choose only the fields you want, in the order you want them exported. That’s all standard Business Central functionality. One thing to watch: Business Central may add new fields to your package on its own. In this case it added the new sustainability fields that were created after the package was built, so the export grew from the fields I’d selected to include those extra ones.

Field Validation

In the config package there’s a validate field checkbox. When it’s on, it runs the underlying Business Central logic as if you were typing the value into the system directly. For example, when you type in a base unit of measure, Business Central runs logic to check that the unit of measure exists, and so on. That’s a standard Business Central concept, and we use it within the Import-Export Power Tool as well.

The File Format

When you export, the tool creates a tab-separated text file, with a tab between each field and each value. In a text editor like Notepad++ it looks ugly and doesn’t line up, but you can right-click the file and open it with Excel, or just copy and paste it into Excel, and the columns line up as you’d expect. From there you can make whatever changes you want and then import the file. In fact, if you’re having trouble figuring out what the file format needs to look like for an import, just export one record and you’ll see exactly what you need: the table name, then a row of field names, then the data underneath. If you have two tables, the second simply continues below the first, and you can refer to tables by name or by ID number.

When you export from a config package with validated fields, you’ll notice an asterisk in front of those field names. That asterisk tells the tool that the field needs to be validated on import. Everything else gets imported without running the underlying Business Central business logic.

Exporting Dimensions

You can export dimensions, both the global dimensions and the transactional dimensions. Default dimensions, like those on a customer, live in a separate default dimension table that you can export and import directly, since it ties back to the source record. Transactional tables are different: they have shortcut dimensions assigned to a journal line or sales line, with no separate table, using underlying dimension set IDs. We’re now able to export and import both the default and transactional dimensions. The only thing we don’t handle is job task dimensions, which you have to do separately by importing or exporting the job task dimension table. You can also set a dimension filter, so if you have a hundred dimensions defined but only want three, you can list just those.

In the exported file, the tool puts the keyword DIM, followed by the dimension code defined in Business Central, and then pulls out those dimensions for each record. On import, you simply put DIM, a colon, the dimension code, and then the value, and the tool automatically sets the shortcut dimension and the dimension set ID. This is one of our most requested features, typically used when importing journal lines.

Record Notes and Links

You can export the record links and notes. The exported file shows the link, who created it, when it was created, and so on, and if there were notes on the record, they’d be listed too. To import them, list the record as many times as needed and put each link and each note on its own line. This works on everything that supports record notes and links: sales headers, customers, and other records in Business Central.

Text Encoding

If you’re creating your own files, the encoding matters. I suggest Notepad++ or TextPad rather than Notepad. These will normally save in UTF-8, though some tools save in the old ANSI or MS-DOS character set. If an import fails with a weird message, like a missing table or missing fields, it may be that the file is in a different character set. In TextPad you can check the encoding directly. Ideally, leave everything in UTF-8, which is Unicode, and that will correctly export and import special characters.

Importing Data

You can import into any table, and you need to grant the setup permission to allow people to import into protected tables. To build the file, you can export the table and fill in what you need, use a RapidStart package to export only the fields you care about, or create the file manually in Excel.

There are some special things it does. You can import lot numbers and serial numbers into item journals just by specifying them. In newer versions of Business Central, around BC 22, you can set the serial and lot number on the line, but we do more than that: you can bring in the package number, lot number, serial number, and expiration date on any journal, whether it’s a physical inventory journal or an item journal. You can also import record links and notes, and you can import directly to ledger entries.

Importing to ledger entries is a good one. There’s a video on our YouTube channel about it. When doing opening balances for inventory, a lot of people import item journal entries, then post them and try to offset them, and it becomes a big mess. Instead, you can import directly to the item ledger entries without posting anything. That doesn’t impact your GL and has no cost issues, but it gets you your sales history immediately, which helps with forecasting and lets you break history down by customer. And again, you can import the dimensions, which was the biggest ask, and disable the business logic when needed.

Import Options

The allow record inserts option creates new records if one can’t be found. If a record already exists and inserts are on, you’ll get an error that it cannot insert that record because it already exists.

On error handling: a question came up about whether it fails one line at a time or fails the entire import. It fails the entire table. RapidStart stages the data and lets you fix failed lines before committing, but this tool writes directly to the tables with no second step to review the data. If it fails partway through, it tells you which line failed and what the issue is, and you fix it and re-import. So if there are 100 lines and line 99 fails, the previous 98 lines are rolled back as if they were never imported.

That brings us to commit behavior. If you have a file with many tables and choose to commit after each table, then if five tables import successfully and the sixth fails, those first five stay imported and you only have to restart from the sixth. There’s still no review of failed data; when a table fails, it rolls back everything it did for that table.

For missing fields and tables, you can choose what happens if a table or field can’t be found: prompt to continue or cancel, ignore everything, or generate an error that forces you to stop and fix your data.

There’s also a two-step insert option, which is usually safest but a little slower because it does two things: it inserts the record with the primary key first, then updates the remaining fields. This matters for tables where you can’t set all the field values and insert them at once. The item table is a good example: if you try to insert an item record with a production BOM number set, the way the logic works, you effectively insert a blank item. So you insert the item number first, then fill in the rest. For something like item journal entries, you don’t need this on.

With record updates off, the tool only updates existing records, and if it finds something in the file that doesn’t exist, you get an error because there’s no record to update and inserts aren’t allowed. With everything on, it will insert and update records.

The validate field data option is where the asterisk in the text file comes in. If a field has an asterisk, the tool runs the underlying Business Central business logic to validate it. So if you set a production BOM number on an item and validate it, it runs all the related logic. Item tracking code is another good example. Sometimes you deliberately leave the asterisk off so a field isn’t validated. For instance, if you want to force a change to the item tracking code on a bunch of items, validating it would trigger checks that block the change, so you leave it out and force it. Just don’t do that unless you know what you’re doing.

The remove quotes option is best left on. When people save an Excel file as a text file, Excel surrounds text that contains commas or quote characters with quotes, and you don’t want those imported as part of your description. If it sees a quote at the start and end of the text, it removes them, and if it sees two double quotes in the middle (an escaped quote), it cleans that up too.

Finally, there are the run-on-insert and run-on-modify options, which run the same underlying business logic as if you were typing the record into Business Central, and the disable change log option discussed earlier.

Import Walkthrough: Item Journal

For this example I’m importing into the item journal line, with the required primary key fields. The easiest way to find what you need is to export an item journal line, or look at the primary key fields in a config package. The item number has an asterisk to validate it, because when you type an item number into a journal line, Business Central pulls in the description, base unit of measure, and other details. I want that to happen here too, so I don’t have to import all those fields myself. I also have a posting date and other fields, the serial number for item tracking (you can also bring in lot number, package number, and expiration date), and some dimensions, including shortcut dimensions like department and area that show up on the journal line, plus a purchaser, which is not a shortcut dimension.

Back in Business Central, I’ve turned everything on, which is the slowest way to import. Normally I’d only turn on what I need. I import by dragging and dropping the file or loading it. The first run took about 2.6 seconds; clearing the data and running again is roughly a second. The import lands in the chosen import batch, with the serial numbers, the dimensions (department, area, and the purchaser dimension), and item tracking all set, and it’s ready to post.

If you try to delete these lines normally, Business Central throws errors, but you can delete them from the Import-Export Power Tool. Just be careful, because there are also records in the reservation entry that you’d need to delete as well. Importing dimensions does slow things down; without them the import would be a lot faster. Either way, you can bring in thousands of journal lines very quickly.

Import Walkthrough: Customers

To import customers, create a file with the table ID Customer and a few fields. The tool doesn’t automatically generate the customer number; you have to provide it, but Excel makes it easy to generate sequential numbers. You can save the file from Excel as tab-delimited text, or copy and paste it into a text editor and save it, for example as xcustomers.txt. Then in the Power Tool, go to import, set your options, and drag the file over. Your customers appear in the customer list. If you later need to add data, take that same file, add the additional fields, and run the same import. Because allow record updates is on, it updates the existing records and adds any new ones.

Updating Data

You can update any field on a table. The idea is to fix bad data or make changes to a single record or a whole bunch of records, and to avoid the table triggers. That means you can change a field even when the user interface won’t let you, and you can do search-and-replace style updates.

You specify the table, select the field to change, and set the value. If you turn off validate field data and the value isn’t valid for that field, the tool will still let you set it, though there are some cases with a stronger database link where it won’t. You then set a filter to target specific records. Once the filter is set, you can preview the filtered records, or view the raw data for tables that don’t have a page in Business Central. When you run the update with enhanced confirm on, you type the confirmation text, it tells you how many records will be updated, and you can undo it. This works on any table in the system, including protected tables like item ledger entries or GL entries, if you’ve been granted that access.

Deleting Data

Delete works the same way. You specify the table and a filter, then delete that data; the tool keeps the last table number you entered. For example, you might delete any customer whose number starts with X0. The on-delete trigger runs the code that would normally happen if you deleted those records manually, which often also deletes related information, such as a primary contact associated with the customer. You typically want that on, unless it produces error messages, because otherwise you’d have to delete those related records manually. Then you run delete data, enter your code, confirm, and the records are gone.

Where to Learn More

If you need more information, our YouTube channel has a number of videos, especially on importing historical sales data into the item ledger entries, along with a few other topics. You can also check out the website and contact us there, and if you’re a partner, give us a shout.

Questions and Answers

Q: There’s a question about a config package issue in the latest version of Business Central, and about altering the configuration package processing order.

A: We don’t change anything on the config packages. The only thing we add is a menu to export the package as a text file instead of Excel, so any underlying Business Central behavior isn’t something we’d address. As for import order, it follows the order of the fields in your file, which you can rearrange in Excel. If you wanted a field like city to come in before another, you’d move that column and the tool would import the fields in that order.

Q: Can you export attachments?

A: No, we don’t export attachments. They’re stored as BLOB or media fields within Business Central. If you need to move attachments, that’s where DocExtender comes in. DocExtender can take attachments, upload them to SharePoint, delete them from Business Central, and create a SharePoint link for them at the same time.

Q: Can you lock the delete capability for specific users?

A: It’s essentially all or nothing; the setting is global, not per user. That said, if a user doesn’t have delete permission on the table in Business Central, they won’t be able to delete that data through the Power Tool anyway. The one thing you can do is set delete to no access so nobody can delete anything, unless an administrator temporarily enables it.

Q: Does it overwrite any permission sets?

A: No. There are two additional permission sets you can apply, manager and user, which you assign appropriately. We don’t change any other permission sets, so whatever users have in Business Central is respected, with the partial exception of the protected tables, which they can’t edit directly in Business Central but can edit through this tool.

Q: Exporting from Excel doesn’t seem to add a carriage return.

A: Saving from Excel as a tab-delimited text file generates the exact file you need, with carriage-return line feeds, ready to import. It will save the active sheet rather than all worksheets, which is what you want. Quite often I save the Excel file as an xlsx to keep any formulas, then copy and paste into a text file, but that’s up to you. If you’re seeing this issue, it might be because you’re on a Mac, in which case try copy-pasting into a text file instead of saving the Excel file.

Q: How does deleting quotes work on import?

A: That’s the remove quotes option. If your description has something like 12 inches written with double quotes, that’s fine; it won’t remove that. What Excel does is add an extra set of quotes, so you’ll see doubled quotes, and we remove one of them to make it look right. So you can keep legitimate quotes inside your file with no problem.

Q: How long would hundreds of thousands of lines take?

A: It depends on the table and which settings you have turned on. For GL journal lines, I’d validate only the important fields, like the GL account number. 100,000 records might take a few minutes, possibly around 20 minutes, but it’s not an all-day job. That comes down to the underlying Business Central processing time, and RapidStart would never finish 100,000 records, whereas this works fine.

Q: What logging is available?

A: The only logging is the change log, and only if you haven’t disabled it. There’s no separate log of what was imported, other than the text file itself, which is effectively your record of what you imported. Incidentally, if you have access to the protected tables, you can use the Power Tool to delete change log entries; don’t do that as a habit. Business Central has a retention policy to manage change log entries, but some people have had millions of entries to clear quickly, and the tool can do it, though it’s still slow.

Q: Can imports be automated?

A: Automation was one of the features that got left off this release. You can already automate it with AL code; there’s an interface where you can set all these values and run it through AL code. A Power Automate interface is coming, though I can’t guarantee the timing, since it was meant for the last release and didn’t make the cut. And if you want to write your own, you’re welcome to.

Thanks to everyone who stuck around. I hope it was informative, and let us know if you have any additional questions.