Posted on

Importing into QuickBooks 1

Importing into QuickBooks

 

Nowadays the need for interfacing and integrating data from other sources than just manual input is desperately needed. Although QuickBooks has for years has had their IIF (Intuit Interchange format) import facility from their File menu if has proven to not be user friendly nor support the needs of today’s demanding business requirements (such as eCommerce and integrating with other systems).  QuickBooks IIF importing require Admin/single user mode, add items, accounts, etc. without asking or telling you (no logging), can corrupt your QuickBooks database, the format is not easy to understand, etc. etc.).  Also note that QuickBooks Online™  does not have any import features (no IIF importing at all).

 

QuickBooks desktop versions also have a excel import capability but for lists only, i.e. Customers, Items, etc. not for transactions (the only way that QuickBooks desktop can import transactions without a 3rd party solution  is with the IIF file import from their File menu).

 

What is an IIF file? The actually format is beyond the scope of this document but is described somewhat in the QuickBooks desktop versions under menu Help, Reference Guide to Importing.

  1. Has the IIF file extension.
  2. It is usually a tab delimited text file but can be a csv (comma separated values) file.
  3. There are headers beginning with ! (exclamation point) that define the headers (i.e. !CUST, !TRNS, !SPL).
  4. Each column for that header row function is for the same thing (i.e. name or amount).
  5. Can usually be viewed with Excel or any spreadsheet program.

 

How to do an IIF file import into QuickBooks.

  1. Ask all your QuickBooks users to logoff (except Admin if logged in).
  2. Log into QuickBooks as the Admin user.
  3. Switch to single user mode from the QuickBooks File menu, Switch to Single-user mode.
  4. Use the menu File, Utilities, Import, IIF files…
  5. Browse to your IIF file.
  6. Click on Open.

 

