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.

More details about the name fields: 

In QuickBooks, there is a Customer name field, which is guaranteed to be unique, because they don't allow you to re-use the same customer name twice. This field is very useful to set as the External constituent id in LGL. 

You will also need to set one or more name fields in LGL. The most likely fields you'll use are:

Full name LGL will parse into first and last (and prefix if there is one). It can also handle spouses, such as "John and Shasha Smith"
Last, first LGL will parse into first and last name. For example "Smith, Will" becomes first name = Will, last name = Smith
First name First name of the constituent
Last name Last name of the constituent
Employer/Organization Organization name (either the Employer in the case of an individual constituent record or the organization itself for an organization constituent record)

About Constituent type:

In LGL every constituent record is one of two types: Individual or Organization.

It's important to set this correctly, based on who the donor actually is. If you're recording a donation from John Jay at Johnson Machine Works, is the gift from John or from the company? If the former, it should be an individual record; if the latter, an organization record.

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.

Beware subtotal rows of data:

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:

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

B. Transaction List

Next, go to the Reports area in QuickBooks and navigate to Accountant Reports area:

We're looking for a report that will provide a listing of your transactions to import to LGL.

Good options are the Transaction Detail by Account report and the Transaction List with Splits report:

Both of these reports include the Class field, which is important to carry into LGL. If you need to filter which transactions you want in your export, then the Transaction Detail by Account report is a good choice, as it provides more filtering options.

Once you've selected the report, set the date range for the transactions to include.

Then edit the Rows/Columns selection. Both reports allow you to set the 'Group by' to None. This is very important: otherwise your report will be full of rows of subtotals and summary information that you'll spend a lot of time removing.

Click Change columns (at the bottom of the Rows/Columns section), to choose the applicable columns for your transaction report:

Filter the transactions you want in your report, for instance you might want to include Sales Receipts, but make sure to exclude other types.

2. Preparing these files for import to LGL

You have two Excel files to upload, and the key thing to notice is that the Customer name field in the two files is identical. You'll set this as the External constituent ID during the customer import and use it to match transactions in the second import.

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.

Here's an example of the two files you'll have:

Customer file
In this example, we've created some additional name fields so we can import various formats of individual names (Full Name or Last,First formats). And we've created a field for the organization name, plus a field to set the Organization Type.

Transaction file 

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 name field should map to the External Constituent ID field and you may also want to use it to set the Full Name field. Or, in your spreadsheet, create the separate name fields as described at the beginning of this article. 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).

In this example, we're using the file where we've created some additional name columns (Last, first and Employer/Organization).

We've also mapped the Address field to a field in LGL called 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.

Do not map the Num field to External gift ID, because that number is not guaranteed to be unique in Quickbooks. For example, if multiple items are on a single sales receipt, they'll have the same Num in that column.

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