Data Imports: Parts, Units, Customers, and Vendors (Video)

Introduction

This article, and video walkthrough, demonstrate how to import data into EMDECS from a spreadsheet file.  There are eight data imports currently available in EMDECS:

From the Part Master (Menu path:  Parts → Part Master):

1. Import Parts (list of all parts)

1. Import Parts On Hands

1. Import Part Location Details (use this upload )

If you have multiple inventory warehouses, use the Import Part Location Details upload to change part prices and storage locations

From the Unit Master (Fleet → Unit Master):

1. Import Readings

1. Import Fuel

1. Import Equipment Units

From Administration:

1. Import Customers (Administration → Customer) 

1. Import Vendors (Administration → Vendors)

Tips to Avoid Common Cell Format Errors that May Corrupt Your Data

If you use Microsoft Excel to enter or paste data for import, ensure that all columns are formatted as type Text. Using other formats can result in import errors. For example, Excel may round numbers automatically, truncate leading zeroes, or change dates based on your computer's location settings (i.e. MM/DD versus DD/MM).

If you copy and paste data from another Excel spreadsheet in to the upload template, unwanted cell formatting may be copied unless you paste values only!

\

Video Walkthrough

Part Master Imports

If you have multiple inventory locations or warehouses. and use cost by warehouse (each of your locations/warehouses track cost separately), it is necessary to use the Import Part Location Details tab to import warehouse specific part prices.  The Part Master import updates the corporate level cost only, not the cost for individual warehouses or locations.

1. Open the Parts menu and select Part Master.
   
   
   
2. Select the Import Parts tab.
   
   
   
3. Before your part list can be uploaded, the data needs to be inserted into a template that is recognized by EMDECS.  Click the Download Template button to download and save the template file.
   
   
   
4. A pop-up window will prompt you whether you want to include your current data in the download file.   Select Yes if you want to download a file containing your current list of parts to modify and add to your existing parts list.   Otherwise, leave this set to No and click Download.
   
   

   If you choose to include your current data, the file may take several minutes or longer to generate.   Open the Reports screen → Report History tab to view / download the file when the report has finished generating.
    
5. The template can be opened and edited using Microsoft Excel.   The top row contains a column header with a code name for the data.   The second row contains a long form description of the data that need to be in the column.   Column headings in bold indicate a required field to insert or create a new part.
   
    
   

   Fields marked in bold (Item Code, Description, and Unit of Measure, in this example) are required. All other fields are optional and will be ignored if left blank.
    
6. Populate the file with your data for import and save the file.
   
   
   
7. After your import file has been prepared, click on Choose File.
   
   
   
8. Browse to the directory on your computer where the import file is located, select it, and click Open.
   
   
   
9. The file name will be displayed.  Click the Import button to continue.
   
   
   
10. An import screen will pop-up to advise the process has been started.  Click OK.
    
    
    
11. Click the Refresh button to refresh the status and progress details.  Once completed, the status will display Complete, 100%, along with the number of records imported and the number of records that could not be imported due to errors.
    

12. Import errors are listed under the Import Data Errors and Import File Errors tabs.
    
    
    Common causes of import errors include:

    1. Required fields left blank (the error message will contain the phrase "cannot insert NULL into. . . ").  To resolve this error, fill in all required fields and re-upload.

    2. Invalid special characters.  EMDECS can only import data containing alphanumeric characters and English language standard keyboard characters.  For example, an ampersand (&) can be entered in the part description field, but the degree symbol (°) will cause an import error.

    3. Invalid data (i.e. alphabetic characters entered in a part price field or an incorrect date format).

    4. Value too large for column.  Some values, such as a product group code, have a maximum length of 12 characters, for example.  Look in the error message for the maximum size:  "ORA-12899: value too large for column "PERFIT"."CD_CONVERSION_ITEM_MASTERS"."PROGRO_CODE" (actual: 13, maximum: 12)"

13. If you have a lot of import errors to fix, click on the Export File button to download a file containing all records with errors so you can fix them and re-upload.
    

