Skip to end of metadata
Go to start of metadata

How to Use the Data Exchange Feature

 

 

The Data Exchange feature allows you to import information to or export information from the General Ledger Masterfile or the General Ledger Transaction file.  The Import feature is especially useful for converting from your existing General Ledger system.  You may also use the Import Transactions to transfer General Ledger activities from one physical location to another or from one company to another.

 

Explanation of File Types

To successfully use the Data Exchange feature, you must understand the different file types supported.  Each type represents a standard file format used by a large number of popular applications.  The Data Exchange feature supports the following file types:

 

ASCII text file DIF (Data Interchange File)

DBASE III Plus file LOTUS 1-2-3 Spreadsheet File

Delimited file

 

If you wish to import data from a file type that is not listed above, you must first create an intermediate file using one of the supported formats. Most applications using a proprietary file system provide a data-exporting feature that supports one of the above file types.

 

NOTE : Make sure when importing that you specify the same File Name and File Type used when exporting the data.  Mismatching File Types will prevent the import process from functioning.

Data Exchange File Formats

When importing using Mas90 G/L Exchange, the data fields MUST be arranged in a very specific order. When exporting through Mas90 G/L Exchange, the data fields will export arranged in a very specific order.  There is no way to alter the order in which these fields will be exported.

 

If you will be using Excel or Lotus to manipulate the data, exporting to a delimited format would also be advisable.  ASCII text should also be readable by most any software package.

 

 

When importing or exporting Account Data

Items considered ”Account Data” include G/L account number, account description, actual postings (summary figures), and budgets.

  1. 1.           Go to the G/L “ X change” menu and select “Export Account Data” or “Import Account Data”.
  2. 2.           Specify the starting and ending ranges for the G/L Acct#’s, Department, and Location. For a full import or export, you should leave these fields at their default values.
  3. 3.           Specify the import or export file name.  Export Account Data uses GLACNTS.ABC as the default File Name, where ABC is the current Company Code. You can accept the default file name or create one of your own. However, it is recommended that you always use the Company Code as the extension to avoid confusion when importing the data.  If you want the file to be placed in a particular file subdirectory, you will need to enter the full path along with the file name. If you simply enter a file name with no pathing, MAS90 will automatically place the file in the ..\MAS90\HOME subdirectory.
  4. 4.           Select the file type you wish to export the data to.  The available file types will be listed along the bottom of the screen.
  5. 5.           Specify the Fiscal Year to be exported. If you have data for several Fiscal Years, each year will need to be exported separately.

 

 

When importing or exporting Transactions

  1. 1.           Go to the G/L “ X change” menu and select “Export Transactions”.
  2. 2.           Specify the starting and ending ranges for the G/L Acct #’s, Department, and Location. For a full export, you should leave these fields at their default values.
  3. 3.           Specify the date range you wish to import or export. For a full import or export, you should leave this field set to “All”.
  4. 4.           Specify the Source Journal codes you wish to export. For a full export, you should leave this field set to “All”.
  5. 5.           Specify the import or export file name. .  Export Transactions uses GLTRANS.ABC as the default File Name, where ABC is the current Company Code. You can accept the default file name or create one of your own. However, it is recommended that you always use the Company Code as the extension to avoid confusion when importing the data.  If you want the file to be placed in a particular file subdirectory, you will need to enter the full path along with the file name. If you simply enter a file name with no pathing, MAS90 will automatically place the file in the ..\MAS90\HOME subdirectory.

 

 

 

General Ledger Account Number & Description Only

The following is a sample of how a source file must be formatted when importing in the G/L Account Number & Description information through “Import Account Data” using the Exchange feature in General Ledger. The following sample is also what will be exported when using the G/L Exchange “Export Account Data” feature to export G/L Account Number & Description information

 

 

 

1

2

3

 

 

 

100000000

Cash on Hand

01

101010000

Cash in Bank-Reg. Checking

01

101020000

Cash in Bank-Payroll

01

101030000

Cash in Bank-Savings

01

 

 

Explanation of Columns

Column 1 - Represents the G/L Account Number.  This field should always have 9 characters and will always export with 9 characters, even if the user’s account numbers in Mas90 may actually be less than 9 characters.

 

Column 2 - Represents the Account Description.  Maximum of 30 characters.

 

Column 3 - Represents the Account Type. *  This field should always have 2 characters and always exports as 2 characters.  For a list of Account Types, see the Account Types Listing at the end of this document.              

 

 

General Ledger Actual Postings

The following sample is what will be exported when using the G/L Exchange “Export Account Data” feature to export G/L Actual Postings information.  The numbers correspond with the applicable column when exporting to a delimited format file

 

Note:  There are only 6 columns shown to depict columns 4-18.  This is due to space considerations.

 

 

1

2

3

4-18

100000000

Cash on Hand

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101010000

Cash in Bank-Reg. Checking

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101020000

Cash in Bank-Payroll

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101030000

Cash in Bank-Savings

01

0.00    0.00    0.00    0.00    0.00     0.00   0.00

 

 

