Using the Data Import/Export Spreadsheet (Overview)

To use the automated options available through the Data Import/Export spreadsheet, you need:

Excel

The Data Import/Export Spreadsheet

An e-automate User ID and Password

Permission to update the appropriate module(s) within e-automate

If you do not have an e-automate User ID and Password and/or if you have questions regarding permissions, contact your system administrator.

When you have these items in place, you need to prepare data to import. For each tab used for importing module-level account balance detail, this document provides a table with the following information for each column of data e-automate uses for the import: Column Name, Required (yes/no), Validated (yes/no), e-automate Use, and Acceptable Values. If e-automate requires a field, you must include data for the field; if you do not, any data on that row is not imported. If e-automate validates a field, you may enter in the field only a valid entry established for that field within e-automate; if you do not, any data on that row is not imported. For fields that e-automate validates (highlighted in Orange in the spreadsheet), the table’s Acceptable Values column explains where to locate the acceptable values within e-automate. For fields that e-automate does not validate (highlighted in Green in the spreadsheet), the table’s Acceptable Values column provides general information about the field (for example, field length) and—where applicable—additional information required to make the entry valid (for example, the date entered in the DueDate field for an invoice should not be prior to the date entered in the Date field for the invoice).

Copy the Data Import/Export spreadsheet (DataImportExport.xls) from its location to your local PC. If you installed e-automate on the server or on a per-machine basis in the default location, the DataImportExport.xls spreadsheet utility should be located at C:\Program Files (x86)\ECI\e-automate\Utilities.

If you installed e-automate on a per-user basis in the default location,the DataImportExport.xls spreadsheet utility should be located at C:\Users\UserName\AppData\Local\ECI\e-automate\Utilities.
Note:  If you cannot view this file location, see the Unhide Files and Folders in Windows topic.

If you did not install e-automate in the default location, locate the e-automate directory. The spreadsheet is located in the Utilities directory found within the e-automate directory.

Using Excel, open the DataImportExport.xls spreadsheet utility.

When you use the Data Import/Export Spreadsheet to import beginning balances and/or data related to beginning balances such as beginning inventory quantities, you need to provide related information on the Settings tab.

Click the Settings tab to bring it forward.

In cell D10, enter the Opening Balance Equity Account Number. When transitioning from any system to e-automate, you must identify the account to which transitional accounting entries are to be made. For acceptable values in this cell, log into e-automate and go to Accounting > General Ledger Accounts. E-automate displays the General Ledger Accounts window. Locate the Account number associated with the Account name ‘Opening Balance Clearing’. Enter this Account number in the Opening Balance Equity Account Number field.

In cell D11, enter the Go-live Transitional Transaction Date. Determine the date you are going to 'go live' with e-automate, and enter the date just prior to your go-live date in this cell. Typically, the date is the last day of the previous month. For example, if your go-live date is 5/1/2020, enter 4/30/2020. E-automate will use this date when posting transitional transactions to the General Ledger.

When you have prepared data for import and entered the appropriate settings for the import, you are ready to process the import.

Click the [Import to e-automate] button. E-automate displays the login window (if you are not already logged in to e-automate).

Enter your User ID.

Enter your Password.

Select the appropriate Profile.

Click [OK]. E-automate displays the Backup Needed window.

Click [OK]. E-automate displays the Confirm Database window.

If you are in the correct database, click [Yes]. If not, click [No] to exit; or click [Change Database] to return to the login window, and return to Step 4. When you click [Yes] to confirm the database, e-automate processes the import.

When you process an import, e-automate displays the results of the import using the Status and Error Message columns. If e-automate accepts a row (record) as valid, the Status column displays ‘Inserted.’  If e-automate does not accept a row (record) as valid, the Status column displays ‘Not Inserted’ and the Error Message column displays the reason the record was not inserted (for example, ‘***Invalid bill to customer’).

For each row that was ‘Not Inserted’, review the Error Message column to determine the change you need to make for the row to be accepted as valid and then make the change in the spreadsheet.
Note: To group all the rows that were ‘Not Inserted’, you can sort the spreadsheet by the Status column; just be sure to expand the selection to include all the rows and columns of data you want to sort—including the Status, Error Message, and Post Action columns.

During this process, you do not need to remove rows marked as ‘Inserted’; on import, e-automate skips records that are already marked as ‘Inserted’. For this reason, do not delete data recorded in the Status column prior to re-processing an import; if you delete ‘Inserted’ for a row and then re-process the import, the row will be inserted again, creating a duplicate record.

When you are ready to retry inserting the records into the e-automate database, click the [Import into e-automate] button. When the import is processed, e-automate updates the Status column, changing ‘Not Inserted’ to ‘Inserted’ for any rows now accepted as valid. If e-automate still marks any rows as ‘Not Inserted’, repeat this process—reviewing the error messages, making the appropriate changes, and retrying to insert the rows—until you have inserted all your records.