Import General Ledger Accounts Version

Contents

Overview

The GL Account Import feature allows you to bring files containing GL account information into the system as new accounts, as changed information for existing accounts, or as an instruction on what account information to delete. You can run the import function from the system as often as you need to.

• This topic focuses on GL account imports using .csv files.

When you upload GL account information, the system performs these steps:

1. Checks the file - The system verifies that the file is a GL account file, and determines if the file format is correct.

2. Reads the file - The system reads the file to determine the action(s) needed; for example, changing GL file information. You set the actions in the import file's ACTIONCODE field. The ADD and DELETE ACTIONCODEs add and delete account information. The CHANGE ACTIONCODE updates account information.

3. Processes the file to add, delete, or change account information in the system.

If the system cannot complete any step because of errors, it logs the errors. You can view the errors and correct them.

Security

To display the list of GL account imports, users must have the role object "Accounts" set to View, as a minimum.

Step 1: Get a template to use for adding, changing, and deleting accounts

The list of General Ledger Accounts can be downloaded as an Excel spreadsheet and saved. This spreadsheet is your template for uploading new accounts, and changes or deletions.

1. From the General Ledger main Contents, select Account Management > General Ledger Accounts.
   The list of accounts in your Chart of Accounts appears.
2. Filter the list for accounts that are similar to the type of account that you wish to import.
   For example, you can select Balance Sheet from the Account Class quick filter and Asset from the Account Type quick filter.
   See Figure 1.
   

Figure 1 - The List of GL Account Codes Filtered for Balance Sheet and Asset

3. Then, click Go.
   The list is filtered as you directed.
   
4. Click Spreadsheet (Figure 1, upper right, red box).
   - If the list contains more than 200 lines, the application asks you how many records you wish to download. Select a value.
   An Excel spreadsheet opens with the accounts displayed on the list (Figure 2).

Figure 2 - The Excel Spreadsheet Download Containing Filtered Accounts

5. Save the spreadsheet to a local network folder as a .csv file. The file name must be unique. This file will be the template you use to add, change, and delete accounts.

• Notice the ActionCode in column G. This code tells ERP what to do with the account on that line. Values are:
  C - change the information on this account.
  A - add this account.
  D - delete this account.

Step 2 - Open and edit the .csv file to work with account information

Be aware that Excel can add commas for blank columns to .csv files, so you may wish to double check the .csv file that you generate.

1. Open the .csv file that you made in the previous step and edit it as follows:
   - Change data in columns as needed. For changed account data, be sure to set the ActionCode column to C.
   - For any account that you wish to delete, set the ActionCode column to D.
   - To add new accounts, enter the account information in the appropriate columns, and set the ActionCode to A.
   The columns AccountCode, Description, GLAccountType, AccountStatus, and ActionCode are required.
2. Add the following end-of-file marker after the last line of comma-separated data, on a line by itself: <EOF>

Important: Your file must contain <EOF>, by itself, on the last line.

3. Save the file as .csv and close Excel.

Step 3: Import GL account information into the system:

1. From the General Ledger main Contents, select Imports / Exports > GL Account Import. Or, from the AP main Contents, select Imports/Exports > GL Account Import.
   Note: You can also import GL accounts from Materials Management > Imports / Exports and Financials.
   
   The Imports for Import Type GLAcct list appears (Figure 3). The list contains GL account information that has been uploaded.

Figure 3 - List of GL Account Imports

2. Click New. The GL Account Import panel appears (Figure 4).

Figure 4 - GL Account Import File Upload Panel

3. Enter the file that you wish to import in the File to Upload field.

• Click Browse (on Microsoft IE or Edge) or Choose File (on Chrome) to locate the file.
• Click the file name to select it.

4. At this step, you have a choice. You can work through the import process in two stages or in one stage. For new users of this feature, we recommend the two-stage approach.

• The two-stage approach first loads the .csv file and validates it. Once the file has successfully loaded, you can import the information into the system.

