Creating Trial Balance Historical CSV

The setup and configuration of a Parish Entity within Intacct includes the loading of GL historical data from the previous accounting system. The GL historical data is exported from the previous accounting system, processed, and imported into Intacct. The basis of the GL historical data is defined in exported monthly trial balance reports into CSV (comma separated variable) file(s). This process guideline provides guidance on creating the CSV file(s) for the GL historical data.

This format of the CSV file for historical GL data is based upon the CSV import template format used by Intacct.

Download CSV template

The CSV template for GL historical data is available from the Intacct PSC Parish Process Documentation dashboard.

Navigate to the Parish Process Documentation dashboard
Locate the GL Historical section
Select Template: GL Historical TB and download the CSV template to you local computer

Figure 1: Parish Process Documentation - GL Historical

Export trial balances

Export a monthly trial balance report from the previous accounting system for each month that GL historical data is to recorded in Intacct.

Include the month prior to your desired GL historical data range. For example, if you are migrating to Intacct in the middle of your fiscal year, you will need to export the trial balances for the previous 7 months.

Create Trial Balance CSV

The monthly trial balances exported from the previous accounting system need to be converted into CSV data files so that they can be used by the process of importing GL historical data. An Intacct CSV template (see section above) defines the layout of the CSV data file.

Figure 2: Trial Balance CSV

The process for importing GL historical data uses a specific CSV data file layout. Minor modifications to each monthly trail balance export will be required. Typically, each export trial balance report would be converted to a CSV data file. Optionally, multiple trial balance reports can be converted into a common CSV data file.

There are several different formats of exported trial balance reports due to the type (e.g. QuickBooks, QuickBooks Online, PDS, BlackBaud, Sage 50, etc.) and version of the previous accounting system. Consequently, the following steps highlight the general steps needed to define the CSV data file.

Open the GL Historical TB template (previously downloaded) in Excel
Define the DATE and DESCRIPTIONof the trial balance. This information only needs to be defined on line 1 of the trial balance’s CSV data.
1. Define the end-of-month DATE of the trial balance data
  1. Example: 6/30/2021
2. Provide a DESCRIPTIONof the trail balance data
  1. Example: TB for June 2021

Figure 3: Define Trial Balance DATE & DESCRIPTION

Open the exported trial balance report in Excel. Copy the Account, Debit, and Credit values from the export trial balance report to the CSV data file. Many rows of data will be copied to the CSV data file. Ensure that the data values remain aligned.
1. Copy / Paste the ACCOUNT values
2. Copy / Paste the DEBIT values
3. Copy / Paste the CREDIT values

Figure 4: Define Trial Balance account values

Often, exported trial balances contain a TOTAL row. Do NOT put an entry in the CSV data file for the trial balance TOTAL.

Define the LINE_NO for each row of the trial balance data. Each of the trial balance’s CSV data requires a line number (LINE_NO) to identify it belongs to the set of CSV data associated with the trial balance.
1. The first row of CSV trial balance data must have the LINE_NO of 1
  1. This row should also have the DATE and DESCRIPTION defined as well.
2. Each sequential row of CSV data should have the next consecutive line number value (e.g. 2, 3, 4, …)

Figure 5: Define Trial Balance LINE_NO

Save the CSV data file
1. Select the File menu and choose the Save a Copy option
2. Name the so that it has the prefix “TB “.
3. Set the file type as “CSV UTF-8”
To create individual CSV data files, repeat these steps for each exported trial balance report.

Optional: CSV with multiple TB data

Optionally, a single CSV data file can be defined that contains the gl historical data from multiple exported trial balance reports. Each export trial balance would have its own set of rows within the CSV data file. The following steps define how to append the trial balance data to an exist CSV data file.

Define a separation row after the last row in the CSV data file.
1. In the DONOTIMPORT column, enter a comment that mustbegin with “#” character.
  1. Example “# trial balance data for July 2021”
    1. Note: the comment would not include the quotation marks
Repeat the steps 1-5 above after the last row in the existing CSV data file.

Sample CSV Data File

Figure 6: Sample CSV Data File

References

Prepare your CSV file for import (intacct.com)