Skip to content

On demand · Product video

Import items, item journals, and sales history with Import Export PowerTool

For partners and administrators who need to bring opening balances, serial numbers, and history into Business Central from another system.

Recorded August 19, 2024 · 25 minutes

Import Export PowerTool import data video thumbnail

Summary

This session shows how Import Export PowerTool imports data into Business Central from text files. This is a completely free app from Insight Works for Business Central. It imports item journal lines with serial numbers, marking the item number and quantity with a star so the standard business logic fills in the rest, and explains each import option: insert or update only, two step insert, field validation, field delimiters, missing fields, insert and modify triggers, and text encoding. It then imports 500 new items, summarizes about a million sales transactions into monthly totals with an Excel pivot table, and imports those totals directly as item ledger entries, so the sales history appears without posting a journal. The session ends by importing record links onto sales orders.

Key topics

  • Prepare an import file with a table ID and starred fields to validate
  • Choose import options such as two step insert, triggers, and encoding
  • Import item journal lines with serial or lot numbers for opening balances
  • Import monthly sales history directly as item ledger entries
Read the transcript

Hey. Good morning. Afternoon, everyone. This is Mark Hamblin here. And today we’re going to spend a little bit of time on our import export power tool and go through how you can use that to sort of improve your efficiencies when you’re doing imports and other things. So now we can import, you know, again, any table including, the sub ledgers and things like that. If you want to import, you know, sort of the sub ledger stuff, you do need to enable that in the setup that you have access to the product tables. Otherwise it’s just going to skip that table entirely, not import it at all.

And as I mentioned earlier, you know, in a text file I can create a text file with any fields I want in it and randomly pick what I want. Add remove fields. Don’t have to go into a config package and deal with that or anything else and just suck it in. A couple other little goodies that you can do. You know, where this is often used is when you’re, you know, spinning up a new environment. And you need to import sort of opening balances and things like that. So one of the nice things is you can import a lot and serial numbers to an item journal, and quite often you’re coming from one system or another and you need all those lot numbers and serial numbers.

Well, yeah, I can suck those in automatically using the Import Export power tool and create all the appropriate entries and where you go. We also already talked about notes and links and again ledger entries. And I’m going to show you this as well because this allows us to go in and bring in things like sales history. Now typically when when people bring in sales history, what they want to do is that they bring it all in as journal entries and post those journal entries. And then after zero out to general entries and all that kind of stuff.

Well, the power tool, you don’t have to do any of that. I’ll show you where you can bring in, you know, old entries like history, historical data without having to bring in journals and post and all that kind of stuff. All right. And, a few other things here. And, you know, again, the events, if you want to bring in, like logs, like, files that you want to bring in as attachments, things like that, that’s where you would extend it yourself with those events that are published. All right. So let’s actually have a look at, how we do imports.

I’ll show you a couple of quick examples here. So, actually the first one I’m going to show you is I will actually show you, you know, that that item journal, import, that I just mentioned. So here I’ve got, you know, a file here. And what it’s going to do is it’s coming in to the item journal line. And up at the top, we’ve got all the fields that, that we want to fill in. And, you know, you can use an export to actually, you know, generate, the file format you need. But really all you need is this table idea at the top.

And there can be a table name or number. So that could be, you know, table 83 or whatever it is that 81 or 83 or something like that or the name. And then what we need is really, you know, sort of the primary key fields to be able to insert and then all the other fields that, that we might need. Now, so this is the file that I’m going to be importing. And you’ll notice we’ve got, you know, a bunch of, items here. And, we’ve got this serial number, column. So the serial number column is going to bring in all of these serial numbers.

And if I had a lot of numbers, I would just come in and, you know, add in, the lot number field and, expiry date field and all that kind of stuff. And if I entered information in here, it would import that as well at the same time. Okay. So that’s that’s basically what we’re going to do. We’re going to bring this in and you’ll notice here on the quantity, we’ve got a star in front of the quantity. And we’ve got a star in front of the item number two. And you’ll see in a seq what that means is we’re going to validate that information.

