Tax & Accounting Blog

Importing Data from a QuickBooks Spreadsheet into Accounting CS or Workpapers CS

CS Professional Suite Accounting & Auditing Solutions June 4, 2014

The Spreadsheet Import Wizard in Accounting CS and Workpapers CS makes it easy to import account balances from a QuickBooks®-generated spreadsheet file, even if you don’t have QuickBooks installed at your firm. During the import process, you will map each spreadsheet column from the QuickBooks file to data fields in Accounting CS or Workpapers CS. The application will then verify the data based on the specified mappings.

Selecting the Source File

  1. Close the spreadsheet file before starting the import process.
  2. Choose File > Import > Spreadsheet to open the Spreadsheet Import Wizard.
  3. In the Data screen of the wizard, select the appropriate client from the Client Name field.
  4. Select Account Balances from QuickBooks from the Data Type drop-down list.
  5. Enter the filename and path of the spreadsheet file to import, or click the Browse button to navigate to the file.
  6. Select the worksheet in the spreadsheet file to import. The drop-down list in the Worksheet to import field includes all worksheets in the selected spreadsheet file.
  7. Click Next.

Mapping Spreadsheet Columns

In the Column Mappings screen of the wizard, the grid displays data from the selected spreadsheet. Use this screen to map the spreadsheet columns to specific data fields in Accounting 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.
    • To save mapping information as a template, right-click in the Template field, choose Add Template from the context list, and then enter the template name in the dialog that opens.
    • To change the name of a mapping template, select the template to rename, right-click in the Template field, choose Edit Template from the context list, and then enter the new mapping template name.
    • To delete a saved mapping template, select the template to delete, right-click in the Template field, and choose Delete Template from the context list.
  2. Specify the column title (type) for each column in the grid by clicking the column heading in the grid and then selecting the applicable mapping item from the drop-down list in the Column field above the grid.
  3. After you have mapped each applicable column, click Next.
  4. Accounting CS validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Notes

  • To exclude a row of data from the import, mark the checkbox in the Omit row column for that row. Accounting CS will not validate or import data in that row.
  • If you select the Year option in the second Column <x> field when importing budget amounts, Accounting CS will distribute the amount equally among all periods within the current year. For example, if you import a balance of $1,200 for a monthly client and select 2012, 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, Accounting CS will import the full budget amount into the selected period only.

Mapping Account Numbers to the Chart of Accounts

In the Data Mapping – Chart of Accounts screen, the grid displays account number data from the selected spreadsheet. Use this screen to map the account numbers and class codes from the QuickBooks spreadsheet to the corresponding account in Accounting CS.

If a corresponding account does not exist in Accounting CS, enter the correct account number in the Account Number column and select <Add as is> from the drop-down list in the Number and Description column.

Next, use the Import Options screen of the wizard to specify how the spreadsheet data should be imported.

  1. Choose the applicable Chart of Accounts import option. The following options are available:
    • Append to existing chart. Accounting CS adds new accounts and their balances to the Chart of Accounts. If the spreadsheet includes data for any existing accounts, Accounting CS updates data for those accounts with the new information.
    • Zero existing balances and import new balances. Accounting CS adds new accounts and their balances to the Chart of Accounts. If the spreadsheet includes data for an existing account, Accounting CS 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, Accounting CS zeroes the balances for that account.
  2. Choose the applicable balance import option. The following options are available:
    • Current period balances. Accounting CS imports the balances from the spreadsheet directly into the period selected for the balance column.
    • Year-to-date balances. Accounting CS calculates current-period activity based on existing prior-period balances and creates a journal entry with the net change for the period.
  3. Click the Import button.

Reviewing Import Diagnostics

Review the summary of the imported data in the Import Diagnostic screen. If necessary, you can click the Back button and make changes to the column mapping, classification code and subcode assignments, and import options and then re-import the data. Click the Finish button to complete the import.