QB Desktop Mac 2020 - How to extract invoice data with custom fields to CSV?

AtlantaBIRT · ‎January 08, 2025

I own a music instrument repair shop and have used QB Desktop for Mac for many years for bookkeeping, managing parts inventory and creating / sending invoices. When I originally started using QB I set up the invoice template to use a couple of custom fields, particularly for instrument serial numbers. Because of this we were never able to move to QB Online or any other cloud-baed bookkeeping solution (they can't handle the custom fields that we require).

We are now in the process of implementing CRM software to handle invoicing and inventory (along with many other tasks) and will be free to move to a cloud-based accounting solution since we will no longer need to track data in custom fields (the CRM software will handle this moving forward).

I have around 10,000 invoices in QB Desktop Mac 2020 that I would like to extract the data from in to a CSV file. I've spent probably 20 hours so far trying to figure out how to do it and have failed miserably. I have been able to export a report of all transactions but the report does not include the critical custom fields I need. It's crucial to our business that we are able to look up the history of the instruments we've worked on so this is not helpful.

I have seen a couple of mentions of writing a script to extract the data however my meager programming abilities are probably not up to the task. There does not appear to be any third-party apps available to accomplish this either.

Does anyone have a solution for this? I'm not really looking to move the data in to the new software but I do need to have an archive of everything we've done to reference in the future.

IntuitBrooks · ‎January 08, 2025

If you can get the custom fields to show up in a report that will be the easiest. I don't know if that is possible or not.

Beyond that, there is an AppleScript dictionary. Open QuickBooks in Apple's ScriptEditor (in the Utilities folder) and you'll see the commands.

But I suspect the command you'll wnant to use is executeqbxml.

And that will then require QBXML commands to pull invoices and then whether or not that includes custom data I don't know.

IntuitBrooks · ‎January 08, 2025

It looks like Transaction Detail by Account report can include custom fields. Hit "Show All" next to the search columns box and see fi the columns are there that you need.

AtlantaBIRT · ‎January 08, 2025

Wow - thanks so much for that! I was able to get the Serial Number column data in to the report but the Service Date column is nowhere to be found. Any idea why it would allow one and not the other?

IntuitBrooks · ‎January 08, 2025

There are different types of custom fields. Some are per item line (the table lines on an invoice for instance) and some are per transaction (like the shipping address - outside of the table line item list). Maybe it has something to do with that? In which case you might be able to get at them in another report.

IntuitBrooks · ‎January 08, 2025

Perhaps upload a window screen shot of where both custom fields are defined and one of an invoice showing where they are used on the form. You can redact any customer data or just who an empty invoice.

AtlantaBIRT · ‎January 08, 2025

I can absolutely do that. However - it's been so long since I set all this up that I'm not entirely sure where to find where I actually defined the custom fields. Do you know where I can find that info? Obviously I see the column named in the invoice template and I can turn it on and off with the checkboxes but I suspect there's more to it somewhere.

Chrea · ‎January 08, 2025

@AtlantaBIRT

Do you have a PC? You can try to convert your file and open it on the trial version of QB Desktop 2020 Pro. Then use an exporter tool to extract the data you need.

IntuitBrooks · ‎January 09, 2025

Lists->Items->Edit Item then click on the Custom Fields button.

Lists->Vendor->Edit Vendor (I double clicked on the vendor for that) Additional Fields Tab then click on the Define Fields button.

Lists->Customer->Edit Customer (I double clicked on the customer for that) Additional Fields Tab then click on the Define Fields button.

My guess is one of the fields is on an item and one is on a customer. Which of the two isn't showing up?

Topics

Training

Community

Resources

QB Desktop Mac 2020 - How to extract invoice data with custom fields to CSV?