Answer: Data can be imported from a comma delimited text file (.csv) into certain application tables. Generally, the areas of programs that are appropriate for importing include customers, vendors, parts, units and employees. Other tables tend to be too complex and different among applications to facilitate importing.
Importing of Data is handled through our Express SQL application. To purchase Express SQL, contact the sales team. The help in the Express SQL will provide complete details on importing data. Express SQL is used for three things in connection with importing data:
- Provides help topics covering the steps involved
- Provides the table list and the table schema (field definitions)
- Provides a wizard for importing the data
The following outlines what is involved in importing data into our application tables. Please review this information and the help in Express SQL. If you have further questions, please Email Sales. Importing data requires some understanding of database tables, fields and schemas. Unless you have a large number of records to import, we recommend entering data from scratch. Rarely is data imported that the user doesn’t have to go through each record making edits to adjust the data to properly reflect the data fields required by the target application.
In order to accomplish the importing of data into any table of the Express Maintenance family of products, you must complete the following:
1. Know the target table name and schema (field definitions).
2. Prepare a .csv (comma delimited file) import text file containing the data to be imported.
3. Run the Express Import Utility and step through the wizard of importing data.
Getting The Table Schema (field definitions)
In order to properly prepare the .csv (comma delimited file), you must know the destination table name and schema (field definitions). To obtain a print out of the table definitions, you should do the following:
1. Run the Express SQL
2. Make a connection to the server / database desired
4. Right click the desired destination table in the Table list
5. The schema will appear in the right panel
6. Click the Export button and export to Word or Excel to export the schema
7. Print the table schema data
8. Study the table schema data to see how it compares to your existing data
The most important and tedious step of data import is preparing the .csv (comma delimited file) which contains the data to be imported. The following will provide full details for preparing such a file.
To import the data into the desired table, follow the directions in the Express SQL help topic Importing data. You will be directed to a simple import data wizard.
If you encounter problems or have questions, please check your import data file carefully. The most common mistakes are:
- Incorrect Field Names at the top of the import file – The field names must match the field names in the target table.
- Missing Fields – Some fields are required and must exist in the import file. The table schema will indicate which fields are required.
- Duplicate Records – Some fields such as part numbers and record numbers do not allow duplicates. Each record must contain a unique number in such cases.
- Incorrect Data – Target fields of the “Date” and “Numeric” types can only contain dates and numeric values. A space or any alpha characters will trigger an error.
- Incorrect Naming – The import file cannot contain spaces in the file name or in the folder path.
- Importing Companies (vendors) – Importing companies data involves importing into two separate tables CompanyMaster and CompanyAddress. For special instructions, see the note below.
- View Existing Records – The simplest way to examine how fields should appear or what data is contained in tables and fields is to enter a record in the application. Next, use ExpressSQL to view and observe the data.
Importing Companies – Special Note
Importing companies data involves importing into two separate tables CompanyMaster and CompanyAddress. You cannot import into Company as this is actually an SQL view (predefined query) rather than a table.
The CompanyMaster table contains the company name and a unique CompanyID number stored in the CompanyID field (example: 1001).
The CompanyAddress table contains the corresponding company addresses. The CompanyID field corresponds to the same field in the CompanyMaster table as is a join link between the two tables (example: 1001). The Numbered field is the CompanyID with a decimal extension based on the number of address records added for the company (example: 1001.01, 1001.02, etc). Also, be sure to populate the Status field with “A” for Active and the Vendor and / or Customer field with “Y’ for Yes.
If you continue to have problems, please Email Support and provide the exact error message being received.