So the idea behind that is you know, none of these are other fields. We’re not going to run any additional business logic when we bring in these additional fields here. The only time we run the built in business central business logic is when we have a star. So the idea behind this is, you know, what you want to do is, think about how you would type this data into an item journal or any, any screen within Business Central. I type in the item number and I type in the quantity. Most of the rest of the stuff would probably default it.

I’m of course serial number, but anything that I would normally manually type in, you may want to validate that because it runs the underlying business logic for that field. So for example, when I type in an item number, the underlying business logic brings in the description, brings in the unit of measure, brings in all of those sorts of things. If we validate that on import, it’s going to run that same logic and fill in all of those fields for us automatically. So we don’t actually have to import those fields. So you see there’s some unit of measure.

I mean, if you needed an alternate unit of measure listed here, but we don’t have to import the base unit of measure. We don’t have to import the description, any of those sorts of things because we’re validating it. It’s going to bring it in automatically. All right. So that’s that’s the file I’m going to use initially to to import an item journal. So we’re going to the power tool here and I’m going to go to import. And here we’ve got a few, different things that we can do. So let’s, let’s talk about these options real quick before I run the import.

So obviously we want to import data and insert that data. But I could also just say you know what. I’m going to import existing records and only update. And if it finds a record that doesn’t live in the in this essential already, it’s going to give me an error message and saying, I don’t have that record to update. You can import that record, right. For for the current example, I’m going to be doing the import into that and drill this two step insert. That’s kind of the default. And you know there’s no harm in doing that.

It slows things down a little. Because what this does if you have that enabled is it first inserts the record with just the primary keys. So let’s say I’m I’m importing a customer record or a bunch of customers. And I’ll sort the record with just the customer number. And then it’ll do an update to update the remaining fields on that now existing record. Okay. So that’s very handy in a lot of cases. So in some cases within Business Central, it needs that, that functionality enabled. Otherwise it won’t import properly. Right. Because when we actually insert the record it goes and makes a bunch of changes to the fields that that were in your import.

And now your fields are an import properly. So sometimes you need to turn this on the item journal import. I’m going to leave that off because I know I don’t need it. But if you if you leave this off by default you import it. And your results are you know what you expected. Turn that on and you’re good to go. Like I said, the slows things down a little because it does two operations. It does an insert and it doesn’t update. This turned off just as the concert. Okay. Down here is that validation. So I can validate all fields, which means every field I import runs the underlying business central business logic.

You typically don’t want to do that. You typically want to you know, again mark any fields for validation that you would normally have to manually type in anything that you manually type in into the user interface. When you’re bringing in data, you’d want to set up to be validated. And again, if you if you run an import and it doesn’t work the way you expect, you can switch it and have it validate all fields where you go going and get better results. This field delimiters quite often. I mean, this is not that, you know, common, but quite often if you’re exporting something, bring it into Excel and then saving it, Excel is going to add a bunch of quotes and stuff around some of those, some of those fields.

So what this will allow you to do is the system will automatically remove those quotes if it detects the fact it got extraneous quotes within the data. Right. So like in a description that has a comment or something like that, Excel is going to put extra quotes around that. Well this will script those quotes from that data automatically. Then over here, if I have missing fields or tables it’s going to prompt me. So in other words, if I’ve got an import and I’ve got a I typed a field in there that doesn’t exist in Business Central or exported from Have.

And I’m bringing in Business Central. It doesn’t exist. It’ll just pop up and say, hey, that field doesn’t exist. Do you want to continue? And I can cancel the import at that point, or I can just ignore and missing fields, which is great, right? Because if I have, you know, a table in nav, it’s got a whole bunch of custom fields. I can export that. Enough. Suck it in here. If I don’t have those custom fields in BCC, it’ll just ignore them and import the core data. And that’s great for, you know, doing demos or proof of concepts using Nav data or other systems data within.

Let’s see. We’re going to have an error out and just prevent me from importing if the fields don’t exist with it. You see these guys down here? Again, more business logic validation. So the idea behind this the on insert trigger, after I insert a record, this will run any additional business logic to, you know, update the data or update alternate data, those types of things. So quite often when you’re doing inserts it’s worth having that turned on. And same thing with, with updates when you’re modifying like the long record updates. Probably want to turn that on, you know, to, to run that underlying business logic.

