Import from ASCII or Excel

You can import the chart of accounts, general ledger balances, and adjusting entries from ASCII or Excel files so that you can proceed with your compilation, review, or audit.

Note the following limitations before importing from ASCII or Excel:

  • Maximum Excel record length: 15000 rows
  • Maximum Excel transaction amounts: 4 years prior
  • Maximum journal source types: 200
  • Invalid characters in account numbers will be replaced automatically.
  • Duplicate account numbers will be affixed with _###, where ### represents a value from 001 to 999.

The following table details the character limits of specific fields in the file:

Field Character limit
Account Description 50
Account Number, Entity Abbreviation, Split-Account Number 40
Cell (Excel) 1024
Division, Sub-Account Number 6
Entry Description 280
Entry Number 20
Entry Type, Misstatement 80
Extended Account Description, Formula 510
Journal Description 35
Journal Source 2
Tax Identifier 4

Import from ASCII

You can import client data from an ASCII text file. If you want to import more than one major group of components, you must perform the imports separately. You can import from both delimited or fixed length files.

To import data from an ASCII file:

  1. On the ribbon, click Engagement | Import | ASCII Text File.... The ASCII Text File Import Wizard displays.

  2. On the Welcome screen, select the applicable options for your import:

    Option Description
    Components to import Select whether to import the Chart of Accounts and GL Balances, GL Details, or Adjusting Journal Entries.
    ASCII file to import Browse for the ASCII file that you want to import.
    Record layout file If applicable, browse for a record layout file if you want to use settings from a previous import.
    Period Date Sequence for importing period balances Select the reporting period that corresponds to the account balances. These reporting period options are set up in the file's Engagement Properties.

    Click Next.

  3. On the File Type screen, select whether the file type is Delimited or Fixed length. A selection is made automatically based on the data in the file. Click Next.
    1. For fixed length files, the Specify Record Length screen displays with an automatically calculated record length. If required, you can adjust the record length and layout. Click Next.
    2. On the Specify Field Delineators screen, set the boundaries for each field in the file. Assumed boundaries are set automatically, however you can modify them if required. Click Next.
    3. When importing Adjusting Entries, specify the default properties for your entries. These properties will be used for the imported entries unless specified otherwise. Click Next.
  4. On the Specify Data Properties screen, select which separator to use when determining the boundaries between fields and which rows to exclude from the file. Select the text qualifier to ignore when importing. Click Next.
    1. When importing Adjusting Entries from delimited files, specify the default properties for your entries. These properties will be used for the imported entries unless specified otherwise. Click Next.
  5. On the Field Details screen, define the file's columns. Above the column you want to define, click Ignore, then select the appropriate heading. You must define one column as the Account Number column to proceed. If you define a column as a Balance, you can specify whether the column displays the Debit field, the Credit field, or both. For a Credit field, select Positive imports as credit if there are balances that contain positive or mixed (positive and negative) values. Click Next.
  6. On the Advanced Specifications screen, you can further refine how Working Papers imports the file.
  7. Confirm which accounts will be added to the file. If changes need to be made click Back and update accordingly. Otherwise, click Next.
  8. You have completed the Import Wizard. You have the option to save the layout settings you selected in the wizard, and can reuse them next time you import. To finalize the Import Wizard, click Import.

The data is imported to your Working Papers file.

Import from Excel

You can import client data from an Excel file. If you want to import more than one major group of components, you must perform the imports separately.

To import data from an Excel file:

  1. On the ribbon, click Engagement | Import | Excel File.... The Excel File Import Wizard displays.

  2. On the Welcome screen, select the applicable options for your import:

    Option Description
    Components to import Select whether to import the Chart of Accounts and GL Balances, GL Details, or Adjusting Journal Entries.
    Excel file to import Browse for the Excel file that you want to import.
    Select a worksheet Select the worksheet containing the components you want to import.
    Record layout file If applicable, browse for a record layout file if you want to use settings from a previous import.
    Period Date Sequence for importing period balances Select the reporting period that corresponds to the account balances. These reporting period options are set up in the file's Engagement Properties.

    Click Next.

  3. On the Specify Data Properties screen, select which separator to use when determining the boundaries between fields and which rows to exclude from the file. Select the text qualifier to ignore when importing. Click Next.
  4. When importing Adjusting Entries, specify the default properties for your entries. These properties will be used for the imported entries unless specified otherwise. Click Next.
  5. On the Field Details screen, define the worksheet's columns. Above the column you want to define, click Ignore, then select the appropriate heading. You must define one column as the Account Number column to proceed. If you define a column as a Balance, you can specify whether the column displays the Debit field, the Credit field, or both. For a Credit field, select Positive imports as credit if there are balances that contain positive or mixed (positive and negative) values. Click Next.
  6. On the Advanced Specifications screen, you can further refine how Working Papers imports the file.
  7. Confirm which accounts will be added to the file. If changes need to be made click Back and update accordingly. Otherwise, click Next.
  8. You have completed the Import Wizard. You have the option to save the layout settings you selected in the wizard, and can reuse them next time you import. To finalize the Import Wizard, click Import.

The data is imported to your Working Papers file.

Advanced specifications

On the Advanced Specifications screen, you can specify file and regional settings for the import. The available settings depend on which components you are importing.

Chart of accounts & general ledger balances

Option Description
Default Account Type Select the default account type to assign accounts.
Accumulate Balances Select to combine the totals of accounts that display on multiple lines in the ASCII or Excel file. If there is an existing balance in the account prior to the import, it is overridden. This option works for prior, budget, and forecast balances as well as current balances.
Clear Balances of Period(s) Being Imported

