Spreadsheet import - AR transactions

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 invoice, credit memo, cash, check, credit card, and write-off transactions from a spreadsheet using .XLS or .XLSX file format. Accounting CS saves these imported transactions as invoices, credit memos, or payments that you can pay or apply in the Actions > Manage Customer Payments screen.

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 Accounts Receivable Transactions 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 <x> field above the grid.
    Mapping item Additional information Required?
    Transaction Type Yes
    Customer ID Yes
    Customer Name Yes
    Journal --
    Date --
    Reference --
    Payment Term --
    Due Date --
    PO Number --
    Accounts Receivable General Ledger Account Yes, for Invoices and Credit Memos
    Undeposited Payments General Ledger Account Yes, for Cash, Check, and Credit Card transactions
    Bad Debt Expense General Ledger Account Yes, for Write-off transactions
    Unearned Revenue General Ledger Account Yes, for Cash, Check, Credit Card, and Write-off transactions
    Message --
    Item ID
    Description
    Quantity
    Price
    Amount    		 Yes, for Invoices and Credit Memos
    --
    --
    --
    --
    Sales Tax Item ID Yes, for items with a taxable tax status
    Shipping Item ID
    To
    Amount
    Date
    Contact Salutation
    Contact Name    		 --
    --
    --
    --
    --
    --
    Payment Amount --
  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. The Data Mapping screens may include the following fields and buttons.

Clear Mapping button

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

Show all mappings

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.

Reset default to

If the application encounters a blank field in the spreadsheet, and that blank field is mapped to a required field, the application enters Add as is in that field by default. To change the default information to Do not import 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

Accounts receivable accounts

For each row in the grid, select the corresponding Accounting CS GL account number. The drop-down list includes all AR 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.

Undeposited payments accounts

For each row in the grid, select the corresponding Accounting CS GL account number to use for Undeposited payments (account to hold the funds until they are deposited into a bank account). The drop-down list includes all AR 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.

Bad debt expense accounts

For each row in the grid, select the corresponding Accounting CS GL account number to use for bad expenses (debit account for write-off transactions). The drop-down list includes all AR 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.

Unearned revenue accounts

For each row in the grid, select the corresponding Accounting CS GL account number to use for unearned revenue (credit account for invoices that have not been applied at the time the payment was created). The drop-down list includes all AR 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.

Customers

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

Payment terms

For each row in the grid, select the corresponding Accounting CS payment term. The drop-down list includes all payment terms that were set up in the Setup > Firm Information > Payment Terms screen. If a corresponding payment term is not listed, select Add as is and then enter a payment term ID and due date type to add the payment term, or select Do not import to skip the import of data for that payment term.

If you select Add as is, the application opens a subsequent payment term Data Mapping screen, based on the due date type you select.

  • Day Based. Enter the number of days after the transaction date to use as the due date.

  • EOM Based. Select the month on which to base the calculation of the expiration date. EOM represents the last day of the current month; EOM+1 represents the last day of next month; EOM+2 represents the last day of the month after next; and so forth. In the Days +/- from EOM column, specify the number of days from the last day of the month. In the Cutoff day column, enter the number of days from the beginning of the month to represent the cutoff day. For examples, see the Payment Terms screen.

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.


Related articles

Spreadsheet import, overview

Accounts Receivable service, overview

Managing customer payments

Setting up accounts receivable customers

Setting up payment terms

Was this article helpful?

Thank you for the feedback!