Questions? We have answers.

QuickBooks Desktop/Enterprise Solutions: Transform the general ledger

  • Updated


This guide goes through the step by step process of transforming the QuickBooks Desktop Journal report into a format suitable for ingestion into MindBridge.


This guide presumes that data has been exported from QuickBooks Desktop in the following format, as detailed in our QuickBooks Desktop/Enterprise Solutions: Export the general ledger guide.



Transform the data

The Problem

  1. In order to ingest a file into MindBridge, the data needs to be in an ungrouped format. This means that the columns have data present on every row. A QuickBooks Desktop Journal report is grouped by transaction, which means this missing data needs to be populated.
  2. The Account ID is a mandatory field in MindBridge, however there aren't always account numbers present in QuickBooks Desktop. Where the account number isn't present, we'll need to use the account name instead.
  3. The account number and name are contained within the same column. These need to be split into separate columns to be utilized as the Account ID and Account Description within MindBridge
  4. There are subtotal rows present which cannot be utilized by MindBridge. These will need to be removed.

The Solution

  1. Populate the Date, Transaction Type, No. and Name fields with the relevant data from the rows above.
  2. Fill in the blank Account Number values with the values from Account.
  3. Split the account number and account name into two separate columns.
  4. Exclude extraneous total/subtotal/blank rows.

Items of Note

  • We will put the revised columns at the end of the file.
  • Date and Type are always present on the first row of the transaction group, but Trans# and Name are sometimes blank for a group.
  • Our logic will be to take the value from the original column if we are on the first row of the transaction, or the previous value we found if we are not on the first row of the transaction.

Ungroup column Date


  • LEN($F2)>0

    tests if there is a value in column F by testing if the length is greater than 0. Since as we observed, Date is always present on the first row of the transaction group, this serves as our check if we are on the first row of the transaction. We put $ in front of the F for ease of copying the formula to the rest of the columns to drag down (Trans#, Type, Entered/Last Modified, Last modified by), as we will always be checking the presence of Date to check if we are on the first row of the transaction.

  • IF(LEN(F2)>0,F2,"")

    returns the date if it is present, "" (empty string) if it is not. This can be simplified to simply F2 for the Date column because the Date is always present on the first row of the transaction group, but this logic is used to be able to copy and paste the same formulas for No. and Name, which, as noted above, are not always present. Because Excel will display 0 instead of blank when referencing an empty cell, we have to tell it to return "" in order for it to display an empty cell.

  • We then need to copy this formula down, either using the fill handle or by copying the formula, selecting the empty cells and pasting the formula in.

Ungroup columns Trans#, Type, Entered/Last Modified, Last modified by


Formulas in N2, O2, P2 & Q2 as follows:



Account Number and Name

The account number and name currently occupy the same column, separated by a delimiter. We can use this delimiter in a formula in order to split the number and name into separate columns.

  • =LEFT(J2,FIND("·",J2)-2)

    This first formula extracts the account number by taking all the characters from the left, up to the delimiter.

  • =MID(J2,FIND("·",J2)+2,LEN(J2))

    This formula extracts the account name by using the delimiter as a start point and extracting all text after it.

Filling the blank values in Debit and Credit columns

MindBridge does not support the blank values left in the debit and credit columns, they must be populated with a number. In order to fix this we can select all the blank cells by highlighting columns K & L, hitting ctrl+g and clicking special. We can then select Blanks, type 0 and hit ctrl+Enter to fill all the empty cells with a zero.



Filter to keep only transaction rows

We observe that every transaction row in the original file has an Account. So with filters turned on, filter out blanks from column J (Account):



Copy and paste values into a new workbook

Starting at cell B1, hit Ctrl-Shift-End to get to the end of the file, and adjust the position as necessary), then copy, then paste special values in a new workbook. You can then delete any duplicate grouped columns. In the end you will be left with a worksheet that looks like the below:



The formatting is now complete and the data is ready to be ingested into MindBridge. You can find the link to our ingestion guide below which will go into more detail about column mapping and transaction ID generation.


Previous step: QuickBooks Desktop/Enterprise Solutions: Export the general ledger

Next step: QuickBooks Desktop/Enterprise Solutions: Import the general ledger into MindBridge

What do you think of this guide? If you have any feedback, or you think you can help us improve it, then we would love to hear from you! Please contact us at We are offering rewards for any information that leads to the improvement of a guide or the creation of a new one.

Anything else on your mind? Chat with us or submit a request for further assistance.

Was this article helpful?