Exporting data to QuickBooks

[stanley]

Hello all:

I've got a client who sends monthly data to his accountant; currently he sends paper, but he'd like his FileMaker solution (which contains all the needed data) to export something which the accountant would then be able to import into QuickBooks. Now, I don't know much of anything about QuickBooks, so I'm not sure where to start with this, and I haven't yet found out exactly what data he'll want to export, but I'll assume it is fairly simple accounting data.

Can anyone give me a quick and dirty rundown of this particular exporting process? I can do the sorting, exporting, etc., it's really just the formatting I'm unsure of, or if I'll need a plug-in, or whatever. The end product would be burned onto a CD and mailed to the accountant.

Thanks

-Stanley

[DanBrill]

Hi Stanley,

This isn't quite that quick and easy, unfortunately. You'd have to export all of the data in the QuickBooks specification of xml, known as qbXML. Each type of transaction has its own xml syntax, as well as a buncj of options. Probably the best thing to do in this case is to export each transaction from FM to QB as a journal entry. (In QuickBooks lingo, as opposed to standard accountant-speak, Journal Entries are only one kind of transaction. There are also checks, deposits, invoices, bills, and a bunch of others.) However, Journal entries would convey all the pertinent information for his accountant. You'll also need some way to actually send the data to QuickBooks. You can do this using the FileBooks link plug-in, but I've written my own executable that does the job.

The first thing you'll want to do is take a look at how QuickBooks works, and get a basic understanding of xml if you don't have that yet. Also check out the FileBooks link site and the Intuit developers network to get the QuickBooks Software Developers Kit (SDK). The SDK has a bunch of examples of the xml requests and commands.

Good luck,

Dan

[andygaunt]

Stanley -

You may also find the following useful

http://www.markjeffords.com/fmbooks/fmp2qb.htm

http://www.filebookslink.com/

HTH

[DanBrill]

Hi Andy and Stanley,

I took a look at the links you suggested. FilebooksLink and my proram are based on xml. Mark Jeffords program is based on iif, the Intuit Interchange Format (I think I have the acronym right). XML is the newer and more current technology from intuit. I don't know how many more QB versions will support iif standards. XML is the wave of the future.

Having said that, the Mark Jeffords program is probably perfect for Stanley's needs. It's free, open source, and simple. It may not be a perfect solution, but it is one that -- gasp -- could generate some revenues!

So Stanley, I'd look at that first since you don't know much about QuickBooks. (I have no idea how much you know about xml, but it's probably more than I know! Good luck!

Dan

[stanley]

Hi Guys:

Thanks for both of your input. I think maybe I wasn't clear, and really my question should have been:

Can QuickBooks import tab-delimited or comma-delimited data, the way a database can? The only data I'd be sending out would be two sets: Invoices and POs. These would just have dates, names and amounts. Can QuickBooks import a set of data like that?

Thanks again

-Stanley

[DanBrill]

Not as such. There are 2 ways to get data into QuickBooks. (Well 3 if you count typing. ) One way is xml. The other is iif. In either case the data has to be in a very specific format so QB can read it. Mark Jeffords program will structure FM output into the iif format, so I think that's what you want to look at first.

Dan

[stanley]

The problem with the Mark Jeffords program is that it is a Win app (unless I missed something on his website) and this is an all-Mac house.

Will Excel export to QuickBooks? I ask that because I believe I can import into Excel - then perhaps I could export to QuickBooks from there?

Thanks

Stanley

[DanBrill]

Hi Stanley,

I haven't looked at it in depth, but isn't the Jeffords program just a straight FM application, and therefore cross platform? What it does is takes your data and reformats it into the the iif format that QuickBOoks needs. iif is just a highly-structured plain text file. And nothing is more cross-platform than plain text.

I think the answer to your question is yes, you could probably get data from excel to export as plain text in the iif format that QB wants. But Jeffords app already accomplishes this, so you'd be re-inventing the wheel, and its a very tedious wheel.

Moreover, I think you'll have problems finding decent documentation for how iif is structured. Intuit (QuickBooks producer) isn't really doing anything with iif these days. It was never easy to get info on the iif structure. Now everything is xml. They don't even talk about iif anymore. Future versions of QB may not even support it. Its an xml world from here on out.

In the long run your better off developing an xml solution. But if you want to do it fast, then go iif. Jeffords program is the fastest way to get the iif structure.

If you really do decide to try it from excel, I'd still look at Jeffords program to see how he turns the FM data into plain text in the iif format. It will probably be better than any documentation that you can find out there.

Dan

[rogermax]

Dan is right. The solution is straight FMP and xplat. Moreover it is freeware, and very nicely done at that. On the other hand xml is the long term solution. Once you get into it then it starts to make sense. The QB SDK has good documentation on how to structure the data.

That said, I am almost to the point of understanding enough to put it together. maybe.

[stanley]

Guys:

Thanks for the input, again. Unfortunately, the Jeffords solution is Windows-only (according to the requirements on his page, and the fact that the download is an .exe file) so I'm going to have to do it another way. It's the last item on the to-do list for this job, so maybe something will come up which will alleviate the problem...

Happy new year to all

-Stanley

[rogermax]

yep, missed that he is using a VB script.