Skip to content

On demand · Product video

Create sample sales orders in Excel and import them with Import Export PowerTool

For consultants and partners who want a large set of realistic test or demo orders in Business Central without entering them by hand.

Recorded July 24, 2023 · 21 minutes

Import Export PowerTool sample data excel video thumbnail

Summary

This video uses Excel and Import Export PowerTool to build 500 sample sales orders with about 2,500 random lines. This is a completely free app from Insight Works for Business Central. Customer and item lists are sent from Business Central to Excel, then the INDIRECT and RANDBETWEEN functions pick a random customer for each sales header and a random order, item, and quantity for each sales line, with a star on fields such as the customer number and item number so they are validated on import. The data is copied into a text editor instead of saving the sheet as a text file, which keeps the formulas for reuse, and is imported with record inserts and two step insert turned on. The video finishes by opening the new sales orders and loading them in Order Fulfillment Worksheet as a test data set.

Key topics

  • Send customer and item lists from Business Central to Excel
  • Generate random sales headers and lines with INDIRECT and RANDBETWEEN
  • Mark fields with a star so they are validated on import
  • Import the file with record inserts and two step insert
Read the transcript

It’s all right in this video. What I’m going to do is show you how you can use the import export power tool to generate, you know, sample data for testing or demos using Excel. So as much as this is going to be a, you know, sort of a demo on how to use import export per tool, it’s also going to show you a little bit of how you can actually use Excel to generate, you know, fairly large datasets fairly easily. So what I’m going to do is in this example, I’m going to generate a bunch of sales orders that I can use with the order fulfillment worksheet just to test it out and demo and everything else.

So the way we do that is I you know, I’m going to create a bunch of sales orders for different customers. So the first thing I need obviously is, is my list of customers that I’m going to generate sales orders for. So the first thing I’m doing here is I’m going to go in and I’m just going to send my list of customers out to Excel. Right. So outgoing, my customers, my customer list into Excel here. And we’re good. And I’ll just, you know, for now will enable editing and we’re good there. And then I’m also going to want my list of items that I can sell to those customers in Excel because I’m going to create a bunch of sales for these items for those customers.

So same thing here. I’m just going to send that out to Excel. So I have my list of customers in Excel or somewhere, items in Excel as well. So here we’ve got, you know, my items and will enable editing, and I’m just going to combine these two things together. So we’ll just copy that over and we’ll put that in the same thing as the customers. So here’s my items and customers in the same workbook and and we’re good to go as far as that goes. So the next thing I want to do is I want to come in here and start creating the data I need for the import export, or I’ll just call this sales orders, need to give it a name, but just to keep it simple, I will.

And now what we’re going to do is generate the structure we need for import export per tool. And this I going to use table 36 where the actual name of the table here, we’re going to come in here and spell it right. Of course we’re going to come in and give it the table idea that we’re going to be importing. And as far as the data that we’re importing into our sales header, we need the document type in there and we need the document number, which in itself is just called number. And oh, and then we also need the sell to customer number and maybe the shipment date as well, just for fun.

And you know what? I’m also going to do location code here. So the setter set the specific location code we want. So basically, those are the fields that I need to import to generate a sales were and one of the little thing on here, what I’m going to do is I’m going to put a star in front of the sell to customer name because what that does is that validates that field. In other words, when I import this, it’s going to validate the sell to customer as if I had typed that into the user interface.

And that of course will fill in the address and the posting groups and everything else we need. So that’s basically it. That’s all I need here. And the document type here is going to be order and the document number. We’re going to keep it pretty simple. We’re just going to go it’s going to be so one, one, two, three, four, five, whatever. Doesn’t matter. That’s going to be our sales order number, our first sales order number. And I’m going to generate like 500 of these guys. So the next step, just a quick sort of tip in Excel Control G And I can say I want to go to sell a 500 and actually that’s make it eight, five or three.

I’ll put an X there. And the reason I do that is because now instead of holding down the mouse and dragging down 500 rows, that’s a very quick way of saying this is the end of my table and then I just do control up. Arrow to jump back up here. Copy that. Order, shift control down, arrow paste. And I’ve got my my 500 rows defined for my order, my document type. Then I just come over here and I double click on that little square at the bottom of the cell. And what it does is it fills in, you know, my my sales order number, right?

