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.
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 Name | Description | | ---------------- | -------------------------------------------------------------------------------- | | Transaction Date | The day the transaction occurred | | Journal Entry Id | The ID of the Journal Entry (becomes the DocNumber in QBO) | | Account Number | | | Account Name | If you aren't using Account Numbers, it will use this column to find the Account | | Class | | | Customer Name | | | Amount | Must be a positive number | | Posting Type | Either "Debit" or "Credit" | | Description | Description for the entry | | Currency | Optional. Allows you to specify a different currency code |
Your final file should look something like below:
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:
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!
