Import from Excel File
|FIRST TOPIC BACK PREVIOUS TOPIC NEXT TOPIC|
MENU: TOOLS > IMPORT / EXPORT DATA > IMPORT FROM EXCEL FILE
eForms Standard & Enterprise Only
(For evaluation purposes, demo installations and eForms Basic can import the first three records of an Excel file)
NOTE: Microsoft Excel must be installed on your computer to use the Import form Excel File function. Excel files must be in the XLS format, not in the XLSX format of Microsoft Excel 2007 and later.
1. When importing using eForms Standard, each sheet in the spreadsheet must contain data for one company and one slip type only. When importing using eForms Enterprise, each sheet in the spreadsheet can contain multiple companies by entering the company name(s) in the COMPANY.NAME1 column to distinguish between the companies that each slip will be imported into. Each sheet will be imported separately.
2. Insert a new column, as column A, at the left of the sheet.
3. Insert a new row, as row 1, at the top of the sheet.
4. Enter the appropriate key in cell A1 to designate the type of data being imported (use of the same key in the other cells in column A is not required). Refer to the appropriate section under Headings for Import Files for the keys for each type of form.
5. Complete the first row by adding the headings that eForms expects for the type of data being imported. See the section Headings for Import Files for the headings for each slip type. Errors will result if headings are used that are not recognized by eForms. Sample Excel import file templates can be found using the menu path: TOOLS > IMPORT DATA > OPEN EXCEL TEMPLATES. There is no need to include columns for every heading (nor need they be in any specific order); only include columns for which data exists that will be imported. Additionally, if you have a column of data in your spreadsheet that you do not wish to import, you can force eForms to ignore it by using the "NULL" heading.
6. Make sure that the data in each column matches the headings. For example, if the city and province were exported to a single column in the Excel sheet, you will need to move the province to the PROV column and the city to the CITY column. A typical spread sheet could look something like this:
A common problem when creating an import file is the presence of empty cells beyond the data cells; this will cause the import process to report an error condition. To correct this problem, simply copy and paste your data cells into a new sheet and import from the new sheet.
COMPANY.NAME1 (eForms Enterprise Only)
The COMPANY.NAME1 is used exclusively by eForms Enterprise to enable importing data for multiple payers/employers/companies. To use this field simply enter the name of a company already existing in the eForms database for each slip to be imported to that company and the data will be saved to the appropriate company during import. COMPANY.NAME1 is a text field that can contain up to 30 characters.
7. Save and close the Excel spreadsheet. NOTE: All instances of the import file must be closed before continuing to the next step.
8. Start eForms. From the Tools menu, choose Import and then Import from Excel File.
9. Browse to the location of the Excel file you just created.
10. Indicate which sheet in the spreadsheet you wish to import data from. The Type of data records in this sheet field should indicate the correct data type automatically.
11. eForms Enterprise Only - Indicate whether you wish to Import into current company, Import into a new company or Import into companies in the Company.Name1 column. If you select Import into a new company, you will be prompted for a new company name. You will need to edit the company profile at a later time to complete the data entry for the company information. If you select Import into companies in the Company.Name1 column, the data for each slip will be imported into the company indicated in the Company.Name1 column.
12. Click Import. eForms will add the slip data to the indicated company. If there are already slips of the selected type in the company, the imported data will be appended as additional slips. A summary for the return will be created or updated as required. If you have a company with 10 T4 slips and you import 15 more T4 slips, eForms updates the T4 summary with the totals of all 25 slips.
Add/Update Options (eForms Enterprise Only)
eForms Enterprise enables a far more granular data import than other versions. Using eForms Enterprise it is possible to import data into an existing company without overwriting data already existing within that company.
Add new slips to the database
Data for the slip in question will be added to the data existing in eForms Enterprise as a new slip. If the slip already exists in the eForms database it will be overwritten
Update existing slips based on linking field
This option will update existing data in the eForms Enterprise by use of the linking field. The linking field can be any field that is unique to each recipient; such as SIN. During the import existing data for the recipients indicated by the linking field will be updated with the data appearing in the import file. If no existing recipient is found matching the linking field you can use the When linking field is missing or not found option to either stop processing the import data or skip the record for which no linking field is found.