How to Import CSV Data Into Quickbooks Using Python
If you've imported data into Quickbooks Online, you know it's tedious. It's such a hassle that companies have built products dedicated to automating the process.
Join the DZone community and get the full member experience.Join For Free
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.
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:
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 the 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:
Fill in your credentials, and save this to a file called config.json in the local directory. Make sure to set the 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:
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!
Published at DZone with permission of Hassan Syyid. See the original article here.
Opinions expressed by DZone contributors are their own.