But again, you know, you can, give it a shot if it’s not working, we expect you can turn these on or off. And of course are disabling with change, like I mentioned earlier. And then finally the text encoding, you know, you typical one is UTF eight. Those are the two. UTF eight gives you the sort of international carrier, characters and things like that. Otherwise I must also this is kind of fun as well. All right. And by the way, if I export something from a, you know, an external system like. Net suite or something like that, it’s likely going to save that as UTF eight.

And then those text editors like that notepad plus plus I mentioned you can actually check what the encoding is. So it’ll tell you if it’s UTF eight or and see which is in the stars. But typically it’s going to be UTF eight if you’re exporting from an external system. All right. Anyway those are the details. Like typically not much you have to modify on here I’m going to do the record inserts and do the import data. We’re going to come in. We’re going to grab that item journal that I had before. That’s going to grab it.

And by the way, I think I had wanted to have 501,000 lines on that. So it’s going to go through for what it’s doing here while it’s importing, because it’s creating those, those, item journal lines. And again, as you know, in Business Central, the first time you do a new operation for the first time takes a little longer. So if I’m doing this, you know, a second time, if I did that same import a second time, it’s probably going to take like 10s. Right? Because this the first time I did, it takes a while for to cache the data and the code and everything else, but it took 25 seconds to import themselves and records.

Typically it’s a lot shorter than I. Normally when I do this, it’s, you know, ten 15 seconds for a thousand records. But even this isn’t bad because this is a linear time for the most part, which means that if I had 10,000 records here, it would be about ten times the amount of times it would be 200 and 50s. Right. So less than five minutes to import 10,000 records. And again, typically it’s going to be a couple of minutes. We’re not talking about five minutes import 10,000 records. It’s just this is the first time I did it so very fast to do those imports.

And then of course, to see what it actually did when it did that import. It’s going to the item journals. And I said import into the import batch. Here’s all the data that’s imported those 5000 lines. And then of course each line had a serial number associated with it. So it brought in the serial number and everything appropriately. And as I mentioned, we had a lot number expiration date. It would have brought those in as well. So it’s an easy way to bring in your serial numbers, lock numbers, those sorts of things. And you’ll notice actually when I go in here, no.

As of Business Central 22, I believe they have this option as well. Item tracking on lines if you’ve got that enabled, like I did for this. What that also allows you to do is import without using the import export prior to that serial number or lot number right onto the line itself. And, you know, in that case, you probably don’t you don’t actually need to use the import export power tool to bring in serial numbers, lot numbers. It just be way, way slower. Right. So anyway, that’s what this thing does. And because I opened up that, that serial number page and now listed on here, but all of these guys do have the appropriate serial number brought in automatically based on that import export power tool.

All right. So again this is how I’d want to bring in opening balances. Right I’m going to bring in the opening balance for all of these individual, items and serial numbers and everything else. I’m going to post this journal and I’m good to go. But if I want to bring in history, I don’t necessarily want to do that. Now. Again, a lot of people, when they’re bringing in history, the brand into the item journal I posted to get the ledger entries, that generates journal entries and value entries and a whole bunch of stuff. And that’s kind of ugly.

What I want to do is I want to bring in, you know, sales history for this particular customer, without having to do all of that kind of stuff. So I’m going to show you how to do that. And this is going to take a little longer. And I’m going to run out of time for sure. But I’m going to show you this because it’s kind of interesting. We get this question a lot. So the first thing I’m going to do actually is I’m going to import, a bunch of items. So if I go in here into my item list.

And I wakey wakey. All right. Now I’ve got a bunch of the standard items, but if I look, you know, in this range here I’ve got two items and sort of that 9 million range or whatever I’m going to bring in. So I’m first I’m going to import the items for, for this particular customer. So we’re going to import a whole bunch of new items. So what I’m going to do is come in here, hit import data. And the the items that I’m going to bring in, I’ll just open it up here, open it up in the text editor.