Explanation of Columns

Column 1 - Represents the G/L Account Number.  This field should always have 9 characters and will always export with 9 characters, even if the user’s account numbers in Mas90 may actually be less than 9 characters.

 

Column 2 - Represents the Account Description.  Maximum of 30 characters.

 

Column 3 - Represents the Account Type. *  This field should always have 2 characters and always exports as 2 characters.  For a list of Account Types, see the Account Types Listing at the end of this document.

Column 4 – 18 - Represents the Beginning Balance for the given account.  Columns 5 – 17 represent the net change for each accounting period (up to 13 period) .

 

 

General Ledger Budget

The following is a sample of how a source file must be formatted when importing in G/L Budget information using the g/l Exchange “Import Account Data”.  The following sample is also what will be e

exported when using the G/L Exchange “Export Account Data” feature to export G/L Budget information. 

 

 

1

2

3

4-18

100000000

Cash on Hand

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101010000

Cash in Bank-Reg. Checking

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101020000

Cash in Bank-Payroll

01

0.00    0.00    0.00    0.00    0.00    0.00    0.00

101030000

Cash in Bank-Savings

01

0.00    0.00    0.00    0.00    0.00     0.00   0.00

 

 

Explanation of Columns

Column 1 - Represents the G/L Account Number.  This field should always have 9 characters and will always export with 9 characters, even if the user’s account numbers in Mas90 may actually be less than 9 characters.

 

Column 2 - Represents the Account Description.  Maximum of 30 characters.

 

Column 3 - Represents the Account Type. * This field should always have 2 characters and always exports as 2 characters.  For a list of Account Types, see the Account Types Listing at the end of this document.              

 

Column 4-18 -Represents the Beginning Balance for the given account.  Columns 5 – 17 represent the net change for each accounting period (up to 13 period) .

 

 

 

 

G/L Exchange, Import or Export Transaction Layout

The following is a sample of how a source file must be formatted when importing in G/L  Detail Transaction information using the G/L Exchange “Import Transactions”.  The following sample is also what will be exported when using the G/L Exchange “Export Transactions” feature to export G/L Detail Transactions.

 

 

1

2

3

4

5

6

7

8

9

101010000

Cash in Bank-Reg. Cking

01/31/01

CD

0010

000

GL

Payable/Monthly Payments

-172746.27

101010000

Cash in Bank-Reg. Cking

01/31/01

CR

0001

000

GL

A/RCOL/Monthly A/R Collections

398343.91

101020000

Cash in Bank-Payroll

01/17/01

PR

0008

000

GL

P/R Payroll Journal PE: 01/17/01

-15141.11

101020000

Cash in Bank-Payroll

05/31/01

PR

0094

000

GL

WHSP/R/Payroll Deposit

37384.66

101030000

Cash in Bank- Savings

01/31/01

JE

0007

000

GL

MTHINT/Monthly Savings Int.

3983.12

105000100

Cash in Bank- Savings

05/31/01

PR

0086

000

GL

PAYADJ/Payroll Adjustment

46000.00

105000100

Acct. receiv.-East  Whs

01/31/01

CR

0001

000

GL

A/RCOL/Monthly A/R Collection

-276951.16

105000100

Acct. receiv.-East  Whs

01/31/01

SJ

0013

000

GL

SLSCST/Monthly Sales & Costs

268516.62

 

 

Explanation of Columns

Column 1 - Represents the G/L Account Number.  This field must always have 9 characters and will always export with 9 characters, even if the user’s account numbers in Mas90 may actually be less than 9 characters.

 

Column 2 - Represents the Account Description.  Maximum of 30 characters.

 

Column 3 - Represents the Transaction Date

 

Column 4 -Represents the Source Journal Code. (CD=Cash Disbursements, CR=Cash Receipts, PR=Payroll, JE=Journal Entry, etc.) For a full list of codes print the source Journal Code Listing from Source Journal Code Maintenance on the G/L Setup Menu.

 

Column 5 - Represents batch # or journal #. MAS90 allows a maximum of 4 characters for this field.

 

Column 6 - Represents sequence counter assigned to the record.  MAS90 allows 3 characters for this field.

 

Column 7 - Represents the Source System (GL,AP,AR, etc).  When using G/L Exchange, the Source System will always be “G/L”              

 

Column 8 - Represents the Transaction Description.

 

Column 9 – Represents the Transaction Amount.  


List of Account Types

 

 

*  Account Type                         Description                   Break Type

 

01 Cash Asset

02 Net A/R Asset

03 Other A/R Asset

04 Inventory Asset

05 Prepaid Expenses Asset

06 Fixed & Other Assets Asset

07 Current Liabilities Liability

08 Long Term Liabilities Liability

09 Stockholders Equity Equity

10 Retained Earnings Equity

11 Net Sales Revenue

12 Cost of Sales Cost of Sales

13 Operating Expense X expense

14 Interest Expense X expense

16 Other Income & Expense Other Income & Expense

17 Income Tax Income Tax

18 Interest Expense Other Income & Expense