In this example, I will take you through the process of taking a SCANNED or PDF item list converted into Excel, then clean it up and ready to import, and finally imported into QuickBooks without doing any data entry! Also, check out my other article at the Intuit website on other shortcuts I take to reduce data entry.
First, if you already have the Item list in Excel, then you can skip to STEP 5… But, if you got the pricelist in a scanned document or PDF, there you must use a 3rd party program called PDF2XL to be able to convert the document to Excel first. PDF2XL has 3 versions: Basic (for Native PDF files) $199, OCR (For Scanned PDF files) $299 and Enterprise (Direct Scan to Excel) $399. I have done a few conversions since I got this program and It has saved me an estimated 20 hours of work already, so the investment is definitely worth it.
For this example, I will be converting the first page of Apple’s Education price list as if it is my cost for products I will resell, into excel:
So the first step is to open the PDF file into PDF2XL:
Then we are going to select the options Tables (Marking Mode) and Pages Different (Structure) which will give you the ability to create a table around the data you want to import for each page:
After that, you create a table by clicking and dragging inside the PDF document ONLY in the area where the relative text and numbers are…
You will notice the darker shade of orange to be the HEADER section which will create all the titles for the columns on row 1 of your spreadsheet. Then you will see dashed vertical lines that represent the separation of each column of data. Then you want to select the Conversion Format (Excel), Convert Pages: 1, and clock on CONVERT.
Within seconds, you will see a spreadsheet with all the data:
There may be some minor clean-up, for example I will delete extra rows that contained subheaders that I didn’t need….
And then cleaning up some extra columns not needed as well:
Then, I can do simple things like adding 20% markup to my cost to have my SALES PRICE in my spreadsheet ready for importing, like this:
And then finally, we will move to QuickBooks to import Copy/Paste this data to create the items, by going into the List Menu and Clicking on Add/Edit Multiple List Entries…
And then select INVENTORY PARTS under List and click on CUSTOMIZE COLUMNS to add or remove the desired columns to match the spreadsheet’s structure to have a perfect copy/paste (in this case we only have ITEM NAME, DESCRIPTION, COST, and SALES PRICE, however the last 3 columns in here (Income, COGS, and Asset Account) cannot be removed as they are required for an Inventory Item:
Finally, your screen will look like this:
Which is now ready for you to COPY the data in Excel and paste it into QuickBooks’s first Item Name box on the top left, after you paste it will look like this:
Before Clicking on Save Changes, you must make sure that all items have their Income Account, COGS Account and Inventory Asset Account.. you can you use the “Right-Click Copy Down” tool to fill all of them out with the same account:
After clicking Save Changes, you will get a confirmation that all the items have been imported, so it will be a great time to go into List, Item List and right click to Customize Columns so you can see the item list just the way you want it:
Conclusion, it took me about 20 minutes to do this process (including writing this article…) which I calculate would have taken me to manually enter item by well over one hour, the time saving exponentially grow as the item list get larger. But best of all, FLAWLESS (or near flawless I must disclaim) data entry with no errors!
You can go to http://bit.ly/PDF2XL to download a trial. You can also use COUPON CODE CPC38629 for 10% off!
Seth David from Schoolofbookkeeping.com made this great video explaining the process: