Spreadsheet import - vendor data

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 vendor data (including beginning accounts payable balances, if applicable) from a spreadsheet file using .XLS or .XLSX file format. You can use this method to add new vendor records to a client record or to update existing vendor records.

Note: If you map a spreadsheet row to an existing Accounting CS vendor record, the application replaces most of the existing Accounting CS values with the spreadsheet value. However, the application will not replace several types of data. Instead it appends the spreadsheet data to the existing vendor record.

  • Vendor type
  • Distribution amount
  • Distribution description
  • Distribution GL account
  • Distribution 1099 item
  • 1099 Form/Box
  • AP BegBal Reference
  • AP BegBal PO#
  • AP BegBal GL Account
  • AP BegBal Date
  • DD routing number
  • DD Account Number
  • DD Account type
  • DD Amount
  • DD Percentage
  • DD status

Special information

  • If you are importing direct deposit information, you must first set up bank information in Accounting CS before you begin the import.
  • If you are importing vendor payment terms information, you may want to first set up payment terms in Accounting CS before you begin the import. Otherwise you can add them on the fly during the import.

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

Be sure that the spreadsheet is not password protected.

Selecting the source file

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the appropriate client from 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 to navigate to the file.
  5. Select the worksheet within the spreadsheet file 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

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 field above the grid.
    Mapping item Additional info Additional info 2 Required?
    ID Yes, if the same vendor name is used for more than one vendor.
    Vendor Name Yes
    EIN/SSN --
    Verified --
    DBA Name --
    Account --
    Vendor Type --
    Address Line 1 --
    Address Line 2 --
    City --
    State --
    Zip --
    County --
    Country --
    Phone Business
    Car
    Home
    Mobile
    Pager
    Other
    --
    --
    --
    --
    --
    --
    Fax --
    Email --
    Website --
    Payment Preferences Method --
    Distribution Amount --
    Distribution Description --
    Distribution General Ledger Account --
    Distribution 1099 Form/Box --
    Transaction Memo --
    1099 Form/Box --
    Accounts Payable General Ledger Account Yes, for AP clients
    Accounts Payable Beginning Balance Amount --
    Accounts Payable Beginning Balance Reference --
    Accounts Payable Beginning Balance PO# --
    Accounts Payable Beginning Balance AP Account Yes, if Accounts Payable Beginning Balance Amount is mapped.
    Accounts Payable Beginning Balance Date Yes, if Accounts Payable Beginning Balance Amount is mapped.
    Accounts Payable Payment Term --
    Accounts Payable PO# --
    Accounts Payable Memo --
    Direct Deposit Allocation Routing Number
    Account Number
    Account Type
    Amount

    Percent

    Status
    Yes, if Direct Deposit Allocation is mapped
    Yes, if Direct Deposit Allocation is mapped
    Yes, if Direct Deposit Allocation is mapped
    If Direct Deposit Allocation is mapped, EITHER Amount OR Percent (but not both) must be mapped.
    If Direct Deposit Allocation is mapped, EITHER Amount OR Percent (but not both) must be mapped.
    --

    Note: To import vendor GL account distributions to the Setup > Vendors > Distributions tab, you must map columns for at least the following items.

    • ID
    • Vendor Name
    • State
    • Distribution General Ledger Account
  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

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

Click this button to clear any changes made in the current Data Mapping screen.

Mark this checkbox to display all mapped and non-mapped items in the grid. If you clear this checkbox, the grid displays only those items that are un-mapped and need attention.

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.

Data mapping screens

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.

For each row in the grid, select the corresponding GL account number and description. The drop-down list includes all accounts that were set up in the Setup > Chart of Accounts screen. If a corresponding account is not listed, 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.

For each row in the grid, select the corresponding Accounting CS vendor. The drop-down list includes all vendors that were set up in the Setup > Vendors screen. If a corresponding vendor is not listed, select Add as is to add the vendor record, or select Do not import to skip the import of data for that vendor.

For each row in the grid, select the corresponding 1099 item. The drop-down list includes all supported 1099 forms.

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 information. 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.

Sample spreadsheet file

The following sample spreadsheet file is available for you to download and review. The sample spreadsheet is 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.

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.

Was this article helpful?

Thank you for the feedback!