So there’s my you know, sales were one, two, five or one of my 500 sales orders and good to go And then shipment date. Well let’s do I’ll do these after in a sec. Now the sell to customer number here is interesting because what I want to do here is I actually want to get the customers out of this list, but I don’t want to, you know, specifying each individual. One of these sells over here which customer to use. I want to to grab one of those customers for me automatically. So in here I’ve got, you know, row two, two rows 69.

As far as customers in the system, I want to randomly grab one of those customers to use on the salesman. So how do I do that? Well, there’s a function in Excel called indirect. And so what I’m going to do is I’m going to type in entire oops, indirect tab there. I’m on indirect. And here we’re going to specify, you know, the cell that we’re after. And what I’m going to do is I’m going to say customers. And then, you know, a and let’s just do a two for now. I’ll show you this, this thing here.

So what that did was it went out to that reference, that cell reference E two and I grabbed the value out of that. So great. Now what I need to do though, is, is choose a random cell to get that information from. So all I do here is I get rid of a two and I do an ampersand to concatenate the strings and I say in around between. So this is a random number between like it says a top and a bottom number. There I’m going to say between two and I think it was 67, wasn’t it.

67. So it’s going to go a and then 2 to 67 and it’s going to grab a random cell in there and bring that in. And I just want to double check it’s actually 69. So let’s come back in here. Doesn’t really matter, but you make it 60. No. So that’s basically it. Now I’ve got that random customer and if I again click on this, it’s going to fill that in and it’s going to grab a random customer for every one of these orders. Now, I could get duplicate customers and things like that in there.

That’s fine. It’s a random sampling of those customers for those 500 orders. And statistically, I would expect each customer to roughly be, you know, four or five orders or something like that based on the numbers. So that’s basically it. Now the shipping date, I could just specify a shipment date or I could also use that, that random function to to specify, you know, a random shipment date. In this case, you know what, just for fun, I’m going to do, you know, five, one, two, three. Getting my business central data entry. Well, it’s just two, two, three, two, three.

All right, So I’ve got that and same thing. I’m just going to click that and I could you know, I could actually leave it like this where it’s incrementing, but I don’t really want it to do that. I want to, you know, just use that that same date. So instead of fill series, we’re just going to do copy sells and everything’s going to have that same shipment date. But I could use that random function to generate a roughly random date in here as well. And then the location for what I’m going to use is going to be main and we’re just going to double click that info.

And you’ll notice every time I change a cell, it’s recalculating this formula, right? Because that’s what Excel does. It read, generates this. So every time I, you know, I come in here and make a change, it calculates that the formula that can get painful if you’re dealing with a ton of records like I recently did, an import of 1 million item ledger entries using it for import export per tool. And that’s a long calculation. If you’ve got a lot of this random stuff. So just as an aside, you can go in the formulas here and you can set the calculation options to manual.

And then now if I make changes, nothing is going to nothing’s going to happen, right? So I can make as many changes as I like. And it’s not going to recalculate to recalculate after it if nine So each time you def nine, it calculates and we’re good. Now if you do that, that’s global. That’ll apply to every worksheet. So you’ll want to, you know, kind of ensure you don’t forget to set that back to automatic, which is what you’re used to. All right. Anyway, these are my my sales order headers. That that’s it. I built these sales order headers.

Now I need some sales order lines. So what I’m going to do is I’m going to go in and do my table I.D. And we’ll just do 37. Just to illustrate, again, we can either use the table name or we can use the table. Number 37 happens to be the sales line here and here. Same thing. We need the primary key. So we’re going to do document type and document number is in there. And we’re also going to need to import the line number and specify the line number that we’re bringing in. And then we’re going to specify the type of the line.

So item or jail account or whatever, In our case, it’s always going to be item. And then we’re also going to specify the the item number and the quantity. All right. Now, just like in the thing above, I’m going to set up on this a star. And what that star does again, is it’ll validate this field. And if I typed it into the system manually and when I do that, it’s going to obviously bring in the description and the price and everything else is if you type that thing in manually and the location code based on the sales header and everything else.