14. If there were any records with errors, an ERROR_MESSAGE column in the export file will show you lines with errors so they can be fixed and re-uploaded, as shown in the example pictured below:
    

Import Parts On Hand (Physical Count Import)

1. Open the Parts menu → Part Master then select  the Import Parts Onhands tab. 
   
   
   
2. Click Download Template to download a template file for your data.
   
   
   
3. Populate the template file with your list of parts, warehouse, date, and on hand quantity.  These fields, marked in bold, are required.  All other fields are optional and will be ignored if left blank.
   
   
   
4. Once your import file is prepared, click Choose File
   
   
   
5. Browse to the directory on your computer where the import file is located, select it, and click Open.
   
   
   
6. Click on the Import button next to the file name.
   
   
   
7. An Import Summary screen will pop-up with a list of the parts and on hand quantities to be imported.  Double check that your data is aligned with the correct column heads and you can scroll through the data to check for any errors.  When you are ready to proceed with the import, click on the Process button at the bottom of the screen.
   
   
   
8. The Import Parts Onhands tab displays a list of the on hands parts imported for the selected import date and file name.
   
   
   
9. If there are any import errors, they will be listed under the Import Errors tab.
   
   
   
10. On hand quantity imports are posted as a physical count at the date and time of the import.   To review on hand import history, open Parts -→ Physical Count, then select the History tab.   Look for the most recent posted count for the warehouse.   The description for an on hand import will be "Conversion factor."   Click on the physical count number to drill-down for more detail.
    
    
    

Import Parts Location Details

Use this import process to set different part costs for separate inventory warehouses.

To import parts location details, with warehouse level costs / prices complete the following steps:

1. Open the Parts menu → Part Master then select the Import Parts Location Details tab.
   
   
   
2. Click on Download Template to download a template file for your data.
   
   
   
3. Insert your part location data into the file using Microsoft Excel.   Columns with bold headings (item code and warehouse code) are required.  Cells left blank will not be uploaded or change existing records.
   
    
   

   The Currency code (CUR_CODE), column P, is required if costs are entered.

4. To upload your prepared data file, click on the Choose File button.
   

5. Browse to the directory on your computer where the file is located, select it, and click Open.
   

6. The file name will be displayed.  Click Import to continue.
   

7. The Import Summary screen displays a list of the parts and location details to be uploaded.  Click Process to continue.
   

8. Successfully imported locations are displayed under the Imported Parts Location Details tab.
   

9. Any errors will displayed under Import Errors.
   

10. An example of a common location import error is displayed below.  The store room 1 location was already in listed for the parts and could not be added again.
    
    
    

Unit Reading Imports

 

Import Fuel Usage

Fuel usage for units can be imported using the following steps:

1. Open the Fleet menu → Unit Master → search for and select the unit, then open the Import Fuel tab.
   
   
   
2. Click on the Download Template button to download the template file for inserting your fuel usage data.
   
   
   
3. Open the template with Microsoft Excel and ensure that all columns highlighted in yellow contain data.  These are required fields for a fuel import.
   
   
   
4. Click on the Import button.
   
   
   
5. Enter a location the unit is based or serviced in and select one of the following date formats:
    
   1. DD/MM/YYYY
   2. DD/MM/YYYY HH:24:MI
   3. DD/MM/YYYY HH:MI AM
   4.  MM/DD/YYYY
   5. MM/DD/YYYY HH:24:MI
   6. MM/DD/YYYY HH:MI AM

    

   HH24:MI is 24 hour format with hours and minutes and HH:MI AM is 12 hour format with hours and minutes followed by AM/PM.  If you choose DD/MM/YYYY or MM/DD/YYYY without a time format, EMDECS will import at zero hundred hours, or 12:00 am, on the date specified.

    

   Click Choose File to select your import file.

   

    
6. Browse to the directory where your fuel import file is located, select it, and click Open.
   
   
   
7. Click OK to process the upload.
   
   
   
