Tax & Accounting Blog

Using the Vendor Spreadsheet Import Wizard in Accounting CS

CS Professional Suite Accounting & Auditing Solutions November 7, 2013

The Spreadsheet Import wizard makes it easy to import vendor data, including beginning balances for accounts payable, from a spreadsheet file into Accounting CS. You can use the wizard to add new vendor records to a client record or to update existing vendor 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.

Before you start the wizard:

  • To import direct deposit information, you must set up bank information in Accounting CS before  starting the import process.
  • To import vendor payment terms, you may want to set up payment terms in Accounting CS before starting the import process. You can also add payment terms on the fly during the import.
  • The application stops reading the spreadsheet data when it encounters a blank row in the spreadsheet. So before you start the import process, make sure that there are no blank rows between vendor records in the spreadsheet.
  • If Accounting CS encounters any issues in the Column Mappings screen and/or any of the data mapping screens, it will display an Accounting CS error icon icon next to the invalid items. Accounting CS will display the total number of errors found in the Error Navigation section below the mapping grids. Use the navigation buttons to jump to each error and correct the data.

    Accounting CS Error Navigation

  • You’ll need to close the spreadsheet file before starting the import wizard. 

Here’s how to use the Spreadsheet Import wizard: 

Select the source data

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the client from the drop-down list in the Client Name field.
  3. Select vendors 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 and navigate to the file.
  5. Specify the worksheet within the spreadsheet to import.
  6. To have the spreadsheet import wizard skip all data mapping screens for the additional data types unless it requires input from you, mark the Skip data mapping screens checkbox.
  7. Click Next.

Map the spreadsheet columns 

The grid in the Column Mappings screen 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.
  2. Map each spreadsheet column by specifying the column title (type) for each column in the grid. Click the column heading in the grid, and 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 theOmit rowcolumn for that row. The application will not validate or import data in that row.
    • You must map a column for the vendor name.
    • If you are importing direct deposit allocations, you must map columns for bank routing number, bank account, account type, and amount or percent. To map these columns, selectDirect Deposit Allocationfrom the first column field, then select the allocation item from the secondary column field.

      Spreadsheet Import Wizard

  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, Accounting CS may display the following data mapping screens, where you can select the Accounting CS data that corresponds with the spreadsheet data.

  • Bank routing number – For each row in the grid, select the corresponding Accounting CS bank and routing number. The drop-down list includes banks that were set up in the Setup > Firm Information > Banks screen.
  • Chart of accounts – For each row in the grid, select the corresponding GL account number and description. If a corresponding account is not listed in the Setup > Chart of Accounts screen,select Add as is and then enter a valid account number and class code to add the account, or select Do not import to skip the import of data for that account.
  • Vendors – For each row in the grid, select the corresponding Accounting CS vendor. If the vendor is not listed in the Setup > Vendors screen, you can add the vendor by selecting Add as is.
  • 1099 items

For each row in the grid, select the corresponding 1099 item.

If the application encounters a blank field in the spreadsheet, and that blank field is mapped to a required field, the application enters Do not import in that field by default. To change the default information to Add as is or to a blank field for any of these fields that you have not changed, select the desired default value from this field.

Review the 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.