Importing Data to QuickBooks Desktop and QuickBooks Online

In this article, will break down the tools available in QuickBooks Desktop and QuickBooks Online,

With QuickBooks Pro, Premier, Accountant, or Enterprise, you have the following built-in tools

• Pre-formatted Spreadsheet (Simple) Import to bring in new Customers, Vendors, Non-inventory items, and Inventory Items using a built-in spreadsheet that opens in excel and has 4-5 fields, it is limited, but very simple to use
  
• Advanced Import can bring Customers, Vendors, Employees, Accounts, Service Items, Non-Inventory Parts, Other Charge, and Inventory Items with a mapping tool so you may choose which column in your spreadsheet matches the data that can be loaded into QuickBooks
  
  I recommend you download Intuit’s template which will make this process very easy to learn how to do. The item has about 20 fields of data you can import and the names, such as customers have over 30 fields of importable data, yay!
  But, what is REALLY neat about this tool, is the ability to UPDATE a field/name for an item already in the list, consequently, I can also wipe the data on that particular field, or pick and choose what to update based on what is on the spreadsheet. So, I am my client’s PRICELIST UPDATER hero!
  
  I would also recommend downloading Intuit’s CSV/EXCEL Import kit, contains a few interesting examples.
• IIF – Intuit Interexchange Format, while most people choose to “stay away” from this type of import, it is a very versatile tool. But this tool is like Dr. Jekyll and Mr. Hyde… Let me explain, there is the “File, Utilities, Export, List as IIF” side of this
  
  Works very well for moving stuff from QuickBooks File A to QuickBooks File B… but it is not always this simple.
  And then there is the “Dark side” of IIF, which makes you feel like hacker when you open these things in excel
  You can download Intuit’s IIF Kit and open each sample IIF file in there within excel to see how these are structured, you will see the following templates: Bills, Bill Credits, Sales Receipts, Credit Memo, Deposit, Estimate, Invoice, Item Receipt, Journal Entry, Purchase Order, Payments, Statement Charges, Bank Transfer, and Write Check… not bad for for a free tool. Also, there is an awesome utility called the IIF Transaction Creator by Big Red Consulting that you can also use, not free but really effective if you choose to do IIF imprinting.
  The big CAVEAT, is that IIF has limited error checking or protection, use at your own risk… your chances of data corruption increase when using IIF’s specially modified by non-experts in the subject matter.
• And lastly, Add/Edit Multiple List Entries. The best thing about this tool is the ability to COPY/PASTE from Excel, so now you have a friendly and familiar interface for experienced Excel users. Warning, for updating lets say prices, on items, this tool is not as effective. This is a great tool for creating new items, customers, vendors, and even inventory assemblies.
  It is important to customize the columns to match your excel spreadsheet
  The great featurette behind this tool, is when updating item accounts, for example, you can choose to update existing transactions as well, retroactively… protected by closing date password, of course.
  

With QuickBooks Accountant Edition (and Enterprise Accountant), you get all of the above plus a relatively new tool introduced in 2013 named Batch Enter Transactions. Another game-changer from the makers of Add/Edit Multiple list entry…

Now we can finally copy/paste transaction data such as:

• Invoices and Credit Memos
• Deposits
• Credit Card Charges & Credits
• Bills and Vendor Credit Memos

This tool is getting revamped every year, there still some challenges such as importing items with multiple items or checks with splits in the accounts. But this is single handedly the most powerful write-up tool I have ever used. Not just for importing data, also for data entry is great because of the ability to customize columns which is not available in check registers.

Moving to QuickBooks Online…

Sadly, this is does not have as power and extensive options to bring data into QBO other then the very powerful bank feeds. In a nutshell there are 2 options:

1. Import Bank Data using .QBO files
   
2. Import transaction data using 3rd party tools such as Transaction Pro or Zed Axis
3. Importing List of customer or list of vendors

Under the New Customer and New Vendor buttons is the import feature, but very limited number of fields

I want to go back to the first bullet point about importing bank data using a .QBO file. Most people I know are only aware of the .QBO file that the bank allows you to download, but you can make ANY CSV or Excel file a .QBO file as long as you got the right converting utility; there are 2… one is called CSV2QBO and the other Excel to OFX Converter

This video was not recorded by me, but is a great resource for seeing how these built-in data import tools work with QuickBooks Desktop:

-Hector Garcia, CPA
hector@garciacpa.com