Spreadsheet import - chart of accounts

Show expandable text

We moved!

Help articles have been migrated to the new Help and Support. You can find help for your products and accounts, discover FAQs, explore training, and contact us!

Use the Spreadsheet Import wizard to import chart of accounts data (account number, account description, account grouping, and balance information) from a spreadsheet file using .XLS or .XLSX file format. You can use this method to add new accounts to a client's chart of accounts or to update their existing chart of accounts.

To import data from a Creative Solutions Accounting (CSA) report, you can export the report information to a spreadsheet, and then import data from the spreadsheet into Workpapers CS.

Be sure that the spreadsheet is not password protected, closed, and remains closed during the import process.

For non-segmented clients that have accounts with subcodes, you must format the spreadsheet column as text.

A client with an account mask of xxx-xx appear as 100 instead of 100-00 if the formatting for the column is set up to display as two decimals. Sub-accounts are retained when the column is formatted as a text column.

Selecting the source file

In the first screen of the wizard, select the spreadsheet file and worksheet (tab) to import.

  1. Choose File > Import > Spreadsheet.

    Note: To include subcodes for a non-segmented client in the spreadsheet import you must format columns as text columns in Microsoft Excel. Example

    Example

    A client with an account mask of xxx-xx appears as 100 instead of 100-00 if the formatting for the column is set to display as two decimals. Sub-accounts are retained when the column is formatted as a text column.

  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select Chart of Accounts from the drop-down list in the Data type field.
  4. In the Import File section, enter the path and filename of the spreadsheet file to import, or click the Browse button to navigate to the file.
  5. Select the worksheet within the spreadsheet file to import.
  6. Click Next.

Mapping spreadsheet columns

In the Column Mappings screen, the grid displays data from the selected spreadsheet. Use this screen to map the spreadsheet columns to specific data fields in Workpapers CS.

  1. If you saved mapping information from a prior import as a mapping template, that template will be included in the drop-down list in the Template field. If applicable, select the appropriate template.
  2. If the spreadsheet includes column headings or other rows of data that should not be imported, mark the checkbox in the Omit row column for that row. The application will not validate or import data in that row.
  3. For each column, click the column heading in the grid, and then select the applicable mapping item from the drop-down list in the Column <x> field above the grid.
    Mapping item Additional info Additional info 2 Required?
    Account Number Yes
    Account Description --
    Account Grouping

    Account Classification Code
    Account Classification Subcode
    Leadsheet Schedule Code
    Leadsheet Schedule Subcode

    (includes Code and Subcode for each account grouping set up for the client)

    Yes
    --
    --
    --

    Tax Information

    Tax Code
    Tax Code Unit
    M-3 Tax Code

    --
    --
    --

    Beginning Balance <Year>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Unadjusted Balance <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Adjusted Budget <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 3 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 4 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 5 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Note: If you map any budget columns and select the year option (as opposed to a specific period-end date) in the second Column <x> field, the application will equally distribute the amount among all periods within the current year. (For example, if you import a balance of $1,200 for a monthly client and select 2015, the application will import a $100 balance in each period for the account.) If you select a specific period end date from the second Column <x> field, the application will import the full budget amount into the selected period only. Show me.

    Budget column mapping
  4. After you have mapped all applicable columns, click Next.
  5. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Mapping additional data types

Workpapers CS requires a code for the Account Classification account grouping for all new accounts. If your spreadsheet does not include a classification code, the application opens the Classification Assignment screen and lists all new account numbers.

Use this screen to assign a code to each new account. If applicable, you can also assign subcodes to each account. The classification codes and subcodes listed in this screen were set up in the Setup > Account Groupings screen.

  1. In the Classification Code column for each account, select the appropriate classification code.
  2. In the Classification Subcode column, select the appropriate subcode for each account, if applicable.
  3. Click Next.

Choosing import options

