It is obvious that the demise of the Intuit Sync Manager will mean a potentially serious inconvenience for a lot of small businesses using QuickBooks desktop products connected to cloud based applications. In the recent article in which we published Intuit’s table reflect the 81 Apps that had used Sync Manager, no less than 40 of those Apps were labeled as ‘Contact Developer’, while 12 Apps were labeled as no longer going to work with QuickBooks (desktop). Of the 81 Apps only 29 have reported to Intuit that they have developed alternatives to retain connectivity, of these the majority appear to be using the QuickBooks Web Connector. A limited few have developed other ‘sync engines’ of their own, some may even be using products we will discuss in part 2 of this mini-series.
So what can you do if your 3rd party app developer doesn’t provide a method to connect to your QuickBooks desktop product? Well many would tell you, “migrate to QuickBooks Online”, and some would say “learn to live without the 3rd Party App”. There might even be those who tell you to ‘switch to Sage or Xero’ if either of those connects to your 3rd party product.
But the reality is that many 3rd party applications were sending data to QuickBooks long before we had the Intuit Sync Manager, and even before we had the QuickBooks Web Connector. I have clients who successfully ‘import iiF files’ every day into their QuickBooks applications which are downloaded from their 3rd party applications. Still others import CSV or Excel files into QuickBooks from their 3rd party applications.
As I wrote about the potential use of the QuickBooks Web Connector this past December as a possible alternative to Intuit Sync Manager, and several of the 81 Apps indicated that they would make use of this methodology, I will not take time in this article to write further on that subject. Just see my article with the highlighted link above.
I suspect that many of the ‘Contact Developer’ listings in the Intuit chart may not yet have figured out what they are going to do, others maybe still in development of alternatives, and others maybe intending on advising clients that file downloads (and perhaps even uploads) are a possibility. It is far more likely that you might be importing data into QuickBooks from your 3rd party using iiF or via an importer tool, than exporting data to your 3rd party application, but some 3rd party applications might very well accept iiF exports of list changes and/or exports of data in csv or Excel format.
iiF Files from 3rd Party Products for Import into QuickBooks (Desktop) Products
We hear a lot about the hazards of iiF file use, and iiF files may not support every feature in QuickBooks, especially some additional tables and advanced inventory functionality associated with QuickBooks Enterprise; however, for the most part iiF import can be used safely with a few precautions. While it is NOT the preferred method, especially for large quantities of data at one time, if you ALWAYS verify and back-up your QuickBooks file prior to importing an iiF file in single-user mode, and then immediately re-verify your data again after importing and confirm the validity of the data added (by comparison of the imported transactions against the iiF file itself (opened in Excel) you probably are not going to have any major issues.
However, if your 3rd party application is feeding you huge iiF files with hundreds or thousands of transaction lines, you may want to consider an importer tool that will actually read and import the file using the safeguards and conveniences of the SDK interface as opposed to the iiF import engine. Tools like AAATeX iiFImporter2 and ZedAxis are two such products. Both of these products ‘translate’ the iiF text file structure, then apply conditional analysis that validates the data to the SDK requirements, they then make the SDK handshake (even in multi-user mode) with QuickBooks and import the data while logging the details of the process. Any rejected data is identified and reported as to the cause of the rejection. While this involves ‘external files’, the process is essentially the same as with local SDK based applications that exchange data with QuickBooks directly. Another advantage of these programs, depending on versions available, is that you may be able to simply download your file into a directory, and schedule the application to check that directory at a specified frequency and then automatically import the file without any other intervention.
While there are many other importer tools, and even iiF tools on the market, they do not provide the functionality that the two products I just mentioned provide when the actual file structure being used is iiF in origin. (Note: There may be other 3rd party tools that ‘convert’ iiF data to a CSV or Excel format, but I am not reviewing those in this article, because that would necessitate an additional ‘tool’ in addition to an importer for CSV or Excel data.
What about Exporting of iiF files?
While the QuickBooks desktop applications provide a way to export iiF files for most QuickBooks lists, it does not provide an iiF export option for transactional data using the standard user interface. If your 3rd party application can accept an iiF file of data from your QuickBooks Company file, then you could export lists via the Export feature; however, to obtain transactional data you would want to use a product like Big Red Consulting’s Transaction Copier. That product pulls transaction from a QuickBooks Company file and formats that data into an iiF file. You can filter data based upon date, account and transaction type. While not a lot of 3rd party applications have been set-up to import iiF files, even if they export them, some developers go with the mindset, ‘why develop two different methods’ just use the same for export or import. Of course not all 3rd party applications need to import data from QuickBooks.
CSV and/or Excel Files from 3rd Party Apps for Import into QuickBooks (Desktop) Products
There are a lot of 3rd party products that have exported CSV or Excel formatted files of data, and some of them were even intended for import into QuickBooks. While the QuickBooks desktop product line has functionality to import Excel/CSV files directly into the product for several of the list types, there isn’t a method for transactions. With that said it is possible to copy and paste spreadsheet data into the QuickBooks Batch Enter Transaction feature for a limited number of transaction types, but I don’t consider copy and paste a viable option with it comes to most 3rd party applications, especially if there is a significant volume of data being downloaded from the 3rd party product.
When it comes to importing transactional data (as well as list data) into a QuickBooks file from CSV or Excel files, there are several tools that QuickBooks ProAdvisors have been using for years. Transaction Pro Importer by Baystate Consulting is one of those ‘old reliable’ tools, both Zed and AAATeX also make importers that work with these file types as well. All of these product work via the SDK approach and thus include file validation, logging and multi-user functionality. Big Red Consulting has a tool that can turn a CSV or Excel file into an iiF file for import, but that’s not really the approach we are discussing here.
Some transactional types are still not available via the SDK due to imposed limitations from Intuit on database access, but for the most part routine day to day, non-payroll transactions are importable.
QODBC by Flexquarters offers a read-write version of their product that will in fact write data from CSV or Excel (as well as many other formats) into QuickBooks. While most people have thought of QODBC as a ‘data extraction engine’ for report writing, the write version of this software makes all QuickBooks data elements supported by the QuickBooks SDK write capable.
AccessBooks Updater by Synergration is a QuickBooks import application that will dynamically create a database for you, from which you can add or modify transaction data and list items for transfer into QuickBooks.
By the way, I will not be discussing file utilities designed primarily for the purpose of QuickBooks to QuickBooks data transfer, since we are striving to export data from, or import data into, a 3rd party application to/from QuickBooks.
What about exporting data from QuickBooks?
Applications are relatively limited for this, Transaction Pro Exporter by Baystate Consulting, is probably the most commonly used tool for exporting data into an Excel/CSV format.
XBooks by Synergration provides powerful integration between Excel and QuickBooks; XBooks makes it easy to access and migrate QuickBooks data from within in Excel.
In part two of this mini-series we will look at more sophisticated technologies that maybe available to assist you in ‘syncing’ your 3rd party application with QuickBooks without the hassle of actual import or export methods using applications as described in this article.