Spreadsheet import - 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 client transactions from a spreadsheet file using .XLS or .XLSX file format. The source of the transactions in the spreadsheet could be data downloaded from a bank statement or other accounting applications such as Point of Sale applications, ADP payroll, or BAI2 or OFX files, or data exported from Client Bookkeeping Solution.

Special information

  • The application supports the import of several transaction types, including payroll checks. To import after-the-fact (handwritten) payroll checks, use the Enter Batch Handwritten Payroll Checks screen. To import unprinted checks, the transaction status must be either "Handwritten" or "Unprinted".
    Transaction type Acceptable spreadsheet values Notes
    AP check Accounts Payable Check, AP Check,
    AP Chk, AP C    		 If the AP service is not enabled, the application displays an error in the Transaction Type field.
    AP payment Accounts Payable Payment, AP Payment,
    AP Pmt, AP Pay, AP P    		 If the AP service is not enabled, the application displays an error in the Transaction Type field.
    Check Check, Chk, C
    Credit Credit, CR This type will be imported as a Deposit.
    Debit Debit, DR, DB This type will be imported as a Payment.
    Deposit Deposit, Dep, D
    Journal entry Journal Entry, JE If you are licensed for only Accounting CS Payroll, the application will not import journal entries.
    Payment Payment, Pmt, Pay, P
    Withdrawal Withdrawal This type will be imported as a Payment.
  • During the import, if the application encounters a blank general ledger account or if a column has not been mapped for the general ledger account, the application fills in the general ledger accounts automatically.

    Checks or payments

    • If the vendor record includes a single distribution, the application uses that account.
    • If the vendor record includes multiple distributions and the number of distributions in the imported transaction matches the number of distributions in the vendor record, the application assigns the distributions to the distributions in the same order as in the vendor record.

      If number of distributions in the imported transaction does not match the number of distributions in the vendor record, or if no vendor record is found, the application uses the Undistributed general ledger account specified in the Accounting Information tab of the client record.

    AP checks or AP payments

    If an Accounts Payable general ledger account is specified in the vendor record, the application uses that account. If an Accounts Payable general ledger account is not specified in the vendor record but an Accounts Payable general ledger account is specified in the client record, the application uses that account. If no vendor record is found, the application uses the Undistributed general ledger account specified in the Accounting Information tab of the client record.

    All other transaction types

    The application replaces all blank general ledger accounts with the Undistributed general ledger account specified in the Accounting Information tab of the client record.

  • The application sets the posting period based on the transaction date.
    • If a transaction is dated in the current fiscal year based on the current posting period, the application posts the transaction to the period for which it is dated.
    • If a transaction is dated in any future fiscal year for which an associated posting period exists, the application posts the transaction to the future period for which it is dated.
    • If a transaction is dated with a date in a prior fiscal year or with a date in a closed period in the current fiscal year, the application posts the transaction to the current period, displays a warning message, and lists the transaction in the diagnostic report.
    • If a transaction is dated with a future date for which there is no appropriate posting period, the application displays a critical error.

    Examples (based on a calendar fiscal year with monthly periods)

    Client's posting period Transaction date Posting period end date
    for imported transaction
    11/30/14 1/18/14 1/31/14
    12/31/14 1/18/15 1/31/15
    11/30/14 12/10/13 11/30/14
    11/30/14 12/10/16 Critical error
  • If you map a column for Payee Name/Description, the application creates multiple transactions for journal entries — one for the debit amount and one for the credit amount.

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

In the Source Data screen, specify the client name and the spreadsheet file and worksheet within that file for which to import the client transactions.

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select 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 Required?
    Transaction Type Yes
    Bank Account Not required, but if you map this column, use the description from the Setup > Bank Accounts > Main tab.
    Journal --
    Transaction Subtype --
    WP Reference --
    Difference --
    Misstatement --
    Reference Yes, for checks , AP checks, and AP payments
    Date --
    Vendor ID Yes, for AP checks and AP payments
    Payee Name/Description Yes, for AP checks, AP payments, and Checks
    Total Amount At least one type of amount (Total, Distribution, or Debit Distribution and Credit Distribution) must be mapped.
    General Ledger Account --
    Distribution Description --
    Debit Distribution Amount At least one type of amount (Total, Distribution, or Debit Distribution and Credit Distribution) must be mapped.
    Credit Distribution Amount At least one type of amount (Total, Distribution, or Debit Distribution and Credit Distribution) must be mapped.
    Distribution Amount At least one type of amount (Total, Distribution, or Debit Distribution and Credit Distribution) must be mapped.
    1099 Form/Box Yes, for importing 1099 transactions. Before you begin the import, the Form Selection and Box number must be complete in the 1099 Properties tab in the Setup > Vendors screen.
    Distribution WP Reference --
    Detail Amount --
    Detail Description --
    Memo --
    Status --
  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

Bank accounts

For each row in the grid, select the corresponding Accounting CS bank account. The drop-down list includes the bank accounts that were set up in the Setup > Bank Accounts screen. To import the transactions for this bank account as cleared (for bank account reconciliation purposes), mark the checkbox in the Cleared column.

Chart of Accounts

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 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. The drop-down list includes all vendors that were set up in the Setup > Vendors screen. If the vendor is not listed, you can add the vendor by selecting Add as is.

1099 items

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

transactions-spreadsheet

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!