Top 5 things you need to know about Importing Data
Watch the recording by clicking on the play button
Thank you for joining our webinar. This is the 15th in the series of monthly webinars on key areas of NAV and Business Central. You can sign up for as many as you like on my website and I’ll show you a list of upcoming webinars at the end of the session. We also send out regular emails to register for the next one. My name is Karen. I’ll be facilitating the demo today. I’m an account manager here at TVision, and my background is 18 years of software account management experience working for ERP companies. Ian is going to be doing the demo today.
And he is a Support Consultant here at TVision. He’s been working here for about two and a half years and has been using NAV since 2000.
At TVision clients often ask us how easy it would be to import data from other systems into NAV or Business Central. What the best way of doing this would be? In order to answer this you firstly need to ask yourself these key questions. So what type of data do you have? Are you importing customers, transactions or payments? Why are you importing it? Are you updating information, adding new information or is it for reporting purposes? And how much data will you be importing? Is it a few lines? Tens? Hundreds or thousands?
And how regular? Do you need to just import this data daily, weekly, monthly, quarterly? Or is it just a one-off? And how automated do you want the import process to be? Do you want NAV or Business Central to automatically import this information or you happy to do a manual import each time? Perhaps you need somebody to approve the import as well. Once you’ve answered these questions then you’ll have a much better idea about which importing method is most suitable.
These options are: Copy and paste from Excel for a small amount of data where the data format is similar to NAV or Business Central. TVision GL Excel import for a larger amount of data perhaps where the format is similar, but some columns may be in a different order. Business Centrals import from Excel edit in Excel – if you’re on the latest version and you have a small amount of data. Rapid Start if you have a larger amount of data or if you need to import regularly.
And finally integration to another system. If you have a large amount of transactional data and/or you want to import daily. So I’ll now hand over to Ian for the demo. Thanks, Karen for today’s demo I am going to be using a standard NAV 2018. We’ll have a look at Business Central a little bit later on. Using this one because it’s probably what everyone’s most familiar with… and some of the questions Karen was saying you need to ask is the type of data that you’re importing.
So if we just go and have a look at some data on the system. Let’s just have a look at our vendors. I’ve got a list of vendors here. And this looks a little bit like an Excel spreadsheet. But if I click around none of these fields are editable. And even if I do try and change one of these fields, nothing happens. So immediately for uploading new vendor information or changing vendors.
Using an Excel import isn’t really going to be suitable. And if I do want to change one of these vendors, let’s go and edit vendor card, this again doesn’t look like an Excel spreadsheet. So again for vendor information, customer information, and such like, import from Excel isn’t the first place to start looking – we need different tools. On the other hand, if we go and look at something like a payment journal or a general journal, cash receipt journal etc…
Again, it looks like an Excel spreadsheet. I’ve got my columns and rows and all of these fields are editable. Immediately this is saying to me, this is something I can use Excel as my source. I’ve also got this Microsoft Excel button up here, which is giving me another clue that Excel is a good place to get this kind of information from if I don’t want to capture it by hand. Now, how would I do that? I can copy this and paste it into an Excel spreadsheet.
Populate my journal. Let’s say that I did that, and I’ve created a journal in Excel. I’m just going to open one up that I prepared earlier.
As you can see, I’ve got a journal here that I’ve pre-prepared and you will notice the column headings on my NAV match up with the column headings the same order and everything on my Excel spreadsheet. To get this information in, it’s as easy as highlighting the data I want to import, copying and pasting… and pretty quickly the system has imported what I copied from Excel so that Excel spreadsheet could have been my monthly accruals or my monthly staff payments Etc.
I can use that Excel spreadsheet. I just want to delete all of these lines.
And show you something…what happens if I change my column layout. Let’s add an area code and move that up.
Now my column layout on NAV has changed and it doesn’t match up with my Excel spreadsheet anymore.
There’s an extra column that’s not here. So if I copy.
And this time if I try and paste.
I just still getting errors. And that’s because now my columns have gone out of sync. So this solution of just simple copy and paste it’s perfect as long as your NAV screen is going to remain the same. If I want to share this Excel spreadsheet with somebody else, their NAV screen needs to be exactly the same as mine, which doesn’t always happen in the real world. So let’s just get rid of these errors.
Refresh, discard all that information. Delete this line. Now in a situation like that where you want to share this spreadsheet or perhaps you’re going to prepare your Excel over the course of a few days and you might change the way your NAV looks, then we have a custom-built tool. This can be added to your system – Import from Excel. And it works very nicely. If we click on it.
It tells me what Excel worksheets do I want to import and I go and choose one and the one I’m going to choose is called import demo.
It’s found data on sheet1. It’s found a row of headers. It’s going to put this into my general journal the one called demo – I’m working with. And I can choose a mapping code. You usually have a whole list of these set up for all your different types of journals as soon as you say, okay. It’s going to read my Excel spreadsheet and its founder columns, and it’s trying to match them to the columns on here.
These are the columns that found in my Excel spreadsheet and that’s the columns that it can map it up to in my journal. I can manually change these if I need to if I’m happy I say, okay.
Ask me if I want to save that for next time.
Tells me that didn’t find any errors with the import and it’s going to bring that information in. And it brought that information in regardless of the fact that I’ve added a new column here. So I can change that of my columns again, pass that Excel file onto one of my colleagues, they can use this import button and it will still work. So, so for you’ve got a straight copy and paste from Excel if everything stays the same.
If things are going to change, or if you’re going to share your Excel documents with colleagues. Then you’ve got the import from Excel button. Now. Another option is the edit in Excel. This has become available on Business Central. So let’s just have a look at that.
And if I go and search for a general journal, for instance. And we have a look at that, I will find somewhere on this page. There’s an edit in Excel button. If I click on this button, it’s going to open an Excel spreadsheet for me. I’ll be able to type within that Excel spreadsheet and it’s going to update the screen automatically without me having to say copy and paste.
We’ve already looked at General journals. So let’s have a look at something a little bit more interesting to demonstrate this tool. Let me get a list of customs.
And you remember upfront I said customers isn’t always a good option to try and use Excel for your imports. Again, this isn’t an editable screen. But because I have the edit in Excel button here – just notice there’s a very limited number of columns on my screen here. I’m going to say edit in Excel. It’s created an Excel workbook for me, which I’m going to open.
And enable editing. Now you’ll notice those columns that I had on my screen. It’s added a whole host of extra columns as well. These are all the things on a customer that you could possibly edit. And this new little window has popped up on the side and it says its refreshing data. What it’s doing its going and fetching information from my Business Central and it’s populated this workbook with that information.
If I look on here, there are columns that are was not seeing on my Business Central stream.
Something that happens quite often, — perhaps the salesperson BD has left the company or he’s moved on to a different department and we want to change all of his customers and give them to Annette. Their initials are AH, and this guy as well, and maybe we want to change somebody’s credit limit to 500,000. For instance. We make all the changes that we want, we can add new rows as well, if we wish. If we want new customers that we want to add. And when we finished we simply say publish information.
And now what it’s doing is its pushing this information back into NAV or back into Business Central for me and the published was successful. It didn’t find any errors. We can close that. I’ve still got my BD here, but if I go out of that screen and go back into it.
Customers. Just to force a refresh of the information…you can see it’s starting to change. It’s not instantaneous. It’s changed one by the time we go and look in drilling information for this customer.
We will see that the sales person not quite yet. It’s busy updating. There we go. It’s finished doing the update and if we went and looked in that tray research where we change the credit limit, we will see, hopefully, the credit limit of 500 thousand. Yep. It’s updated the credit limit. So there I use the edit in Excel button to edit the information that was already there.
As I said I could have added extra rows if I wanted to. If I had deleted rows, don’t worry won’t delete your customers. It will just not make any changes for those customers. So that’s another option that’s available but still very familiar and very easy to use. The next option that’s available is for when you’ve got some more information to bring in. It’s not just a simple Journal that you’re copying and pasting.
Perhaps you’re doing a real big bulk update of your items or your customers Etc.
And for that we have a tool or NAV and Business Central have tools called Rapid Start. So if we’re going to have a look at our Rapid Start, you get a host of configuration packages and you can set up separate packages so you can have one for updating customers, vendors, items, Banks, Chart of Accounts – anything that you need to update on a regular basis. Dimensions might be another one if you want to add new Dimensions.
You can go into any of these rapid start packages and see which tables they affect.
And this one, only affects table 18 – my customers. And at the moment is telling me I can only have 68 customers in my database and there are 90 fields for each customer.
I can from here hit the Excel button. I can export this information to excel, it will create an Excel spreadsheet for me. I created one a little earlier which I will open up.
So this is a list that it exported of all the customers on this particular system. I can go and I can update those customer names their addresses Etc. I can go and change who their salespeople are. So if I wanted to change all these first ones to PS, AH.
Just for fun, and we’ll change a few more …whoopsie. As soon as I’m happy, and it will do validation, if you make a spelling mistake your imports going to fail, as soon as I’m happy with all of this. I save the file with my changes.
I can close it.
And I can come back here and I can say import from Excel. Now, I could have added extra lines there as well if I had wished, and there’s a file that I saved.
And it’s going to do some validation on that information now to make sure that all of this information is valid. There’s no spelling mistakes. I haven’t used any codes and I shouldn’t have used. I can always ask it to show me if it finds any errors – and this one’s coming up blank. So it didn’t find any errors. If I’m happy with these 68 lines that I want to change the 68 that already exists.
I can simply say apply the data. And again, it’s going to do some more checking, make sure all the dimensions are valid Etc. And it’s going to apply this information to my NAV system. Usually takes a few seconds.
Pops up a message to say that it’s completed. There we go. Didn’t find any errors, didn’t insert any new records, but it did modify 68 records.
And if I then go and have a look at my customer list. I will see those changes that I made.
And choose one of those first ones which I changed. I think two PS.
And I can see the code has been changed to PS.
And the second one, I think I changed to AH, and if we have a look at that one. It’s changed to AH. So any changes that I made have been applied to my database, this this has been done in bulk. And this is very nice for bringing thousands and thousands of rows. The final option that was mentioned was integrating with external systems. And there’s a number of ways that you can integrate with external systems.
You can use CSV files or XML files. You can use web services. So within NAV and Business Central, we have web services and these publish web pages that other systems can use to communicate with NAV in either direction. So they can read from NAV, they can write to NAV depending on how this has been set up.
And we also have Job Queues that go with this. And within my job queues – I’ve got a sample one here.
Where I can simply say that I want to run code unit number 2161 and this could be a code unit to import an Excel file, export to CSV file, import and XML file, whatever it might be. And I can set up a schedule for this, tell it which days of the week we want it to run, at what time do I want to start running, do I wanted to go from one o’clock in the morning or start working at nine o’clock.
What time must it finish, and how often must it run – once every 60 Minutes, every 10 minutes … So you can have an external system that is providing information for NAV and on this set schedule run one of those code units, which is accessing a file or a web service, to import data into your system. And you will also see here if there are any problems with this web service.
So you’re expecting it to bring in orders every 10 minutes from an external order processing system. If there’s an error, it will be in red and you will be able to go to the log and the error entries to see what’s wrong. Why isn’t it coming through what’s wrong with that import file?
So those are the kind of five methods. So you got your basic copy and paste from Excel. You got the slightly more advanced GL Excel import which doesn’t worry about the field orders. You’ve got the built-in edit in Excel if you’re using Business Central, where you don’t need to copy and paste you can just edit within Excel as if you were in Business Central. There’s Rapid Start, which is really good for bringing in bulk master data changes.
And integration with third-party systems. So that’s what I needed to show you unless there’s any questions. Is there anything Karen?
Oh, what’s the maximum number of records that can be imported? There really is no no upper limit in reality to the number of records you can bring in. The biggest limit is your computer and how much memory it has.
So if you’re in Excel, you can bring through thousands of lines from an Excel spreadsheet quite comfortably, when you start into hundreds of thousands of lines. You probably find that your computer slows down and that’s what stops you bringing in more data. For Rapid Start, and for external integration, there really is no upper limit. Again, you’re limited by computer memory and how much bandwidth you have for transferring that information. So if you’ve got 200,000 lines you’re trying to import into Business Central, you know, it might take a few hours for all that data to upload into the system.
If you are trying to do that much information, it’s even more memory or more bandwidth is needed to make that work. Another question does the type of data effect which import method I can use? Yes, if you’re bringing through simple text and numbers you can bring that through from Excel.
It’s very good at handling fields of text and numbers if you want to bring through for instance, you’re updating your customers and you bring you through your company logos for those customers. That’s pictures – Excel doesn’t really hold pictures nicely. Then you really want to use an integration tool, a web service, or something to bring that in. So the type of data does affect which type of tool you need to use. That’s it for the questions. I will pass you back over to Karen. Okay. Thank you Ian. So I’d like to leave you with the 5 key takeaways about importing data, which we hope will assist you.
So first one is manual versus importing. Does the amount of imports set-up time justify the work needed compared to manually typing in in the information?
For example, if you just have a few new customers, it may be quicker and easier to just type information in. How often do you actually need to refresh the data? Is it imperative that the information is imported – to be imported should be done when it’s immediately available? Or can it be scheduled at a quiet time or to coincide with other information needs to be imported? Who’s going to check for the errors? Do you want the system to do this? If so, then the copy, paste or edit in Excel methods won’t work. You’ll need to consider the other methods. And remember, although you are setting up the quick way to import data, it’s still worth checking beforehand. Otherwise incorrect data won’t be flagged. So special characters. They work in Excel, but they do not work in NAV or Business Central – remember to amend these before doing the import.
And finally and very importantly, who’s going to maintain these import templates? Like all data management, somebody does need to own the responsibility of keeping these templates updated. Especially if new data needs to be added.
Okay, we hope you found this webinar interesting and informative. As mentioned previously, this is the 15th in the series of webinars, and we should be sending out emails soon to register for the next one. And we’ll also put a reminder about the webinar in our newsletter as well.
Okay. Thank you for attending this webinar. If you want to download previous webinars, please go to the webinars and events section under resources on our website. And if you do have a question, feel free to email it to me firstname.lastname@example.org. Thank you.