Solution Properties
Solution ID:
222924450019939
Last Modified Date:
Thu May 04 17:20:43 UTC 2023
Taxonomy Path:
Category//Data//Import and export
Author:
sageadmin
How to import and export Accounts, Budgets &/or Transactions into the General Ledger using General Ledger Exchange
Description
Cause
Resolution
[BCB:1:Backup warning:ECB]

 

A GL Exchange import job often contains data from different modules and dates within the import job. For example, when creating an import job that contains Payroll module journal entries with postings dated 6/1/21 and General Ledger module journal entries dated 7/21/21 in the same import job. The import is complete when GL Exchange process is complete and the General Journal register is updated. The journal entries will be grouped by module and posting date.

You can import account, budget, and transaction information from external financial applications into the General Ledger module, and export information from the General Ledger module to an external application. After the data export, budget data can be manipulated and account activity analyzed by reviewing the resulting GL transactions.  For example, you can export general ledger balances into a spreadsheet and prepare next year’s budgets based on this information, and then import the new budgets into the general ledger.

You can also transfer data from one company to another by setting up an export exchange setting in the source company and an import exchange setting in the destination company.

When importing transactions, entries are created in the General Journal Entry and then posted when the General Journal is updated. Exported transactions are exported from the Transaction Detail (GL_DetailPosting.m4t) file.

Before You Begin

  • If exchange settings haven’t been defined, the General Ledger Exchange Wizard appears and prompts you to define an exchange setting. You must define an exchange setting to import or export information.

  • When importing an XML document, verify that the path to the installation of the software is set correctly on the workstation you are running the import from. This path is located in the Library Master Company Maintenance window.

  • General Ledger Exchange supported file types: ASCII, CSV (Comma Separated Values), Excel, XML (Extensible Markup Language).

 

Source file layout requirements

Use the General Ledger Exchange FAQs in help files to determine the file types supported and the required layout of the source file. One way to view source file layouts requirements for General Ledger Exchange:

    1. Open General Ledger, Utilities, General Ledger Exchange
    2. Click New Exchange Setting button
    3. In the "Welcome" window of wizard, click General Ledger Exchange link
    4. Under Related Topics section click General Ledger Exchange FAQs
    5. Click the plus sign next to What general ledger exchange formats are supported?
    6. Click the plus sign next to applicable format to view file layout requirements

 

To import Budget or Transactions:

  1. Open General Ledger, Utilities, General Ledger Exchange
  2. Click New Exchange Setting button
  3. Go through the Wizard to create the desired import job.
  4. At the Define Account Creation Options window note that `Main and Sub accounts are created with a blank description'.
  5. At the Define Exchange File Type and Name window, select the Exchange file type. It is best to browse to the source files' location by clicking the yellow envelope.
  6. Finish the Wizard and select to run the import.
  7. Select Yes to the Test the exchange setting prior to importing? prompt. (If the message "Source file cannot be opened. Retry?" pops up verify the "Exchange File Name" path, and make sure the document to be imported is closed.)
  8. Print or preview the import log for verification. There are options for printing all, only those that were imported, or only those that are skipped.
  9. Click Yes to "Continue with import" prompt.
  10. Click Yes to "# out of # records imported. Print an error log?" prompt
  11. Click Yes to the second "Continue with import" prompt

 

If importing G/L transactions, you must review and update the General Journal, by doing the following:

  1. Open General Ledger, Main, General Journal
  2. After reviewing the import, click Yes at the prompt to "Do you want to update the General Journal?"

 

To export General Ledger information:

  1. Open General Ledger, Utilities, General Ledger Exchange
  2. Click New Exchange Setting button
  3. Go through the wizard and select the desired data for export.
  4. In the Define Exchange File Type and Name window, select the Exchange File Type; and when entering the Exchange File Name, it is best to type only the name, then press ENTER. The wizard will automatically assign the correct extension.
  5. Upon executing the export, the file is sent to the path you designated in the Exchange File Name field.

Note for Sage 100 Advanced environment:

  • The default path for importing/exporting is the MAS90/Home/Textout folder

Importing Accounts:

In the exchange setting, if applicable you will need to select the option to Create Main accounts and Create Sub accounts during import if they do not already exist in Main Account Maintenance and Sub Account Maintenance. If you do not select this option, only full account numbers will be imported into GL_Account file (G/L Account Maintenance). Also, note when selecting this option, the Main account and Sub-Account descriptions are not imported into Main Account Maintenance and Sub Account Maintenance, see related article below.

DocLink: How to determine the two-digit account code associated with account types in General Ledger
DocLink: How to import General Ledger Accounts with Visual Integrator, or Error: " is not on file" in Job Log
DocLink: Account descriptions are missing in Main Account Maintenance &/or Sub Account Maintenance when importing accounts using GL Exchange
Attachment: 348_20230425114702_How to use General Ledger Exchange in Sage 100 ERP v 4.x.doc
Attachment: 348_20230425114702_GL Exchange File Format Layouts.pdf



Steps to duplicate
Related Solutions