How to Export and Import Line Item Data Using Excel - (2024)

See more videos and subscribe to the Proposal Kit YouTube channel

In this video we're going to show how the line item database system works using Excel to import and export data. Now there's a couple different places this is used in the Preferences and in the Projects so we'll show the Preferences first. Just go into Preferences then Data and Display, click Line Item Quote Database.

We're going to start with just the Basic Product Sales database. This will work with any database. If you look down here you have Export Definition.

What this is going to do is whatever your currently selected database is when we export you can export it to a few different places. We can actually export into the MyProjectFiles folder. Any files we export into this folder get copied into every single project folder for every project you create so you'd only do this if you're going to be setting up a very custom specialized Excel spreadsheet that you want to make use of within all of your projects.

Maybe you are going to be filling it with some predefined data that every project is going to want to use. You can just export it to the current project. Whatever your current project is you can export into there.

Now there's an another export option we'll show next in the project screens that'll export into this as well or you can just export into a temporary folder. We're just going to export into a temporary folder. We can specify the number of dummy rows.

If we skip that it's just going to export a blank template with a single row. You'll notice here every sheet is going to be for one category so say if we had two, three, four categories in our database definition there'll be one sheet for every category and you'll have one sheet for all the global variables and an instruction sheet. The instruction sheet will give you some more details on how to use the spreadsheet.

Okay, so for each category you'll have a column for each field and you'll see your labels and codes and then every row from three after is going to be a data entry line. Now anything in orange is a calculation. You'll see if you click here you'll see that these are all calculated values.

So when the Wizard exports your database definition it actually recreates all your calculations so you can use these Excel spreadsheets as a double check as well. Now when you reimport these Excel spreadsheets in the basic version and by default the calculation columns and fields just get ignored only the data fields get reimported. Now there is an option in the expert edition where you can cause the Wizard to import all the calculated columns back into your Wizard's version of the database as static data values.

That would be a very special case use where say you had need for custom calculations that you could only do in Excel that the Wizard couldn't handle for example. But for the most part you're just going to be filling in data entry values like anything in gray. And purple, that just denotes a hidden field from view in the output versus a visible field.

You can see if you read through the instructions it kind of explains that here, cells shaded and purple or fields hidden from view it's still a data entry field. All right, now if we add if we start typing in multiple data entry lines you can recreate the calculations just to see how things are going to be calculated. We can just copy and paste cells for example and it recreates the appropriate calculations because Excel knows to just increase these values as it pastes in.

Now this doesn't always work however. There are cases where calculations are complex enough where Excel can't figure out how to auto correct the calculations and you won't get the correct values as expected. That only occurs so far in say some of the schedule database definitions where you're doing running calculations of start and end dates and so on.

So the way to get around that is let's just reexport this again but say we know we're going to do 10, 20, whatever data entry lines. We can just say export 10 rows and The Wizard will create that many dummy line items and all the correct calculations. Now we can just go through and just retype in over the top of these our actual line items.

So as we go through and retype over any of these with actual data and say it's like we only need seven. Well we can just go and delete those and then doesn't matter if the colors are still there the Wizard only reimport any rows with actual data in them. And we can see the summary calculations have been recalculated here.

I'm just going to save this and I'm going to keep these dummy data rows just to illustrate later but that's how you export using the Preferences side of things. Now this can come in handy too where we want to create pre–made line items. So actually I'll show that real quick here as well.

I will just type over some of this data. All right, so let's just delete that. All right, so here are our dummy line items.

Now this can come in handy when we want to create pre–made line items so let's just go into Edit Definition then Line Item Data. There's another video on creating pre–made or preload line items. This is good use for the Excel spreadsheet export import from the Preferences.

So pre–made line items those are going to be used when you want to create a pick list of items for use in your project so the user doesn't have to retype all the data fields. I can click Import Data and we have options here so it kind of knows to go check a few places for a matching spreadsheet and it found one in the temporary folder because remember we just exported into that temporary folder. I'm just going to say Import from Excel.

It loaded three rows. Close that and you can see our blue, red, yellow widgets and all the data values. Now we have a set of pre–made line items for our database definition that can be used in any project.

All right, so we'll close that. Now let's go show importing and exporting from the project screens. We go create a new project and we're going to click the Basic Product Sales.

Now you'll see the Import and Export buttons here but real quick we'll go into Manage Data. There's no line items, Manage Line Data, add a new line and you'll see our pre–made items that we just imported in the Preferences. So that's a quick way to set up a bunch of pre–made items.

Now you don't have to manually retype all of the data fields, you just select something from your pre–made row, maybe update the quantity, call it good. Okay, so from the project screen now we have the Import Data. Now there's nothing here to import from yet so we have the option of importing from another project that's got the same database definition.

We don't have any other projects we've created yet so there's nothing here. You can see Import Excel is blank because it's actually looking for an Excel spreadsheet in the current project folder and there isn't one. So how do we do that.

Well, let's just go export and same as before we can export X number of dummy rows. Now if you had actual line items already created in the database it's not going to ask for how many dummy rows it's just going to export your full database that you've already built up but since there's no line items it's going to create an empty spreadsheet. So again, I'll just do three rows and just as before it's the exact same spreadsheet as from the Preferences.

All right, we've got three line items added it's recalculated everything for us. Let's set the tax rate say 6%, shipping might be $40, add a $10 discount and total amount as calculated by Excel is $1553 and 11 cents. So we'll save that.

Now we want to import. In the expert edition here's that check box I mentioned earlier where we can override the Wizard calculations with Excels. So this if we check that, that's going to cause the Wizard to treat all of our calculated values as static and it will just import the actual calculations from Excel.

But we're just going to stay with the default. We're going to let the Wizard do our calculations so we're only going to import those data entry fields, not the calculated fields. All right, so that imported okay.

Go to Manage Data and you can see yellow, red, orange widgets and what we entered into the data entry fields. The Wizard has now done these calculations. View our globals and $1553 and 11 cents.

So the Wizards calculations match Excels. So that is really about all there is to using Excel to export and import data for your line item database projects. It is important that the database definitions match so if you're starting to do importing and exporting using Excel but then you're also going and making database changes to the definition by say adding your new fields, you'll have to recreate or update your spreadsheets if you've changed the structure so at least the fields all match up.

We'll just do a quick little Test and Preview. All right, so here's what the invoice created. You see our line items, all the calculations.

Invoice looks good. Cost summary, that all looks good. So now we could actually build a full document and I'll add the cover letter, front and back covers, maybe an introduction and we'll build the full document.

All right, the document's been generated. Let's open that up take a look at it and invoice still looks good, you can see it's got the design theme added to it. Our full document starts with our cover letter, front cover, introduction, cost summary.

Looks good and back page. So, that is the basics of using the import export features for the line item databases.