ProSeries Frequently Asked Questions

How do I use the Trial Balance Import feature of ProSeries to import from Microsoft Excel?

Products Affected:

 Print

In order to import an Excel data file into ProSeries using the Trial Balance Import (TBI) feature, you must modify the Excel file to a format TBI can recognize and use. TBI can recognize and use the Generic ASCII file format.

To create and modify an Excel file:

  1. Create an Excel spreadsheet with seven columns. (Refer to the ASCII File Format table below.)
    1. First column contains a Y or N
    2. Second column contains an account number up to 12 digits long
    3. Third column contains an account description up to 25 characters long
    4. Fourth, fifth, sixth and seventh columns contain the same right-justified whole number amount
    5. Delete any total and header rows, and other rows which do not contain plain data to be imported
  2. Set each column to the correct width. From the Format menu, point to Column, then Width. Enter the correct number and click OK.
  3. Set the cells to display whole numbers. From the Format menu, select Cells. On the Number tab, select Number and set Decimal Places to zero.
  4. After the spreadsheet is correctly aligned and formatted, go to the File menu and select Save As. Save as type Formatted Text (Space delimited) (*.prn). Save the file to any directory but make a note of where you save it and the file name.

    Excel may give you a warning that the workbook contains more than one sheet and only the active sheet will be converted. Click OK to convert just the active sheet.

    Excel may give you a warning that the workbook may contain some features that are not compatible with the converted file type. Click Yes to save the file and leave out features.

  5. In ProSeries, open the tax return for this imported data.
  6. From the Import menu, select Trial Balance Setup.
  7. In the Trial Balance Program drop-down list, select Generic ASCII Trial Balance File.
  8. Click Select File and use the Look in field to navigate to the directory where you saved your converted Excel file.
  9. Change the Files of type to All Files (*.*).
  10. The file you saved should have the extension .prn. Select the file and click Open.
  11. Make any further adjustments in the Trial Balance Setup window and click OK to save the settings.

The Generic ASCII file format is a text file with seven columns with this format and these specifications:

General information about the file:

  1. This file must contain each field, regardless of whether it contains any information.
  2. Each field must be the exact length indicated.
  3. Amounts should be right justified within the field.
  4. Any record that is less than 90 characters will be ignored.
  5. All amounts should be in whole dollars.
  6. Income, liabilities, and capital/equity accounts should be exported as negative amounts. If you have sales of 10000, it should be in the file as -10000. When the amounts are assigned in the tax program they will reverse on lines that expect these types of accounts to be assigned.
    Note: To change the sign of a linked account press CTRL+SHIFT+R.

ASCII File Format:

Field name Type Column start Width Decimals
PLCODE Character 1 1  
ACCTNO Character 2 12  
ACCTDESC Character 14 25  
ABAL Numeric 39 13 0
FBAL Numeric 52 13 0
SBAL Numeric 65 13 0
OBAL Numeric 78 13 0

PLCODE: One character code, Y or N, indicating if the account is a Profit and Loss account (Y) or a Balance Sheet account (N).

ACCTNO: General Ledger account number - Maximum of 12 characters.

ACCTDESC: General Ledger account description - Maximum of 25 characters.

ABAL, FBAL, SBAL, OBAL: Amount to be imported - Maximum of 13 characters, no decimals allowed.

FIELD NAME FIELD DESCRIPTION
PLCODE Profit/Loss Code - PLCODE must contain a Y or N to indicate if the account is an income or expense account (marked with a "Y") or if it is a balance sheet account (marked with an "N"). A record showing the net income may be included with a PLCODE of "*". If a net income record is not included in the file, the program will create a net income record for tax purposes by summing all accounts with PLCODE equal to "Y".
ACCTNO Account Number - This field supports alpha/numeric account numbers. Each account number must be unique -- no duplicates are allowed. If two accounts have the same account number, both accounts will be imported and visible in the ProSeries link dialogue box, but there will be problems when attempting to link to one of the accounts.
ACCTDESC Account Description - A description of the account.
ABAL Adjusted Book Balance - This is the field used to record the adjusted book balances. If the Trial Balance/General Ledger program supports one set of books, use this field for account balances. You may also want to set the same value for the other amount fields. That way, no matter what basis the user selects in ProSeries, the same numbers will always be shown.
FBAL Federal Tax Balance - This is the field used to record the federal tax balances.
SBAL State Tax Balance - This is the field used to record the state tax balances.
OBAL Other Balance - If the program supports an additional set of books, the balances should be recorded to this field.

Last updated: 12/11/2006

Did this information help you?
Yes    No
Does Not Apply
    Comments about this page:
  Note: You will not receive a personal response to your comment.
©2012 Intuit Inc. All rights reserved