Migrating from QuickBooks to LGL

In this article:


If you use QuickBooks, we expect that you will continue to use QuickBooks, so this isn't as much about migrating, per se, as it is about copying your QuickBooks database into LGL to get your LGL database up and running.

To copy data from QuickBooks into LGL, you can run an export from QuickBooks and then import that into LGL using the Flex Importer.

There are a few key things to keep in mind as you do this:

  1. Export both the customer (donor) details that you want in LGL as well as the transactions (donations) you want in LGL.

  2. When importing data using LGL's Flex Importer, take advantage of the "Full Name" field to import the Name field from QuickBooks. LGL will parse the Full Name field into its respective elements and does a good job with a variety of Full Names formats, such as Paul Newman; Mr. Paul Newman; Mr. and Mrs. Paul Newman; and even formats with middle names and initials.

Note: if you have Organization names in your Name field in QuickBooks, then you should insert a column in your upload file with the header "Organization name" and move all your organization names out of the Full Name column and into your Organization name column. Otherwise you will end up with records like "Foundation, Ford" because LGL will try to parse Ford Foundation as if it were a First and Last Name.

QuickBooks likes to provide summary data, such as rows of subtotals and totals. Those are not helpful when importing data to LGL. You should not provide any summary or subtotal data to LGL -- LGL will be able to run those based on the individual items you upload.

The instructions below are split into the desktop version of QuickBooks Desktop and QuickBooks Online.

Migrating data from QuickBooks Desktop

You'll need to complete these three steps: 1. Preparing the export in QuickBooks (running a query to get the transactions you want) 2. Exporting that report from QuickBooks to Excel (or a text file) 3. Importing that file into LGL

1. Preparing the export

Generate a report that has the transactions you want to upload into LGL. In this example we’re using the report Transaction List by Date found in the Accountant & Taxes area. Do not use a report that creates subtotals—this will cause problems when uploading to LGL.

2-quickbooks-select.png

A few things to keep in mind

  • Time frame: You want to make sure you select a time period that covers the period you want to load into LGL, which is probably your entire history.

  • Address, Phone, and Email fields: When importing your customer list from QuickBooks file into LGL, you want as much of their contact information as possible. Customize your report so you include these fields in your export.

  • Transaction Types: LGL is a great place to track income, but is not designed to help you track the payments you make. So, you will want to filter your report from QuickBooks to include just the income items. Also, you might not track income from sales of merchandise or program fees in LGL (some organizations do, some don’t). When you run your QuickBooks report to get a file of transactions to upload into LGL, make sure you filter by Class or Transaction Type or some other way you might distinguish donations from other transactions.

4-qb-select-classes.png

You’ll see the results on your screen.

quickbooks-report.JPG

2. Generating the export

Click “Customize Report” and go to the Header/Footer tab and uncheck all the header and footer information Quickbooks tries to add. This information is helpful if you’re printing the report, but it’s not helpful for the file we want to upload to LGL.

Click Excel to export a copy to Excel (create a new spreadsheet).

6-qb-export-options.png

The Excel export is in pretty good shape for uploading now into LGL. A few of things to point out:

  • Make sure your transaction data is the first tab (sheet) in your workbook. If QuickBooks created a summary page on tab one, delete that or move it so your transaction data is in sheet 1. This is critical because the LGL Flex Importer only looks at the first tab of the Excel file you upload.

  • The first two columns have no headers. If you upload as an xls or xlsx file these will be ignored automatically; if you upload a csv file you will be able to tell LGL to ignore these columns.

  • The first row below the headers is blank except for column A, and the same for the last row. LGL should ignore these rows, but if not, you will be able to ‘reject’ those records when reviewing your uploaded file.

quickbooks-export-excel.JPG

3. Importing into LGL

In LGL select the Flex Importer (on your home screen) for upload.

During the import process, you will be able to review which records LGL matched to, and if you see a match that is incorrect, you can break that match (i.e., if your QuickBooks record for John Smith is incorrectly matching to an LGL record with the name John Smith).

Map the fields from your upload to the LGL fields.

As you see below next to the arrow, we are telling LGL to import the Name field into both the Full Name field as well as the External Constituent ID. This is a good option if you're not using the External Constituent ID field for anything else. You can use it to store your QuickBooks name field and that will help make sure that future imports from QuickBooks will map to the correct constituent record in LGL. Note that QuickBooks ensures that each Name field is unique.

Where the arrow points above, there is a default being set that inserts the Name field from your Quickbooks file into the External Constituent ID field. The "Set default" rule looks like this:

After you’ve completed the mapping one time, you can save that upload as a template and then re-use it on future uploads. So future uploads will go very quickly.


Migrating data from QuickBooks Online

You'll take these three steps: 1. Exporting customer and transaction lists from QuickBooks 2. Preparing these files for import to LGL 3. Importing into LGL

1. Exporting customer and transaction lists from QuickBooks

From QuickBooks Online, this step requires two parts (versus only one from QuickBooks desktop)

A. Customer List

In the Customers area, click the Export to Excel button to generate an export of your customer list:

med-QBO-customer-export-button.png

Note that the Excel export includes fields for Address, Phone, and Email, even if they aren't showing on your on-screen view:

med-QBO-customers-excel-export.png

B. Transaction List

Next, go to the Reports area in QuickBooks and select the Transaction List by Date report. Customize the list to include all of the fields that you'll want in LGL. It would be nice if Address, Phone, and Email were available options here, but they aren't (hence step 1 above).

Transaction Types: LGL is a great place to track income, but is not designed to help you track the payments you make. So, you will want to filter your report from QuickBooks to include just the income items. Also, you might not track income from sales of merchandise or program fees in LGL (some organizations do, some don’t). When you run your QuickBooks report to get a file of transactions to upload into LGL, make sure you filter by Class or Transaction Type or some other way you might distinguish donations from other transactions.

med-QBO-transactionlist-avail_fields.png

2. Preparing these files for import to LGL

You have two Excel files to upload, and the key thing to notice is that the Name field in the two files is identical:

med-combo_excel_cust_and_trans_lists.png

Because the Name field in QuickBooks is guaranteed to be unique (QuickBooks doesn't allow two records to have the same customer name), you can use this field as an External Constituent ID in LGL. This will really simplify things when it comes to matching the records from the second file (the Transaction List) to the records you've uploaded from the first file (the Customer List).

The Transaction List report includes a few header rows. You'll want to delete those before uploading. Your first row should be the headers of your columns.

3. Importing into LGL

In LGL go to the Flex Importer (located in the subnav of the Home tab). Select your QuickBooks customer list file for upload.

Map your fields to LGL:

The Customer field should map to both the External Constituent ID field and the Full Name field. The image above doesn't show the rule in detail, but where it says "Rule: Defaults for Full Name" is where we've told the Flex Importer to re-use the Customer field for the Full Name field (in addition to the External Constituent ID field).

The Address in QuickBooks is a Full/Formatted Address. You'll want to review those in LGL since it can be tricky to parse addresses.

Next, map your Transaction List to LGL. The most important field to map correctly is to map the Name field to the External Constituent ID field. This allows LGL to use that field to identify the exact customer record you uploaded from the first file:

You'll want to take a minute to see if mapping the Split field to Gift Category is what you want, and also decide if you want to map any of the other fields to Campaign or Fund in LGL.

After you’ve completed the mapping one time, you can save that upload as a template and then re-use it on future uploads (so future uploads will go very quickly).

Still need help? Contact Us Contact Us