Tax & Accounting Blog

How to Perform a Customer Spreadsheet Import in Accounting CS

CS Professional Suite Accounting & Auditing Solutions December 5, 2013

Accounting CS iconYou can use the Spreadsheet Import wizard to import customer data (including beginning accounts receivable balances, if applicable) from a spreadsheet file. This is a good way to add new customer records to a client record or to update existing customer records. During the import process, you will map the spreadsheet columns to data in Accounting CS. The application verifies the data based on the specified mappings. 

Note:  You will need to close the spreadsheet file before starting the import process. 

Before you start the wizard: 

  • If you are importing the following types of customer information, you may want to set up that information in Accounting CS before you begin the import. Otherwise, you can add this information on the fly during the import.
    • Customer payment terms = Payment terms
    • Finance charge information = Finance charges
    • Shipping information = Shipping type invoice items
  • If you add sales tax invoice items on the fly during the import, you must first set up global sales tax information in Accounting CS.
  • The application stops reading the spreadsheet data when it encounters a blank row in the spreadsheet. So before you start the import process, verify that there are no blank rows between customer records in the spreadsheet.
  • If the application encounters any issues in the Column Mappings screen and/or any of the Data mapping screens, it displays an Accounting CS error icon icon next to the invalid items. In the Error Navigation section below the mapping grids, the application displays the total number of errors found. Use the navigation buttons to jump to each error and correct the data.

Selecting the source data:

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select Customers 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 to import.
  6. To have the spreadsheet import wizard skip all data mapping screens for the additional data types, mark the Skip data mapping screens checkbox. The application will skip all data mapping screens unless it requires input from you.
  7. Click Next.

Mapping spreadsheet columns 

In the Column Mappings screen, the grid displays data contained in 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. Note that you must do this before you map any columns; otherwise the template will not be saved.
    • 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. Map each spreadsheet column by specifying the column title (type) for each column in the grid. Click the column heading in the grid, then select the applicable mapping item from the drop-down list in the Column field above the grid.
    • To exclude a row of data from the import, mark the checkbox in the Omit row column for that row. The application will not validate or import data in that row.
    • You must map columns for the Customer ID and Customer Name.
  3. After you have mapped each applicable column, click Next.

Mapping additional data types 

Depending on the columns you mapped in the Column Mappings screen, the application may display the following Data Mapping screens, where you can select the Accounting CS data to correspond with the spreadsheet data.

  • Customers
  • Chart of Accounts
  • Payment Terms
  • Finance Charges
  • Shipping Items
  • Sales Tax Items

In each Data Mapping screen, map the spreadsheet items to specific data in the application. The Data Mapping screens may include the following fields and buttons.

  • Clear mapping button
  • Show all mappings
  • Reset to default

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. To view a diagnostic report for any of the items listed, mark the checkbox next to that item and click the Preview Selected or Print Selected button.

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