So it’s just, you know, starting with nine and I’ve got, 500 items that I’m going to bring in. Okay. So I’m going to bring in 500 items is very little information. I’ve got the item number description, some hosting groups. And that’s basically what I’m I’m going to bring into the system. So we’re going to grab that guy. We’re going to bring in those those items. And by the way in this particular case I would have normally turned off that, in one of the on insert, trigger because I actually don’t need it. I know, just based on the way the code looks, that I don’t actually need this.

With that on, it takes a little more time to run, but it still took six seconds to import those items. So if we go back and look at those items now. You ready? All right. And we look now at this, we see we to look okay. So we look now we’ve got all of these items that we just brought in and they’re valid items on. And one of the things I should mention is, you know, I, I brought in that that unit of measure and everything else, I didn’t have to go in and set the, the sub table or anything like that.

You know, it’s, it’s come in and it’s set the, the appropriate unit of measure and it’s creating the, you know, the, the item units of measure, information and everything automatically. Right. So it was very simple. I imported like four fields and I got everything I need to make this a valid item that I can use, like, right now if I’m on. And of course, I didn’t bring in any history or everything. Zero account or history. But again, I want sales history for all these items that I just brought in. So what I’m going to do is I’m going to import that sales history, but we’re going to prep it first.

So I’m going to show you a few tricks for prepping that sales history. So the first thing is what I’m going to do is I’m going to bring up this, you know, this item history. So this is all my individual transactions for all of those different items by date. Right. So if I scroll down here, you can see, you know, over the 12 months of 2023, this is all my sales history and we happen to have, you know, about a million records on this file. Right? So 999,000 records in this file. This is what we’re going to import.

But I don’t want this level of detail. I want monthly, history. So I just want to, you know, consolidate this as a, a monthly history import. So I’m going to show you a couple of tricks to do that. Now normally what I would do is I would just, you know, bring this stuff into SQL server, run a quick query and it’ll dump out the format, the file, exactly the way you need it. But I’m going to do it in Excel just in case you don’t have access to SQL or whatever. So I’m opening up that that same data in Excel.

Now those million records so or takes, you know, Excel chugs along for a little bit here as it opens. We’re going to bring in those, those million records. All right. So here we go. We’ve got this open in Excel. Here’s my roughly million records, you know, based on individual dates. Now, what I did was I just came in and I created a formula here to show me, you know, the month. So this is grouping all of the individual dates, by month. And what I did was I just used the first of the month, and I’m going to set my history as of the first of the month for, for every month, just so I don’t have to worry about if there’s 29 days or 31 days in a month, everything just comes in as of the first of the month.

Now this is great. But again, I want to summarize by by month. I want to sum up all the sales for this particular item by month. So how do you do that? Well, what we do is use a pivot table. Again if you’re using this in SQL, it’s a quick query and it gives you everything you need. And you export that file and you’re good to go. But I’m going to do it in in Excel here. And I’m going to create a pivot table just because I’m going to show you some tricks in Excel, for, for doing this.

So here’s our pivot table. And what we’re going to do is of course item number and and quantity here. And there’s a, there’s our total sales for this particular item, but it’s not by month. So what we’re going to do is we’re going to drop this in and see it by month as well. So now I’ve got, you know, item number by month. And you know, maybe you also want to buy by location code just for fun. So here I’ve got it item number month location code. And what I’m going to do while I’m here is I’m just going to not subtotal that.

Now this is great. Except this I can’t export this into a flat text file the way I want to do it. Right like this is this is not that useful for me. It breaks out, you know, my sales by month for all this. And great, but it’s not useful. So here’s a few tricks you can use within Excel. So the first thing I can do is I’m going to go into pivot table options. And if I go to the display here, one of the options there’s this classic pivot table layout. So if I do that magically now what happens is I’ve got, you know, item number, date, location and quantity.

So now I’ve got this in a flat format that I can actually use. Right. This is the format that my file needs to import this data. But we’re missing you know, this stuff here. So here’s another little quick trick you can use. If I go in here and I do field settings, if you go to layout and print, I just repeat the item labels. And guess what? Now I’ve got pretty much the exact file I need to, you know, import in here. I just come in and select all this data and and I can import it.