- Select the box labeled Load and Verify, No Import.

- Click Submit. The system attempts to upload your file and validate it. If successful, the system sends you a message. If the system cannot upload your file and validate it, error messages appear. Read and close the error messages. Make changes as needed in your .csv file and try the upload again. Click here to read debugging information.

The Imports for Import Type GLAcct list appears.

- Click Refresh to see your file listed.

• The one-stage approach loads and immediately processes the file records. This method is efficient if you are confident that your .csv file will not contain many errors.

- Click Submit. The system attempts to upload your file and validate it. The Imports for Import Type GLAcct list appears.

- Click Refresh to see your file listed.

5. Review the Import.. list entry for your file. The following fields provide useful status data:

Import Status Desc - The successful or unsuccessful status of the upload.

Errors: The .csv file could not be read or validated.

Ready for Processing: If you have used a two-stage upload, the file is ready for the second (update) step.

Processed - The number of data records processed out of the total uploaded. This number indicates how many of the import file records successfully performed their respective actions on General Ledger account records.

n of X: X is the number of data records uploaded and n is the number of records processed.

For a one-stage upload, this field indicates whether the system could make changes to existing GL account information using the imported file's records, and, if so, how many records processed successfully. 0 of X means that the system was not able to process any of the imported file's records.

Click Menu > View Import Details. The GL Account Import Details list appears, and you can review your data for errors.

For a two-stage upload, 0 of X is normal.

To complete processing of your import file:

If you are using the one-stage approach, the system has run the processing step for you. You can review any errors, and begin fixing any problems.

If you are using the two-stage approach, you need to process the GL account information file that you uploaded so that the appropriate changes are made to GL account records. Do the following:

1. From the General Ledger main Contents, select Imports / Exports > GL Account Import. The Imports for Import Type GLAcct list appears (Figure 3).
2. Locate the file that you wish to process.
3. Click Menu > Reprocess. The system asks you to verify that you wish to reprocess the file.
4. Click Submit.

• The system tells you how many records were successfully processed, and how many contained errors.
• If you have no processing errors, then you are finished, and the Import Status Desc is Complete.
• If you have errors, Import Status Desc contains the value Errors. See To debug your import file to correct the errors and click Menu > Reprocess again. You may need to make changes in your .csv file and re-import the file.

Step 4: Debug your import file:

You can encounter errors at any or all of the stages of GL account import. Error messages provide information about problems with the .csv file. In most cases, you will wish to alter your .csv file. Rarely, you may need to change values in the system tables.

A. At the validation and reading stages:

1. Check that the file name and path are correct.
2. Make sure that the import file is a .csv file.
3. Verify that your file has the header line.
4. Check that you have included a line containing the correct column names, in the correct order, and that none are missing.
5. Check that the correct number of fields are in the import file for each record.
6. Review the ActionCode values to make sure they are correct.
7. Check that the file contains <EOF> on the last line.

B. At the processing stage:

When you select Menu > Reprocess, the system attempts to update (add, or delete) data in its GL account records using the imported data. The number of records successfully processed appears on the Import... list, and errors in processing each record are logged. To see the errors:

1. Click Menu > View Import Details. The imported records appear.
2. Review the Processed field for each record to determine which have errors. Yes indicates that the record successfully updated, deleted, or added account information. If the field is empty, the record has a problem, and the system could not process it.
3. Click View Errors to get a list of the problems with the record. In most cases, you will want to go back to the .csv file, correct the data, and re-import the file.
   

Appendix - GL Account Import .csv File Fields

Figure 5 documents the fields in the spreadsheet template (.csv file), in case you wish more information.
- The green row in the figure is Row 1 for the .csv file.
- The yellow column (that contains column names) is Row 2 for the .csv file.
- <EOF> (the last row in the figure) must also be the last row in the .csv file.

Figure 5 - .csv Fields for Importing GL Accounts

Copyright © 2023 by Premier Inc. All rights reserved.