To this end we at AaaTeX have been years been providing import solutions. (see http://www.aaatex.com/QuickBooks_Products.htm).

 

Why pay for and use a 3rd party tool when there is a free IIF tool build into QuickBooks.

  1. To support the format that your input is in without any type of conversion.
  2. To solve your business needs for special formats, functions, etc.
  3. Not to disrupt you business work flow by requiring other users to log out of QuickBooks so you can do an IIF import
  4. Security – not requiring someone with the QuickBooks Admin user from having to login to QuickBooks every time an import is needed.

 

Our solutions include:

  1. The IIFImporter which is used with those legacy IIF files and systems that produce IIF files
  2. The TransImporter which is used with Excel types of files (xls, xlsx, csv, tab delimited text, etc.).
  3. The Shopping Cart Integrator which was designed just for eCommerce integrations.

 

Not sure which is best for you. See http://www.aaatex.com/QuickBooks_Import_Assistance.htm or ask us.

 

Besides these solutions we have written hundreds of programs for invidual companies to solve their business needs.

Posted on

Importing into QuickBooks 2

Importing Lists into QuickBooks Desktop Versions

(Learn and Working Smarter not Harder)

By AaaTeX

 

QuickBooks has a new Add/Edit Multiple List Entries feature to allow easy editing and importing of list entries.

 

This feature is available from the QuickBooks menu List, Add/Edit Multiple List Entries and does not require admin or single user mode.

 

It supports customers, vendors and some items (service, inventory, non-inventory only).  It does not allow importing of any other types (i.e. Payment Methods,  Item Assemblies, etc.).

 

You will get a matrix or grid that resembles a spreadsheet.  You will have to put your spreadsheet in the same column order and just for that type (i.e. customer, service item or vendor).  Multiple types cannot be combined.

 

Once you put your spreadsheet in the same column order as the QuickBooks you can just copy and paste the data rows (no headers rows) into the QuickBooks grid and make edits very similar to using a spreadsheet program like Microsoft Office Excel.

Errors are highlighted in red and clicking on them will display the error. Click on Save Changes and the changes/adds will be go into QuickBooks.

 

It should be noted that although this is a nice feature it is limited to the types it can handle and still does require human intervention to put everything in the correct column order, remove numeric formatting of $ signs must copy and paste, fix errors.

 

So limitations are:

  1. Only some types are supported
  2. Human intervention and delays
  3. Must put in same column order
  4. Most remove formatting for numeric fields as dollar signs.
  5. No logging of changes.

 

Another way to work smarter is to only create the list entries when you need them and from the source (i.e. The transaction that needs to be entered as an invoice or sales receipt). Although this does not apply in all cases think of the need for interfacing with another system. This might be an EDI system, eCommerce or just another application that creates a file.  Why not just import the data and have the list created automatically?  The build in QuickBooks IIF import does that but without a log and always does it even if you don’t want to.  (This was covered in a previous article).  Our solutions include the option to dynamically create the entries from transactions data. A log of all changes is created.  You have control.

 (see http://www.aaatex.com/QuickBooks_Products.htm)

 

For IIF files use or IIFImporter without the QuickBooks IIF Importing limitations.

http://aaatex.com/QuickBooks_IIF_Importer.htm

 

For Excel files our TransImporter

http://aaatex.com/QuickBooks_Transaction_Importer_Excel_2011.htm

 

For eCommerce and other database interfaces our Integrator

http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

Posted on

Importing into QuickBooks Online (QBO)

Importing Lists into QuickBooks Online

(Working Smarter not Harder)

By AaaTeX

 

QuickBooks Online can now import some list from other sources source such as email programs and e.  (Would you believe that is a new feature after several years of being in the market).

 

This article will talk about importing from Excel.  Most people would probably now want to import their email lists but would need to import from other systems or programs that can create excel types of files.

 

First there are restrictions:

  1. Only Customer and Vendors (no items, methods, nor transactions)
  2. XLS or csv only (no tab delimited no IIF files but XLSX files work)
  3. 1,000 rows or less
  4. 2 MB or less
  5. Must be Sheet 1
  6. Must have a header row
  7. Cannot contain formulas
  8. Cannot contain errors (i.e. #REF, #N/A, etc.)
  9. Contact names cannot contain the colon or quotation mark characters.
  10. Only certain fields are supported

 

You should check with the QuickBooks Online Help for a full list of restrictions and limitations.

 

Here are the fields that can be imported:

Name,Company,Email,Phone,Mobile,Fax,Pager,Website,Street,City,State,Zip,Country

Not many.

 

So how do you import a customer of vendor list.  It is relatively easy:

  1. Find the Import button and click on it.
  2. Click on the Choose File
  3. Assuming your headings are the same as the QuickBooks Online fields click on Import nn Records.  If they are different you will have to use the column drop downloads to map your column to the QuickBooks Online field.
  4. That’s it.  The import should have been done.

 

But what if your file is not in the format that QuickBooks Online wants? 

Where then you have to reformat your file and try it.  This is not the Work Smarter method that we have been talking about and will take more time and maybe cause more problems.

 

If you have a tab delimited text file you can save as a csv or xls file but watch out for Excel reformatting of numbers like zip codes leaving out leading zeroes or dates.  That is a whole other topic that we could spend an article on.

 

What if you also want to import items?  Well not without a solutions like ours.

What if there are a mixed of customers and invoices? Well not without a solutions like ours.

 

To Work Smarter and Faster look at our solutions. No reformatting or files usually needed and we can customize it to solve your business needs. (Try to get Intuit to customize their import for you).

 

Our TransImporter2011 (http://aaatex.com/QuickBooks_Transaction_Importer_Excel_2011.htm)

does not have all these limitations.  Columns can be mapped to the field and header rows are optional.  It can import to any sheet. Can give it a Sheet Name or number.  The TransImporter2011 supports just about all list types AND transactions.

 

If you do have an IIF file from another system or program you can use our IIFImporter (http://aaatex.com/QuickBooks_IIF_Importer.htmwithout changing the file.

 

For Excel files (xls,xlsx, csv, tab delimited) our TransImporter

http://aaatex.com/QuickBooks_Transaction_Importer_Excel_2011.htm

 

For eCommerce and other database interfaces our ShoppingCartIntegrator (the Integrator)

http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

Posted on

Importing into QuickBooks from Web Stores

Importing into QuickBooks from your Web Store

(Learn and Work Smarter not Harder)
July 24, 2011

 

One of the most frequent uses of Importing in QuickBooks is to get your web orders into QuickBooks.

 

There may be many reasons why you want to do this depending on what your web store software (shopping cart, eCommerce or CRM) does and how you use QuickBooks (accounting only, fulfillment or order management).  Some systems call this syncing or synching others integrating, exporting, download, etc.

 

Many eCommerce systems and shopping cart software companies say that they integrate with QuickBooks but how? Here are the questions you should ask:

  1. Does they just provide a file (i.e. IIF File, or csv file)?  And then you have to figure out how to import it into QuickBooks.
  2. Do you have to request the orders by date or order number and you have to remember what to enter?
  3. Is it automatic and seamless or do you have keep entering information?
  4. Is it flexible enough to meet your business needs?

 

Did you know what some eCommerce companies make you agree that they are not responsible if the IIF file they create damages your QuickBooks file? (The built in QuickBooks IIF import has some issues).

 

Well our Integrator products solve these issues. 

See http://www.aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm. It was designed for eCommerce. We have one for QuickBooks and another for QuickBooks Point of Sale. With our solutions you can seamlessly get your orders from our support list of shopping carts and eCommerce systems including many custom carts.

 

Well ‘how does it work?’ your might ask and usually our reply is ‘Very well!’J

 

But the techniques used vary per eCommerce system and what they support.  And there are some that do not allow any interfaces at all. So be careful when picking your eCommerce software your business depends on it.

 

We will go into more details in future articles but here is some information on our techniques and it does depend on what your requirements.

  1. Do you require 2 way or just 1 way? 
  2. 1 way is just getting data from your online store into QuickBooks
  3. 2 way can be sending information up to your store.
  4.       This can be just for Quantity on Hand or
  5.       uploading of either new or changed lists (items, customers, etc.)

iii.      uploading of transactions

  1. How often do you need your orders in QuickBooks? 
  2. Daily, weekly, monthly.  Mostly done this way for just accounting
  3. Every hours or more often. Done this way for fulfillment or when you use your QuickBooks for business operations i.e. CRM type functions.

 

Our Integrator does handle many of the above but it does vary with the store’s software features.  We will discuss more of this in a future article.

 

If you do have an IIF file from your store you can use our IIFImporter (http://aaatex.com/QuickBooks_IIF_Importer.htmwithout changing the file.

 

For Excel files (xls,xlsx, csv, tab delimited) our TransImporter

http://aaatex.com/QuickBooks_Transaction_Importer_Excel_2011.htm

 

For eCommerce and other database interfaces our ShoppingCartIntegrator (the Integrator)

http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

Posted on

Importing into QuickBooks from Web Stores #2

Importing from your Web Store #2

(Understanding more to Work Smarter not Harder)

By AaaTeX
July 31, 2011

 

In the previously article we started talking about importing orders from your web store into QuickBooks.  There are thousands of different web stores systems.  These can be put into general classifications as far as getting the orders out of the store into QuickBooks:

  1. No way.  These stores just do not have any interface and do not provide any exporting of orders. These may just be pages web pages with no real code or system behind them but maybe an interface into the payment gateway.  These web stores may not even have a database. Custom programming may be possible to add support for an interface or perhaps and interface to their merchant account or payment gateway. This is not covered in this article.
  2. Their way or no way.  They support only their interface. Like it or not. Nothing can be done with these stores.
  3. Manual Export of a file to your Workstation. The file can be:
  4. Excel formats of xls, xlsx, csv
  5. Tab deleted text (also opens with Excel) or notepad
  6. Text file
  7. XLS
  8. IIF file (Intuit Interchange Format for QuickBooks).
  9. Other (not covered in this article)
  10. XML/API Interface where with eCommerce developer has provided an interface with rules that an application can use to interface with the web store database.
  11. Database access via ODBC (Open DataBase Connectivity) also known as Database Remote Access or Remote MySQL, etc..  There are several databases around but the most common are:
  12. MySQL
  13. MS SQL Server

 

Of the above there are multiple ways of getting the orders for types 3 – Manual Export of a file to your Workstation.

  1. If Excel or tab delimited text format you can uses our TransImporter
  2. If IIF files
  3. you can use the QB menu Utilities, Import, IIF files are your own risk and limitations if you have QuickBooks Desktop (not QuickBooks Online)
  4. Or you can uses our IIFImporter
  5. The Text files and other files are not currently supported by our programs without customization.
  6. If XML/API you may be able to use our Integrator. Since each XML file and API interface from each web store system is different each has to be programmed into our Integrator.  Now we do have a lot of general functions for this but new interfaces must be reviewed and tested.  We currently support XML/API interfaces with Amazon, Channel Advisor, eBay, PayPal, ProStores, Shopify and several others.
  7. DataBase access via ODBC is the most common and easiest.  All of the functions are in general purpose functions and external support files so most of the time no coding changes need to be make. This allows us to add not only new commercially available web stores quickly but also custom store/database are usually just as easily.

 

We will discuss these more in future articles.

 

If you do have an IIF file from your store you can use our IIFImporter (http://aaatex.com/QuickBooks_IIF_Importer.htm) without changing the file.

 

For Excel files (xls,xlsx, csv, tab delimited) our TransImporter

(http://aaatex.com/QuickBooks_Transaction_Importer_Excel_2011.htm)

 

For eCommerce and other database interfaces our ShoppingCartIntegrator (the Integrator)

(http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm)

Posted on

Importing into QuickBooks from Web Stores #3

Importing into QuickBooks from your Web Store #3

(Learn and Work Smarter not Harder)

With our Shopping Cart Integrator
August 7, 2011

 

When importing or integrator your web store it is important that it is entered the way you need it. This can be because of laws, your accountant, your fulfillment needs or just the way you want it.

 

You will find that although many shopping carts and eCommerce systems even say that they interface with QuickBooks it is they way they have programmed it.  Weather it be via IIF file or Web Connector you really do not have much control.  Our Integrator solves this problem since it is the most flexible product out there.

 

Most eCommerce systems if they do have a QuickBooks interface will allow you to select the transaction type and accounts. But there are so many more options that must be considered.

 

Different QuickBooks transactions:

Let us say that you like to do have your orders come in as Invoices for wholesale customers or Sales Receipts for retail customers.  As long as there is a way to tell the Integrator that the order is a wholesale order then the Integrator can create a Invoice otherwise it would be a Sales Receipt. This can be any field in the order.  Our mapping allows you to look at just about any field and use wild cards to indicate that the order is wholesale or retail.

 

Shipping Items vs. Methods:

Many QuickBooks users use different shipping items for the shipping method (i.e. Ground, Overnight, 3 Day) not just for the Ship Via field.  Most eCommerce systems will only provide an item for shipping (i.e. Shipping).  Our Integrator can create different shipping items based on the shipping method which can be the same as the shipping method or different.

 

Sales Tax:

Another common issue is sales tax (for US). Sales tax in QuickBooks is somewhat complex.  Many states are now demanding tax reporting on a county or municipality level.  The tax rates can be different for these areas.  Now your eCommerce system must calculate the correct tax amount so that the online customer can pay the correct amount but then you have to account for it in the proper area.  This is certainly true for states of Washington and California.  Just creating a QuickBooks tax line to ‘WA Sales Tax’ does not provide the level needed.  Our Integrator again solves this with multiple levels of sales tax handing. The Integrator creates a Sales Tax Item by:

  1. State (Shipping or Billing)
  2. Postal Code
  3. Any field provided by your eCommerce system as suburb, city
  4. Washington State government web service interface by address.

An external table for the above with wild cards can be used for additional flexibility.

 

The key for our flexibility is our use of external tables for the mapping of most data including:

  1. Data Fields from your eCommerce data to QuickBooks fields with our MAP files.
  2. Lookup and replace tables with our REP files.
  3. Tax table
Posted on

Integrator Channel Advisors with QuickBooks

Integrating QuickBooks and Channel Advisor

(Learn and Work Smarter not Harder)

With our  Shopping Cart Integrator
August 21, 2011

 

Channel Advisor is an eCommerce system that assist in managing your eCommerce channels i.e. Amazon, eBay, etc.  (See http://www.ChannelAdvisor.com).  It does a good job of this but ‘How do you get the orders into your QuickBooks?’, you might ask.

 

The Integrator solves this problem not by integrating each channel (i.e. Amazon, eBay, etc.) but by integrating directly with Channel Advisor. (See http://www.aaatex.com/QuickBooks_ChannelAdvisor_Integrator.shtml)

 

When Channel Advisor asked us to integrate with QuickBooks several years ago we were one of the only  company to provide a solution.

 

The Integrator supports both 1 way (downloading orders into QuickBooks) and 2 way (uploading into Channel Advisor).  Uploading includes both simple updating of a products SKU’s quantity on hand from QuickBooks and more complex the adding/updating of items from QuickBooks.  (The Integrator also supports some direct eCommerce to eCommerce without QuickBooks but that is another subject for another time).

 

There are many different configurations for the Channel Advisor setup including QuickBooks customer name, items (SKUs), methods (shipping, payment, etc.) and more.

 

The full range of Integrator premier features is supported for Channel Advisor including  but not limited to:

  1. the flagging of orders in Channel Advisor when the order is successfully imported into QuickBooks so they do not come in again and there is no need to worry about the order number or date range, etc. (Order Flagging).
  2. The Integrator can download and import orders either on demand (i.e. once a month, week, day) or periodically (every 2 hrs, 10 minutes, etc.).  We call this Polling (Real Time Simulation).
  3. Items (i.e. SKUs) can be added to QuickBooks when the order comes in.  Thus there is no need to have all of your items defined in QuickBooks before you even sell any of them. (This is great for Drop Shippers)
  4. Customers can be added to QuickBooks if not already there or 1 QuickBooks customer can be used for all orders or 1 customer name per marketplace, etc. but preserving the billing and shipping information.
  5. Yes, the Shopping Cart Integrator supports Drop Shipping (creating of Purchase Orders, etc. for QuickBooks Desktop). There are multiple configurations for this as well including 1 purchase order per order in or the combining of orders onto a single purchase order. Purchase orders will be a future article.

 

The Integrator can also support multiple Channel Advisor accounts.  Each account is a different ‘store’ in the Integrator and can have a different configuration.  This makes it possible to meet your business needs with our Integrator. The Integrator Premier Edition supports up to 3 stores, the Premier Plus up to 5 stores and the Enterprise Edition up to 20 stores.  The stores do NOT have to be the same platform and can be totally other types of eCommerce systems or shopping carts (i.e. osCommerce, Miva, ZenCart, Infusionsoft, etc. etc. and new ones are constantly being added.).  This also includes custom systems/databases.

 

 

For eCommerce and other database interfaces see our ShoppingCartIntegrator (the Integrator)

http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

Posted on

Get Your Revenues In Quicker

Get Your Revenues in Quicker
by Getting Your Billing out Faster

Importing into QuickBooks

(Learn and Work Smarter not Harder)

September 9, 2011

 

 

Many times you have a need to import a Microsoft Office Excel type of file (xls, xlsx, csv, or tab delimited) file into QuickBooks. In many cases this files are use to do your billing.  The faster you get your billing into QuickBooks the faster you can send out bills and get your revenue.

 

These files could either be manually created by someone in your office or be generated but another system.  Such systems could be web site online stores or eCommerce systems or a another program on your desktop or web, or it can come from your suppliers or customers or EDI (Electronic Data Interface).  We have seen files from ServiceCEO, Xerox PagePack, IntouchPOS and others.

 

Many people now just try to re-type this data into QuickBooks.  This can be difficult and time consuming. Prone to errors, etc. etc.   Some of our customers reported that this can take them from a few hours to a couple of weeks depending upon the nature of the data and how the files appear, etc. etc.  This can get a long time to get it done correctly.

 

To this end we have developed a number different QuickBooks programs that can import these files in minutes and save the user hours if not days of time and be more accurate.  In some cases these are billing files and that importing these files can result in getting your revenue quicker.  These bills are more accurate and thus save on frustrated customers who get overcharged or missing revenue if the bill was incorrect billed too low.

 

If the file in question is an IIF file then our IIFImporter is the solution for you.  This program will read an IIF file and import it into QuickBooks (desktop versions such as Pro, Premier and Enterprise) and QuickBooks Online.  It does not require Admin/Single user mode so there is no disruption telling you people to get off of QuickBooks while your import.

 

If the files is an Microsoft Office Excel type of file (xls, xlsx, csv or tab delimited) then either our TransImporter2011 or our Shopping Cart Integrator could work.  If the file is from eCommerce then the Shopping Cart Integrator would be best otherwise our TransImporter2011.  The TransImporter2011 is a general purpose program for importing files.  It does not have eCommerce logic in it so it is best for simpler files. Both programs support Excel types of files.

 

In some cases a special program is needed for the specific file since it may require special handling.  This is true of our PagePack2Books program which we designed just for the Xerox PagePack resellers. In this case Xerox PagePack billing files are downloaded from the Xerox web site monthly.  This billing file can be imported using our PagePack2Books program in usually about 10 minutes less.  Customers have reported that this saves them up to 80 hours per month (that’s 2 weeks a month).  Thus using a program like ours could almost page hiring a person just to do the Xerox PagePack billing and get the billing out 2 weeks sooner and revenues in sooner.

 

We have similar stories from other customers using these or other programs that we have developed for them.

 

For eCommerce and other database interfaces see our ShoppingCartIntegrator (the Integrator)

http://aaatex.com/QuickBooks_Shopping_Cart_Integrator_Cart.htm

Posted on

QuickBooks and Cloud Computing

QuickBooks in the Clouds

Access your QuickBooks from Anywhere

 (Learn and Work Smarter not Harder)

October 2, 2011

 

 

Everyone has heard of Cloud Computing and terms as Hosted, SaaS and PaaS. But what do they mean in reference to QuickBooks.

 

First Cloud Computer is just that the computers are on some system that is available via Internet Access.  Just like a web based application in many cases.  If a company sells their application program as a service via access of the Internet it is Software as a Service (SaaS).  SaaS can be extended  to include portions of the operating system or Platform and is known as Platform as a Service (PaaS).  A Hosted environment is where the operating system is provided for you with certain software products. This is called a Platform.  So this would be aPaaS.  Citrix and VMWare are becoming popular Hosting companies or provide software for hosting companies to provide a hosted solution. There are now many companies offering PaaS.

 

There are pros and cons for these techniques such a PaaS has scalability where you can ‘turn up’ the processing power and include more CPUs, more RAM, more Hard Drive as a setting on your system.  If you had to do this on your own in-house system it might mean new hardware, down time, etc.  Cloud Computing does simplify this and usually can be done with a screen setting with no down time. There are books written on this and this is not the topic of this article but Cloud Computing as it relates to QuickBooks is.

 

For QuickBooks US there are 2 basic platforms.  Desktop (QBD for Windows and MACs) and QuickBooks Online called QBO or QBOE. (In Australia, where Reckon Limited supports QuickBooks (not Intuit) their QuickBooks Online is actually a ‘hosted’ an AU version QuickBooks Enterprise on a Windows Desktop system. Many host companies do something similar)

 

QBO is in the Clouds as a SaaS application.  QBO looks a bit like QBD but it is a totally separate program and there are obvious differences not only in appearance but also in functionality.  QBD for MACs is also different than QBD for Windows. We will not be going over these differences.  Intuit’s new offering Intuit Anywhere will increase the entire ‘Cloud’ experience and make QuickBooks available to most internet connected devices (phones, tablets, etc.).

 

 

So what does it matter to you.

 

If you are using Cloud Computing then:

  1. Your data is also in the clouds. Although the provider will protect your data it is not in your control which brings up security issues and backup/restore concerns.
  2. Updating is not in your control.  Although you do not have to worry about updating your QuickBooks and applying those automatic updates, etc.  You have no control.  You will come in one day and it will be done for you. ‘Great!’ you might say but if it doesn’t work or behaves differently then you cannot fall back.  You usually have no control over the software version, etc.  You cannot test it before it goes live.
  3. 3rd party applications.  Many cloud systems or hosted environments have strict restrictions on adding 3rd party applications.  Some don’t allow it (directly on QBO Server). Some have certification processing which can be costly and take months, etc.
  4. You access the app via the internet so although it is available to you wherever you have Internet access if there is no internet service or the service is slow then the app may not be useable.

Intuit’s is pushing for their new software development interface called IPP for Intuit Partner Platform where web and desktop apps can process QuickBooks data via the cloud.  IPP does require that your QuickBooks data be in the clouds even if your are using QBD. So the Intuit QuickBooks Sync Manager will copy most of your QuickBooks data into their ‘cloud’ version of the database and all IPP apps will work on the ‘cloud’ version for your QuickBooks data.  The QuickBooks Sync Manager will sync periodically i.e. every hour or so. You must be careful that your data is synced for the cloud applications to work properly.  This can get confusing if you have multiple QuickBooks company files.

 

So what about 3rd party applications will they work with your QuickBooks?
Some will, some won’t. As technology changes the apps you use must keep up.  We will be updating our products to be in the clouds shortly.  The first will be our IIFImporter to import your legacy IIF files.  A cloud version will be available shortly (by 2012) and support QBO and QBD (it already can run in a Hosted environment with some configuration changes) then our TransImporter for Excel types of files (XLS, CSV, tab delimited text).  Our Integrator for eCommerce web sites, which currently supports about 50 different eCommerce systems and shopping carts will be later. But all do currently support a hosted Windows solution (no MACs).

Posted on

QuickBooks IIF Files

QuickBooks IIF Files
Transactions
(Learn and Work Smarter not Harder)

November 29, 2011

 

In the previous articles we discussed some basis of the IIF files, some ways of exporting and importing lists (i.e. customer, vendors, items, etc) but now transactions (i.e. Invoices, Sales Receipts, Bills, Payments, Checks, etc.).

 

 

You CANNOT export IIF files from QuickBooks Online.  You cannot natively import IIF files into QuickBooks Online but you can with our IIFImporter.

 

You should know what the ONLY way to import transactions into QuickBooks natively (without a 3rd party program) is with IIF, web connect file or bank download (qbo) file.  Normal xls or csv files will NOT import transactions. You can use our TransImporter for that.

 

Each transaction type will have a slightly different format.  We cannot go into all the different types and formats but just the basics.  Please check with the QuickBooks Help, Reference Guide to Importing for more information.

 

 

 

A transaction will consist of 3 different row types.

TRNS for the transaction header row.  A !TRNS row would indicate what columns are for what fields i.e.

!TRNS,TRNSTYPE,NAME,AMOUNT

The SPL would describe the SPLIT of the transaction into item or account lines. There would be as many SPL rows as different items on an transactions.

It would end with an ENDTRNS.

 

Note that IIF files can be either tab delimited or comma delimited. But since tabs cannot be shown commas are used here.

 

So you would see a set of definition (!) rows with the data rows underneath.

!TRNS,TRNSTYPE,NAME,DATE,AMOUNT,…

!SPL,TRNSTYPE,NAME,DATE,AMOUNT,INVITEM,QNTY, PRICE,

!ENDTRNS (optional)

 

Would indicated that the transaction type is column B or 2nd column, the NAME field is column C, etc.

The actual data for the transaction header would be in a TRNS record.

 

TRNS,INVOICE,JOHN SMITH,12/31/2011,100.00,…

SPL,INVOICE,JOHN SMITH,12/31/2011,-20,-1,20,…

SPL,INVOICE,JOHN SMITH,12/31/2011,-80,-4,20,…

ENDTRNS (required)

 

Notes:

  1. Different transaction types have different rules.  i.e. the TRNS AMOUNT for an INVOICE must be positive but for a BILL negative.  This is based on accounting practices and the ACCOUNTS associated with the transaction.
  2. the transaction must balance.
  3. All the amount fields must be equal to 0.
  4. SPL AMOUNT = PRICE (or COST) x QNTY
  5. QNTY (quantity) is usually negative on sales transactions (INVOICE, CASH SALE, etc.)
  6. The accounts used must be of the correct type.
  7. Names for customer, vendors, items, accounts, etc. if do not exist in QuickBooks will be created without notice or any logging.  Items are created as SERVICE items and ACCOUNTS as BANKS. This may not be what you want.  It is easy to get your QuickBooks all messed but with similar but different names i.e. Item123 vs Item 123 (space).
  8. NOT all transaction types are supported by QuickBooks native iif (but most are with Our IIFImporter).
  9. Customer Payments (i.e. RECEIVE PAYMENTS) are NOT posted to any INVOICES but are left as just unapplied (dangling) credits for that customer.

Our IIFImporter imports IIF Files into both QuickBooks desktop and QuickBooks online and has many features that you will probably like. Such as error logs, replacement options, not requiring either Admin or Single user mode and optionally not always creating names (optionally we can).  A log is created of all activity.

 

We will talk more on this in future article.