So I don’t need the location code and all that kind of stuff in here. I just need that and I’m going to fill in in the quantity or and I also want to validate the quantity because that’s going to set quantity based quantity to up and all that kind of fun stuff. All right. So same thing here. I’m going to do order and the document number, I can do a lot of different things here to choose a document number. But what I’m going to do is I’m going to choose a random sales order to add this product to.

So to do that, I’m just going to do the grand between again and it’s going to be between one and 501 are my sales order numbers, right? So that’s going to be sales order 23. Obviously that’s not going to work because that doesn’t match what’s above that. So to fix up what I do is exactly what we saw before. We’re going to do. Well, it’s actually it’s a bunch of ways we could do this. Let me think about the best way I’m going to just do an indirect again to just just because what I would typically do is is just, you know, build up the string so 1000 and then that last little bit of text.

But we’ve got to do some padding because those extra zeros and stuff. So anyway, all I’ll do is I’ll just do the, the indirect again. Right. So indirect and we’re going to do, it’s going to be B and, and then we’re going to go actually from, from row three to rule 503 and that’s oops. And I forgot the extra parentheses at the end, but it’s so nicely filled out. And for me and there’s my random sales order in them and then the line number, I’m just going to go up by tens. Usually that’s by 10,000.

In Business Central, we’re just going to go up attendance because we don’t need that. And then it’s going to be item. And then here again we’re going to choose a random item number and we’re going to choose our random item number from this list. So here we’ve got 687 items in the system, partially because I went in and created 500 items using this similar technique earlier. But in here I’m going to come in here and I really forgot what the count was. It was 687. Okay, So I’m going to come in here and I’m going to do that same indirect function and I’m going to do items and it’s going to be a and then the random between two and six eight is seven.

And that’s going to get me my random item number and then quantity. You’ll never guess what we’re going to do. We’re going to do the random and I’m going to say between one and ten, let’s say it’s going to be a quantity for this, this one. And you’ll see here, obviously every time I make a change, it’s recalculating. And if I decide to do, you know, 10,000 lines, I recalculating may take some time, which again, is why you might want to turn on that that manual calculation. But same thing here. I’m going to want a bunch of sales order lines.

So I’m going to do the same thing I’m going to do, let’s say 2500 sales or lines roughly. So I’m going to go in here Control G, and I’m going to go out to a 3000. Let’s see if I want it to be specific, 3000. So, all right. And I’m going to put an X there again or whatever character you want, just to sort of as a placeholder control C control shift or control shift down arrow And we’ve got our order there. Then same thing here. I just double click this guy. It fills in with my random order number.

We’ve got this guy here is a little interesting because what I’ll do is I’ll just come in. I could actually do it, which is ten and 20. But if I do it like this, I’m now telling Excel I want to use this increment when I show the series. So when I do that, it’s going to go up by tens from from then on. Okay, so there’s my line numbers and we don’t really care if this is this line. 25,000 happens to be the first line on the sales order of who cares, right? Like it’s just a line number.

We don’t really care if it’s in sequence or not. And then same thing. We just dropped out down. We dropped down our item number and we drop down our quantity. That’s it. I’ve now got all the data I need, so I’ve got a whole bunch. I got 500 sales orders here selling to random customers, right? I’ve got, you know, basically 2500 lines of of sales lines that are associated with, you know, those sales orders. So again, it will have potentially some sales orders with no lines on them or we’ll have one sales order written and another sales order with three or whatever.

You know, statistically I’ve got 2500 lines here and 500 sales orders, so we’ll get roughly an average of five lines for sales order. But, you know, that’s basically what we’re what we’re going to get. So that that’s it. That’s how I generate this. The Steve Now to get this into business central, what I do is I come in here and I just select all this text. It controls C and I’m going to open up a text editor and I would suggest to use Notepad plus plus the text part, paste it. And there’s my data that I can now import into business section using the import export part.

So let me hit control s and I’m going to put this in. Let’s do large sales orders. Okay, So there’s my data that I’m going to import. I just go into business Central here and we’re going to find my import export power tool like that. AM Power. It’s like I am powerful of this tool. Anyway, we’re going to come in, open that up, and what we’re going to do is we’re going to do our import and what I’m going to do here is I’m going to allow record inserts and the two step insert, because for some of these, like the sales line, we’re going to import, you know, the primary keys.

