Contacting Support

We’re currently experiencing service issues for contacting Support. Please use the Help & How-To Center while we work through these issues.

The Spreadsheet Import Wizard makes it easy to import your QuickBooks clients' account balances from a spreadsheet file created by QuickBooks Pro (including the Premier Accountant and Enterprise editions for versions currently supported by Intuit) or QuickBooks Online.

Recommended account setup in QuickBooks

Although QuickBooks does not require account numbers (only account descriptions), if you will be importing the client's balances into Accounting CS, we recommend that you instruct your client assign account numbers to all GL accounts in QuickBooks. The import will work even if an account number is not assigned to each account, but there will be some additional mapping steps involved.

Steps in QuickBooks

1. From within your QuickBooks application, choose Reports > Accountant & Taxes > Trial Balance.
2. In the Dates field, select the date range for the data that you will import into Accounting CS.
3. Click the Excel button on the toolbar and choose Create New Worksheet.
4. Choose the Create new worksheet option (new or existing workbook) in the Send Report to Excel dialog, and then click the Export button.
5. When the spreadsheet opens in Excel, save the file in a location that you can later access to import into Accounting CS and be sure that it is not password protected.

   Note: Be sure that the spreadsheet is closed and remains closed during the import process.

To import subaccounts correctly and to avoid account duplication in Accounting CS, be sure to mark the Show lowest subaccount only checkbox in QuickBooks. The checkbox is in the Company Preferences tab of the Edit > Preferences screen when Accounting is selected in the left pane. Note that the Show lowest subaccount only checkbox is available only when an account number is assigned to every account in QuickBooks.

Selecting the source file in Accounting CS

1. Choose File > Import > Spreadsheet.
2. In the Source Data screen, select the appropriate client from the Client name field.
3. Select Account Balances from QuickBooks 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 and click Next.

Mapping spreadsheet columns

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.
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			At least Account Number or Account Description must be mapped.
   Account Description			At least Account Number or Account Description must be mapped.
   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)		-- -- -- -- --
   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	-- -- --

   Notes

   • In most cases, even if some or all of the QuickBooks accounts do not have account numbers, you may want to map the account name/description as an account number column. The application will map any QuickBooks account numbers as is and use the account name/description as the account number for any QuickBooks accounts that do not have an account number. If needed, you can modify the account number for any new accounts in screen; however, you cannot modify the account number for any accounts that already exist in Accounting CS.
   • 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.
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

The application displays the Data Mapping - Chart of Accounts screen, where you can map the appropriate Accounting CS account to the corresponding account in the spreadsheet.

In the Accounting CS Number and Description column for each row in the grid, select the corresponding Accounting CS GL account number. The drop-down list includes all GL accounts that were set up in the Setup > Chart of Accounts screen. If a corresponding account is not listed, select one of the following options.

• Add as is. Accounting CS adds the account as it is entered in the spreadsheet. Select a valid class code to add the account.

  Note: If you add an account as is, it must conform to the Chart of Accounts mask displayed above the grid. The application displays an error indicator next to each account that does not conform to the mask, and it will not allow you to continue with the import until all account numbers are valid. If you are importing a large number of accounts that may not conform to the mask, you may want to exit the import wizard and change the client's account mask. If the client uses long account descriptions or names that will be converted to account numbers, you may want to set up the client's account mask to use the maximum number of characters to ensure that the account numbers fit within the mask limitations.

  You can use the Error Navigation buttons below the mapping grids to jump to each error in the grid and correct the data. Show me.

  
• Do not import. Accounting CS does not import data for that account.

The Data Mapping screens may include the following fields and buttons.

Clear Mapping button

Reset default to

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 client's Chart of Accounts. If the spreadsheet includes data for any existing accounts, the application updates data for those account with the new information.
   • Zero existing balances and import new balances. The application adds new accounts and their balances to the client's 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

     Example

     The application adds the balance from the spreadsheet to the existing balance in the client data to create year-to-date balances. During the import process, Accounting CS creates an activity journal entry to store balances that are imported from the spreadsheet.

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

   • 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

     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. During the import process, Accounting CS creates an activity journal entry to store balances that are imported from the spreadsheet.

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

3. Click the Import button to begin the data import.

Reviewing import diagnostics

The Data Analysis screen displays a summary of the information that was imported from the spreadsheet and an explanation of the results for the imported data. Review the information. If necessary, you can click the Back button to make changes in the mapping and options screens. To print a simple report of the import results, click the Print button to open the Print Preview window, where you can view and print the report.

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

Assigning tax codes

After you import account balances from QuickBooks, you will need to manually assign tax codes to each account to use in your tax application. This is a one-time assignment for each account. In subsequent imports, you will need to do this for new accounts only.

1. Choose Actions > Enter Trial Balance.
2. Click the View Maintenance link in the upper-right corner of the screen.
3. In the View Maintenance dialog, select the appropriate view description, and then click the Edit button.
4. Highlight a blank row (denoted with an asterisk *) and select Tax Code from the drop-down list in the Column Type section.
5. Click Enter and then Done to return to the Enter Trial Balance screen.
6. For each account in the Enter Trial Balance screen, select the applicable classification code, classification subcode, and tax code.

   If a tax code has been assigned to a subcode, that application automatically enters the tax code when you select that subcode for new accounts only. If you select a new subcode for an existing account, the application does not automatically enter a tax code.

Sample spreadsheets

The following sample spreadsheet files 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.

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.

• Account balances from QuickBooks (with account and description)
• Account balances from QuickBooks (with descriptions only)

Was this article helpful?

Yes No

Submit