The Import Options screen opens only if you mapped at least one balance-based column. Use this screen to specify how the spreadsheet data should be imported.

  1. Choose the applicable Chart of Accounts import option.
    • Append to existing chart. The application adds new accounts and their balances to the existing Chart of Accounts. If the spreadsheet includes data for any existing accounts, the application updates data for those accounts with the new information.
    • Zero existing balances and import new balances. The application adds new accounts and their balances to the Chart of Accounts. If the spreadsheet includes data for an existing account, the application zeroes the balances for that account for the selected dates, and imports only the balances in the spreadsheet file. If the spreadsheet does not include data for an existing account, the application zeroes the balances for that account.
  2. Choose the applicable balance import option.
    • Current period balances. The application imports the balances from the spreadsheet directly into the period selected for the balance column.

      Example

      The application adds the balance from the spreadsheet to the existing balance in the client data to create year-to-date balances.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/15
      100.00
      100.00
      100.00
      200.00
      02/28/15
      200.00
      200.00
      200.00
      400.00
      03/31/15
      400.00
      300.00
      300.00
      700.00
      04/30/15
      700.00
      400.00
      400.00
      1100.00
      05/31/15
      1100.00
      500.00
      500.00
      1600.00
      06/30/15
      1600.00
      600.00
      600.00
      2200.00
      07/31/15
      2200.00
      700.00
      700.00
      2900.00
      08/31/15
      2900.00
      800.00
      800.00
      3700.00
      09/30/15
      3700.00
      900.00
      900.00
      4600.00
      10/31/15
      4600.00
      1000.00
      1000.00
      5600.00
      11/30/15
      5600.00
      1100.00
      1100.00
      6700.00
      12/31/15
      6700.00
      1200.00
      1200.00
      7900.00

      * During the import process, Workpapers CS creates an activity journal entry to store balances that are imported from the spreadsheet.

    • Year-to-date balances. The application calculates current-period activity based on existing prior-period balances and creates a journal entry with the net change for the period.

      Example

      The balance in the spreadsheet data should be the current year-to-date balance in the client data. The application calculates the difference between the current year-to-date balance in the client data and the balance in the spreadsheet data.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/15
      100.00
      100.00
      0.00
      100.00
      02/28/15
      100.00
      200.00
      100.00
      200.00
      03/31/15
      200.00
      300.00
      100.00
      300.00
      04/30/15
      300.00
      400.00
      100.00
      400.00
      05/31/15
      400.00
      500.00
      100.00
      500.00
      06/30/15
      500.00
      600.00
      100.00
      600.00
      07/31/15
      600.00
      700.00
      100.00
      700.00
      08/31/15
      700.00
      800.00
      100.00
      800.00
      09/30/15
      800.00
      900.00
      100.00
      900.00
      10/31/15
      900.00
      1000.00
      100.00
      1000.00
      11/30/15
      1000.00
      1100.00
      100.00
      1100.00
      12/31/15
      1100.00
      1200.00
      100.00
      1200.00

      * During the import process, Workpapers CS creates an activity journal entry to store balances that are imported from the spreadsheet.

  3. Click Import to begin the data import.
  4. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Note: The maximum amount limit when importing balances into the Chart of Accounts via the spreadsheet import is 999,999,999.99.

Reviewing import diagnostics

The Data Analysis screen displays a list of the information that will be imported from the spreadsheet and the analysis results for the data. If necessary, you can click the Back button to make changes to any of the mapping and options screens. To view a diagnostic report for any of the items listed, mark the checkbox next to that item, and then click the Preview Selected or Print Selected button.

When you are satisfied with the data that will be imported, click the Finish button.

The Import Complete screen displays a summary of the information that was imported from the spreadsheet. Review the diagnostic messages. You can click the Print button to display a simple report of the import results, or you can click the Close button to close the Spreadsheet Import wizard.

Below are some of the diagnostic messages you might see.

  • Number of accounts read: displays the number of accounts in the spreadsheet.
  • Number of new accounts added: displays the number of accounts added to Workpapers CS.
  • Number of accounts not matched in the import: displays a list of accounts that currently exist in Workpapers CS but were not included in the spreadsheet.
  • <date> Unadjusted balances successfully imported: informational message to let you know that Workpapers CS imported unadjusted balances for that date.
  • Number of tax codes imported (tax codes, tax units codes, M-3 tax codes): displays the number of tax codes imported into Workpapers CS. Note that you may see this message even if you did not include a tax codes column in your spreadsheet, if you mapped both a classification code and subcode. The application assigns tax codes based on the account mappings in the Setup > Firm Information > Standard Account Mappings screen.

Note: The application creates an activity journal entry using the reference AA99 in the Actions > Enter Transactions screen. If the spreadsheet includes data for multiple periods, the application creates one AA99 entry for each period.

Sample spreadsheets

The following sample spreadsheets are available for you to download and review. The sample spreadsheets are set up with commonly used columns and sample data. You can modify the formatting, column, and data to fit your needs.

Note: If you import the sample data into a live client record, please remember to delete the imported data when you are finished.

Click a link below to open the spreadsheet file, and then save the file to the location specified in the Spreadsheet field in the Import Data tab of the Setup > File Locations dialog.

  • Chart of Accounts (with current-year and prior-year balances)
  • Chart of Accounts (with type and tax code)

    Note: Because the Type column cannot be imported, leave this column as Not Used.

Was this article helpful?

Thank you for the feedback!