How to import CSV data into Quickbooks using Python cover
Mar 9th 2021

How to import CSV data into Quickbooks using Python

Hassan Syyid profile image
Hassan Syyid

If you've dealt with importing data into Quickbooks Online, you know it's a tedious process. In fact, it's such a hassle that companies like SaasAnt have built products dedicated solely to automating this process.

In this article, I'll show you how to leverage hotglue's target-quickbooks to import CSV data into Quickbooks.

Importing data to Quickbooks with Python

Step 1: Format your Spreadsheet

First, we have to put our Journal Entries spreadsheet in a format that the target-quickbooks package can understand. Name the file JournalEntries.csv the columns should be:

Column NameDescription
Transaction DateThe day the transaction occurred
Journal Entry IdThe ID of the Journal Entry (becomes the DocNumber in QBO)
Account Number
Account NameIf you aren't using Account Numbers, it will use this column to find the Account
Class
Customer Name
AmountMust be a positive number
Posting TypeEither "Debit" or "Credit"
DescriptionDescription for the entry
CurrencyOptional. Allows you to specify a different currency code

Your final file should look something like below:

example file

Step 2: Setup our environment

Create the virtualenv

To avoid dependency hell, I highly recommend running through this example in a virtual environment.

# Create the virtual env

$ python3 -m venv ~/env/target-quickbooks

# Activate the virtual env

$ source ~/env/target-quickbooks/bin/activate

# Install the dependencies

$ pip install git+https://github.com/hotgluexyz/target-quickbooks.git

# Create a workspace for this

$ mkdir quickbooks-import

# Enter the directory

$ cd quickbooks-import

These commands may vary depending on your OS and Python version.

Step 3: Configure the target

Get the OAuth credentials

First off, you're going to need Quickbooks OAuth credentials. This process is already well-documented by Quickbooks, so I'll assume you can follow that guide. If you're not familiar with how to complete an OAuth authorization flow, you can follow the hotglue docs.

Create the target config

Now we have to create a target config. This will specify our OAuth credentials, where it should look for our CSV file, and some QBO specific settings. Here is an example config:

{

"client_id": "secret_client_id",

"client_secret": "secret_client_secret",

"refresh_token": "secret_refresh_token",

"sandbox": true,

"realmId": "123456789",

"input_path": "tests"

}

Fill in your credentials, and save this to a file called config.json in the local directory. Make sure to set sandbox to false if you're not using a sandbox QBO account.

Save the formatted spreadsheet

Save your JournalEntries.csv file to the folder you specified in input_path for me, that is tests

Note: The target expects the file to be named exactly JournalEntries.csv

Step 4: Send the data to Quickbooks Online

Now we can run the target, and send our data to QBO!

target-quickbooks --config config.json

If any errors occur while sending the Journal Entries you will see the errors directly in your console. A successful import should look something like this:

target-quickbooks - INFO - Converting MAR21 REV_REC (2)...

target-quickbooks - INFO - Loaded 1 journal entries to post

target-quickbooks - INFO - Posting process has completed!

Voilà! We see the new Journal Entry in Quickbooks:

1_8h_YOE3D8YUjKHmhEAYulg.png

Conclusion

Next steps

If you're looking to deploy this pipeline, or want to offer this functionality from within your own product check out hotglue.

Hopefully this saves you some time. Right now the target only supports Journal Entries, but support for Invoices and more are coming soon. Thanks for reading!