8. Allow for a few moments of processing, depending on the size of the import file, before receiving a message that the file has been successfully uploaded.
   
   
   
9. Click Post to finalize the fuel import.
   
   
   
10. If there are errors or rejected entries, open the Rejected Data Lines tab, then click on the Resolve buttons to review or correct the entry.
    
    
    
11. A reject reason is displayed at the top of the screen.   In the example pictured below, the fuel quantity of 50 cannot be imported because the unit's tax size has a maximum capacity of 40.   The fuel quantity can be changed to correct this.
    
    
    
12. If there is a "no fuel capacity defined" error, the unit may be missing fuel settings (i.e. no fuel capacity or fuel type has been defined for the unit).
    
    
    
13. Open the unit details from the Unit Master screen then select the Fuel sub-tab under Specifications.   If no fuel capacity and type have been defined, click Add Fuel Capacity.
    
    
    
14. Select the reading type, unit of measure (UOM), fuel type, and the maximum quantity or capacity of the tank, then click OK to save.
    
    
    

Import Equipment Units

Use the following steps to import equipment units (i.e. trucks or trailers):

1. Open the Fleet menu → Unit Master →  then open the Import Equipment Units tab.
   
   
   
2. Click on the Download Template button to download the template file for importing the unit list.
   
   
   
3. Populate the download template with the unit list.  Columns with bold headings (Customer Code, Equipment number, and Description) are required.  Cells left blank will not be uploaded or change existing records.
   
   
   
4. Click on the Choose File button to select your prepared unit import file.
   
   
   
5. Browse to the directory on your computer where the file is located, select it, and click Open.
   
   
   
6. The file name will be displayed.  Click Import to continue.
   
   
   
7. An Import Units screen will pop-up to advise the process has been started.  Click OK.
   
   
   
8. Click the Refresh button to refresh the status and progress details. Once completed, the status will display Complete, 100%, along with the number of records imported and the number of records that could not be imported due to errors.
   
   
   
9. Any import errors will be listed by clicking on the Number of Errors Hyperlink.
   
   
   

10. Once you have corrected the errors listed in the original file, you can simply repeat this process from Step 4.
    
    

Import Customers

To import a list of customers in bulk from a spreadsheet, complete the following steps:

1. Open the Administration menu and select Customer.
   
   
   
2. Select the Import Customers tab.
   
   
   
3. Click Download Template to download the customer import template file to paste in your data.
   
   
   
4. The customer upload file has two required fields, code and name.
   
   
   
5. Click on the Choose File button to select your prepared unit import file.
   
   
   
6. Browse to the directory on your computer where the customer import file is located, select it, and click Open.
   
   
   
7. The import file name is displayed.  Click Import to continue.
   
   
   
8. The import summary lists the customers to be imported.  Click Process to continue.
   
   
   
9. Successfully imported customers are listed under Imported Customers.
   
   
   
10. Any import errors can be reviewed under the Import Errors tab.
    
    
    

Import Vendors (i.e. Part Suppliers)

To import a list of vendors / suppliers in bulk from a spreadsheet, complete the following steps:

1. To import a vendor list, open the Administration menu and select Vendors.
   
   
   
2. Select the Import Vendors tab.
   
   
   
3. Click Download Template to download a template for your vendor import list.
   
   
   
4. The vendor code and supplier name columns, displayed in bold, are required fields.  All other fields are optional.   Blank columns will be ignored and will not change existing records (i.e. the address will not be removed if you upload an existing vendor with a new payment term without entering their address).
   
   
   
5. Click Choose File to select your prepared vendor list file for upload.
   
   
   
6. Browse to the directory on your computer where the file is located, select it, and click Open.
   
   
   
7. The file name will be displayed.  Click Import to continue.
   
   
   
8. The list of vendors to be imported are listed on the Import Summary screen.  Click Process to continue.
   
   
   
9. Successfully imported vendors are listed under the Imported Vendors tab.
   
   
   
10. Any errors will be displayed under Import Errors.