Question: How can I import data into ExpressMaintenance from other Applications?

See Also: How can I export data from ExpressMaintenance?

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 ExpressUtility application.  This application is available free and should be downloaded from our website - Download ExpressUtility.  The help in the ExpressUtility will provide complete details on importing data.  ExpressUtility is used for three things in connection with importing data:

1. Provides help topics covering the steps involved

2. Provides the table list and the table schema (field definitions)

3. 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 ExpressUtility.  If you have further questions, please contact support@ExpressTechnology.com.  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.

Requirements

In order to accomplish the importing of data into any table of Express Technology software, 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 ExpressUtility
2. Make a connection to the server / database desired
3. Click Explore Data / Schema
4. Click the desired destination table in the left panel
5. The schema will appear in the right panel
6. Click the Export button and export to Word or Excel.
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.

Preparing Import File

What is a .csv file?
A csv (comma delimited file) is an ASCII standard text file that is formatted in a certain way. Each row represents a record and each column is separated by a comma. In addition, string (character) fields begin and end with double quotes. The following is an example of such a file where there are five records with columns of Name, Type, Unit, Make and Status.

Name, Type, Unit, Make, Status
"Expo High Speed Printer","Printing Equipment", "CP-43","Expo","A"
"Expo High Speed Folder","Printing Equipment", "CP-99","Expo","A"
"Dell Server Computer","Electronics", "Insp101","Dell","A"

"Notebook Computer","Electronics", "Insp999","Dell","A"

"2 HP Fan Motor","Motor", "FM2-220","GE","A"

You will notice that the field name are listed in the first column. A string (character) field does not have to include the quotes if it does not contain spaces, commas or other such characters. However, it is a good idea to surround all string fields in double quotes.

Why use a .csv file?
Comma delimited files are somewhat of an industry standard for importing and exporting data. Most major spread sheet programs such as Microsoft Excel allow for saving data to csv file format. Most commercial applications have an option for exporting to csv files. If your old application does not support exporting to csv file format, it should at least support exporting to a spread sheet. Once the data is in a spread sheet, you should be able to save the file in csv format.

How do I create the csv file?
The answer to this question is ultimately determined by the application in which your data currently resides. Check your application documentation for ways to export the data to a spread sheet, text, or csv file. If your application uses a proprietary database engine and does not provide any form of exporting, you are probably out of luck. However, it is worth a support call to the application vendor as they may have an export utility available. Most good applications include options for exporting to numerous file types.

Special Requirements of the csv file.
1. The field names must be in the first row of the csv file.
2. The field names must be changed to the names of the fields in the target Express Technology software table schema.

3. The fields must contain data that is of the same type of the target fields.  In other words, a target numeric field cannot contain alpha characters.  It can only contain numbers.  Watch out for this when importing into numeric and date fields.Steps to preparing the csv file.
1. Use your old application to export the data to a csv, text or spread sheet file.
2. Open the exported file using a spread sheet program such as Microsoft Excel.
3. Make sure the first line (record) in the file contains the field names.
4. Edit field names to the names of the fields in the Express Technology software table schema.
5. Delete any columns that you do not wish to import or that do not exist in the target table.
6. Save the csv file to TableName.csv, where TableName is the name of the target table such as (Units.csv).  The csv file cannot contain spaces in the file name or in the path where it resides.  This is because ExpressUtility uses an SQL statement to extract the data and the spaces cause problems in building the SQL statement.

Notes
1. We recommend saving the csv files to C:\ExpressTech where the ImportUtility.exe file resides.  do not use any spaces in the file name.
2. When you run the import utility, you will have the opportunity to review data before it is imported.

Importing The Data

Once you have the csv file prepare properly and ready for import, the next step is to run the Import Utility. Run the ExpressUtility program to access the Import Utility wizard.  Select or enter your server name and database to connect to the database engine.  Click on Import Data tab to access the import wizard.  The following is an explanation of each step:

Step 1: Select Source File
1. Click the Open File button and locate the prepared csv file.
2. Once the file is selected, simply click the Next button.

Step 2: Confirm Source Data & Destination Table
1. The csv file data is read and displayed in a grid for your review and editing (if necessary).
2. Review the source data and confirm this is the data desired.
3. Make sure the destination table is the correct target table in the Express Technology software. The destination table is determined by the file name of the csv file or can be selected.
4. Once review is complete, click the Next button.

Step 3: Select Data Insertion Method
1. Select the method to be used for inserting data:
a. Append - Imports records leaving existing records in place.
b. Overwrite - Deletes all existing records and then imports new records.
2. Once method is selected, click the Next button.

Step 4: Import Records
1. Review all data displayed and make sure it is correct.
2. Select the target database which represents the Express Technology application.
3. Click the Import button to perform the import of data.
4. The process will be displayed and results will be reported upon completion.

Typical Problems

If you encounter problems or have questions, please check your import data file carefully.  The most common mistakes are:

1. Incorrect Field Names at the top of the import file - The field names must match the field names in the target table.

2. Missing Fields - Some fields are required and must exist in the import file.  The table schema will indicate which fields are required.

3. 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.

4. 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.

5. Incorrect Naming - The import file cannot contain spaces in the file name or in the folder path.

6. Importing Companies (vendors) - Importing companies data involves importing into two separate tables CompanyMaster and CompanyAddress.  For special instructions, see the note below.

7. 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 ExpressUtility 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 contact support@ExpressTechnology.com and provide the exact error message being received.