First, insert our record and then we’re going to go and validate that number field and everything else. And that’s what this setting is here. We’re only validating the fields to have that star in front of them. So again, in our data, if we look in here, that’s going to be the sell to customer number and then also the number of field on the sales line. I’m going to run the on insert trigger. I don’t actually think I need this. This will slow things down a little bit and probably don’t need it, but I’ll run it anyway.

The rest of the stuff we can leave as it is. So when I actually go in and do the import data, you know, we just browse to it here and we’ll grab large sales orders and give it a sec to upload that file. So right now it’s uploading to my this is my business Central Cloud sandbox, by the way. Now it’s starting to insert and it is a little slow likely because I’ve got that run on insert trigger but whatever, it’s fine. So there’s my 500 sales orders now it’s going to do my sales lines and it’s taking a long time, which makes me think I have a mistake.

Not there it goes. So now it’s importing all my sales lines for those sales orders and we’re going to sit here and wait probably for, you know, 30 seconds for this to happen. So I should find something else to talk about. But we’ll go in and let this let us finish. Importing and what you would do now while. Well, that’s important. Well, I just let it run. One note on this. I might want to reuse this so when I when I copied and pasted that, the reason I did that instead of saving this is a text file because that’s the other option.

I could come in here and I could save this as a, as a text file. Right. But the problem with that is when I say that as a text file, what that does is it kills all my formulas so I can save this and then I just copy and paste that data rather than saving the sheet as a text file. Because then I, you know, if I save it as a text file to lose all my all my formulas. All right, So let’s go back here. It’s busy going. We’ve got another few hundred records to go.

I sort of left that front on insert trigger off because it would have been done by now. But we’re going through and we’ll finish it up. All right, So we’re done. It took us just under 2 minutes to import all those records. And again, ideally, probably if I’d left, the software would have been, you know, half that time or less. But that’s basically it. So now why did that get us? Well, let’s actually go in and we’ll go on to my sales orders here and we’ll see what we’ve got. So let’s just filter actually on the the numbers that I used.

So I’ll just go in, create a filter, and we’ll do the number of field. And it was so, you know, one student at that there’s all the sales orders that I created or for my main location and we can scroll down and we can see we’ve got all the data that’s open up one of those sales orders here and we’ll see what it shows us. So there it is. You know, all my data brought in, all the customer information, everything, because again, we validated that customer number on on the import. So it filled in all the all the data we needed.

Hey, this is an interesting one because it’s it’s actually got no, no items on it, which as I mentioned, that could easily happen. Right. We could have that situation where it it creates a sales order with no with no data on it. And that’s actually fairly realistic because we might have in a in a real environment, sell orders with no lines on an abandoned sales orders and things like that. Here’s and here’s the next one that actually has a bunch of those items. And this is, you know, kind of a typical case where it’s got five items on it, but we can keep going there.

And by the way, that control down Arrow will move you through records instead of having a having to click this guy over here. You can see here as we’re going through, these are all the sales orders that it’s generated and and they’re all valid sales orders, you know, assuming all the posts and groups and everything are correct on on a particular customer, I could come in here and I could start posting these orders. And here you can actually see it’s brought in, you know, the discounts and everything based on the logic that’s within business Central.

So let’s actually just see if if this one will post and again, it’s going to be dependent on the customer data and everything else. But I can come in and it’s a real sales order that you can do all of this kind of stuff with. And this one’s yelling at me because, yeah, there’s some data not set up for some of these things, but you get the general idea. That’s how we can import it. And my, my purpose of this, as I mentioned, was to actually go in and use all those orders as a test base for the order fulfillment worksheet or as a demo tool.

So I’m coming in and now this is a different location that has some other orders in it. So it’s just loading up, you know, a few thousand orders from from this location and processing them. But the ones I loaded up were in this main location. And if I go in to Main, you were performing worksheet. Now it’s going to show us, you know, the 500 orders that we brought in. And this is actually a very good data set because I’ve got some orders that we can ship, some that we can’t ship and those sorts of things.

And this is an easy way to build up that demo data for your environments or just for testing and those sorts of things. So

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