Select to clear the period balances of all existing financial accounts in the imported data prior to import.

By default, this option only clears balances for entities included in current import, and other entities will not be affected.

Notes:

  • If this option is selected along with the Balances being imported are year-to-date option, only the balances of the period being imported will be cleared (e.g. Prior period balances will be ignored).
  • If you select this option along with the Balances being imported are year-to-date option and the year-to-date balance does not appear to be correct in the Working Trial Balance, make sure you have specified the opening and continuous period balances in the Field Details dialog.
  • If you want to apply this option to a specific entity, the imported data should contain at least one account from that specific entity.
Round Balances Select to round the Trial Balance upon import. Click Settings to specify the rounding type and the accounts where you want to apply rounding.
Import
  • All Accounts: Import all accounts, including those with zero balances.
  • Accounts with Non-Zero Total Balance: Import all accounts that have a non-zero balance during the specified period.
  • Accounts with Opening Balance/Period Movement: Import all accounts that have a non-zero opening balance or transactions during the specified period.

Specify the period(s) from which to import accounts:

  • Current Year
  • Prior Year 1
  • Prior Year 2
  • Prior Year 3
  • Prior Year 4
Use currency format in Regional Settings

Deselect to override the workstation's regional settings.

When using regional settings and working in any region that uses a (,) to separate units of currency, the (.) on the numeric keyboard is translated to a (,) on the screen.
Decimal Symbol

Select either the period (.) or the comma (,) to separate units of currency.
Digit Group Symbol

Select either the comma (,) or the period (.) to group the digits in large currency values.
Translate from DOS to Windows file Select if the ASCII or Excel file was originally exported from a DOS based application. This option automatically translates any special characters between DOS and Windows formats.
Balance Type Select the balance type from a balance, budget, or forecast from the prior 5 years (or future 5 years for forecasts).
Positive Value Descriptor (Optional) Enter a symbol to denote positive values.
Negative Value Descriptor (Optional) Enter a symbol to denote negative values.
Balances being imported are year-to-date Select if the balances you are importing are year-to-date numbers only.
Balances include adjustments

Select when importing year-to-date amounts to ensure that any existing adjusting entries in the file are taken into account when calculating the period movement amounts.
Import YTD balances for subsequent periods

Select if you are importing year-to-date balances to a range of periods within the current period date sequence, and you do not want reversing transactions to be added to subsequent periods' year-to-date balance.

If you select this option and you are importing into a blank file, all subsequent periods will have the same year-to-date balance as the last period imported.
Only Balance Sheet Accounts have year-to-date balances Select if only the Balance Sheet account numbers are year-to-date and all other accounts should be imported as per the preferences set above.
Reverse period closing to Retained Earnings account

Select in the following situations:

  • With any unconsolidated client.
  • With a consolidated client when using the abbreviated entity option in the Advanced | Entity tab to generate the entity. In this case, Working Papers assumes the Retained Earnings account resides in the top entity.
  • With a consolidated client when using the divisional profile option in the Advanced | Entity tab to assign the entity. Working Papers assumes the Retained Earnings account is in the head entity of the sub-branch selected for import.

When importing periods where the income statements accounts have already been closed to the Retained Earnings account, this option can be used to reverse the prior period's net income out of the Retained Earnings account. The period movement amount for the Income Statement accounts import for each period while the Balance Sheet account's amounts are imported as year-to-date. Working Papers calculates the amount and manually adjusts for this amount in the Retained Earnings account.
R/E Account Number

Enter the account number of the retained earnings account to use to reverse the above entries.

General ledger details

Option Description
Clear Opening Balances
  • Financial: Clear the opening balances of financial accounts.
  • Cash Flow: Clear the opening balances of cash flow accounts.
  • Performance Measures: Clear the opening balances of accounts that measure performance.
Clear Journals of Source Type
  • Financial: Overwrite existing financial entries with imported data.
  • Cash Flow: Overwrite existing cash flow entries with imported data.
  • Performance Measures: Overwrite existing performance entries with imported data.
Use currency format in Regional Settings

Deselect to override the workstation's regional settings.

When using regional settings and working in any region that uses a (,) to separate units of currency, the (.) on the numeric keyboard is translated to a (,) on the screen.
Decimal symbol

Select either the period (.) or the comma (,) to separate units of currency.
Digit group symbol

Select either the comma (,) or the period (.) to group the digits in large currency values.
Positive Value Descriptor (Optional) Enter a symbol to denote positive values.
Negative Value Descriptor (Optional) Enter a symbol to denote negative values.
Translate from DOS to Windows File Select if the ASCII or Excel file was originally exported from a DOS based application. This option automatically translates any special characters between DOS and Windows formats.

Adjusting journal entries

Option Description
Use currency format in Regional Settings

Deselect to override the workstation's regional settings.

When using regional settings and working in any region that uses a (,) to separate units of currency, the (.) on the numeric keyboard is translated to a (,) on the screen.
Decimal symbol

Select either the period (.) or the comma (,) to separate units of currency.
Digit group symbol

Select either the comma (,) or the period (.) to group the digits in large currency values.
Positive Value Descriptor (Optional) Enter a symbol to denote positive values.
Negative Value Descriptor (Optional) Enter a symbol to denote negative values.
Translate from DOS to Windows File Select if the ASCII or Excel file was originally exported from a DOS based application. This option automatically translates any special characters between DOS and Windows formats.
Clear previously imported adjusting entries Select to clear previously imported adjusting entries from the destination file.
Accumulate adjusting amount Select to accumulate the adjusting amounts between the imported data and existing data in the destination file.