The Import A/R Open Items (Spreadsheet) utility allows you to import beginning balances for your customers from an Excel spreadsheet. With Compeat, you can either load customer beginning balances within Compeat using the Load Open Items feature in Setup>Accounts Receivable, or import beginning customer balances into Compeat using the Import A/R Open Items (Spreadsheet) in File>Import Data.
Note: For the Import A/R Open Items (Spreadsheet) utility to work, you must have Excel installed on your computer.
To successfully import, you must consider a few minor formatting details.
Files must be in an .xls file format. This is the standard Excel file extension.
The basis of the import process is simply to indicate to Compeat where each piece of data in your spreadsheet is located. You do this by entering the cell reference for your data in the Cell Locations fields.
You can view a sample spreadsheet by clicking Create Sample Spreadsheet. The data arrangement in the sample spreadsheet corresponds to the example cell references shown next to each field in Compeat. In some cases it may be easiest to rearrange your data to match the sample arrangement.
Do not leave any blank lines between customer balances data; when Compeat encounters a blank line, it will stop importing.
Your data must be in the first worksheet of the Excel workbook.
Note: Typically in Excel, when you create a new spreadsheet you will actually be creating a workbook with 3 worksheets; Compeat can only access data information from the first sheet (named Sheet 1 by default). You can rename the sheet, as long as it remains the first worksheet in the book (worksheets within a book can easily be dragged to change their order).
You must have data in each cell defined in Cell Locations.
The imported Customer numbers must match those define in Setup > Accounts Receivable>Customers.
Note: The total customer balances imported must match the balance of the beginning A/R GL Account on the Balance Sheet.
Selecting the Import A/R Open Items (Spreadsheet) utility brings up a screen similar to the following:
After you have properly formatted your data in Excel, you can import them into Compeat. Define the cell locations for the following information:
Note: Next to each field is an example cell reference that corresponds to the cell in the sample spreadsheet. If your data matches the organization of the sample spreadsheet, simply use this cell reference.
• Cell location for the entity to import into: Enter the cell-reference for the cell containing the entity number.
• Starting row for detail entries: Enter the cell-reference for the cell containing the first row of data.
• Column position for customer #’s: Enter the cell-reference for the cell containing the customer’s number .
• Column position for the Trx Dates: Enter the cell-reference for the cell containing the transaction date.
• Column position for References: Enter the cell reference for the cell containing the customer transaction number.
• Column position for Amounts: Enter the cell reference for the cell containing the amount of the transaction.
Note: Compeat will remember the locations that you specified; if you make all of your customer beginning balance spreadsheets from a template, this will save you from retyping the positions each time you import a customer beginning balance spreadsheet.
After filling in the cell locations, select the files that you want to import. Use the directory tree (in the lower left section of the screen) to navigate to the folder containing your Excel files. When a folder is selected that contains .xls files, they will be displayed in the right-hand panel; check the files that you want to import. Click Import Selected A/R File(s) to import.