And that’s what I’ve done. I’ve actually come in with the magic of copy and paste and a few little tricks because, you know, your sales history needs to be negative. What I did was I took, you know, the total. Like, if we look at this, I took the total. This is a key point. This needs to be my opening balance because this is my opening balance. And then this is going to, you know, draw down on that opening balance. So at the end of this import I’m going to be left with a quantity of zero and stock.

So again with the magic of copy and paste, what I did there was I, I grabbed that that total. That’s it here. And then if we scroll down a whole bunch we get to the transactions. So all the transactions now are negative. So this item this month, this is my sales quantity. And if I find that item in the next month it’s going to have a sales quantity and everything else. So this is the actual file I’m going to use. I’m going to basically copy and paste this into a text file or save this as a text file.

And I’m good to go. And so what that ends up looking like after I do that is this. So if you don’t know what table 32 is that’s my item ledger entry table. And I could have put an item ledger entry in here if I like as a as a description. So what I’ve got is the entry number is the item ledger entry number I started at, at 1000 because I’ve got existing item ledger entries. But you know, if this is a brand new system you’d started in a one and grow up. I just generated these in Excel.

Of course, I open this up in Excel and just kind of generate this, this, stuff down. And here you can see it’s going to bring in my opening balance of, you know, 48,000. And then all of these guys below it are going to draw down on that 48,000. And this will add up to zero at the end. And I added a couple of other things. Right. So I’ve got you know, my my quantity, I’ve got the the invoice quantity as well. So I should have opened this up in Excel. But I’ve got invoice quantity and quantity and of course the remaining quantity is going to be zero.

So let’s actually see what this looks like. Now I, I stripped this down that it resulted in about 6000 records in total. I cut it down a little just to increase the import speed. And what we’re going to do is just go to import data. We’re going to come in and we’re going to grab, the opening balance data for that. Again, what we’re doing here is we’re sucking this directly into the item was your increase. There’s no posting required, which means there’s no impact to the jail. There’s no value entries. There’s none of that stuff that that gets generated when we get that sale system.

Right. So now if I open up the items. Sector load here and we go in and look at our our item numbers that we imported previously, right. We’re still all zero. But if I open any of these guys up, we’ve now got, full sales history for those items in the system. Right. So in reverse order, because the default sort orders by an entry number. But here we’ve got, you know, our, our, opening balance 50,000 and then all our sales month by month until we get, you know, basically down to zero. So that is how you can import.

And this goes for anything. I mean, item ledger entries are pretty typical, but I went from a million records down to 6000 records. Takes like 30s to import. And now I’ve got my monthly sales history. And quite often people will also break this down by customer. Same process. I’ve got my monthly sales history by item and customer and location very easy to do, and the power tool makes it very simple. Right? To prep for it again in SQL server is easier, but if you want to use Excel, those are some tips and tricks. And so you can use to do that, that import, while I’m here just for fun because I said I would I’m going to, bring in we’ll just bring in the, the sales order stuff.

So what I did was, brought this in. Oh, yeah. What I want to do is I want to update because it’s going to try and, and do this. So what I’m going to do is I’m going to import those record lengths. And I forgot to change my settings, and it gave me a nice error saying, you can’t do that. So I’m going to update the records because I’m bringing in record links and I’m not going to, you know, run any, any triggers because we don’t need it. We’re just bringing in the record lengths. And so we’re going to bring in those sales order record links.

That’s going to come in import of those. And now if we look at, This guy here easier way, if you search the company data sometimes that’s a quicker way of doing it. Except I didn’t know the oh, I lied. All right, let’s go with the sales orders. I couldn’t remember the actual sales order number that I used. I think it was one. We’ll find that here soon as everything loads. And, All right, so there it is. There’s the links that I imported using that, that import. Right. So just these links that, that you saw here in the thing.

And again, I could have brought in notes and all that kind of stuff as well. All right. So that’s the import side. Thanks everyone. Hopefully we’ll see you on the next webinars. Talk to you later.

Questions about this webcast?

Send your question and the Insight Works team will reply by email. Ready to plan a project? Contact your Microsoft Partner.

Ask a question

Webcast Questions