Feature - New Item Import Through CSV File
Document Release Date: 08/21/2020
Version: 7.5 & up
The feature can be accessed in I/M, Utility, and New Item Import. It allows you to import new items from either an ASCII or CSV file. This document only discusses features related to the CSV file import, which is easier to prepare than an ASCII file. The CSV file option also allows you to create an item by copying from a similar existing item, which makes it easy. In addition, the CSV file import allows copying of BOMP Product Structures, Kits and Feature/Option Structures.
Global Setup
Before you can use this feature, you must configure Global Setup, Add-ons, and Item Import Utility. This is a licensed add-on enhancement. If you have not purchased the license, then you will be reminded to do so accordingly. The following shows the Global Setup screen:
1. Enable item Import Interface? Answer "Y" to this field to turn on this feature.
2. Ignore Invalid Item and Import the Rest? Should the import ignore records with invalid items or should the import fail for the entire batch? Please see the "What Is Considered Invalid Item?" section below for more details. The default value is "N."
3. Fixed Length ASCII Import File Path & Name: Enter the default file name and path of the ASCII file name (not discussed in this document).
4. Import If There's Any Warning? If the import detects a warning, should the program proceed with the import? A typical warning would be a string size that exceeds the maximum allowable value. If you answer "Y" to this question, the import simply truncates the value and proceeds with the import. If "N," the entire batch will not be imported until you fix all the warning conditions.
5. Numeric Data is Text Format or Cobol Format? This flag is only applicable for Fixed Length ASCII file import. This applies to a numeric field like "Unit Price" that has the COBOL format of "PIC 9(6)V9999 SIGN IS TRAILING SEPARATE." If you choose "C"=COBOL format, then the import will expect a unit price of 1,234.56 to be an 11-digit value like "0012345600+" where a leading zero is required, the decimal digit is implied and the sign is trailing separately. On the other hand, if you choose "T"=Text format, then we expect your data to be "1234.56," "1,234.56," "1,234.5600" or pretty much any humanly recognizable numeric notation anywhere in this 11-digit space. This flag is not applicable to a CSV file, in which we always assume a human-readable format for numeric fields.
6. Print Sorted By Seq No Or Item No? Would you like to see the output report in the same sequence of the ASCII or CSV file or sorted by item number sequence?
7. CSV ASCII Import File Path & Name: Enter the default file name and path of the CSV file name. Make sure the file name extension is CSV.
8. Does CSV File Have A Header Row? Indicate whether your CSV file has a header row.
CSV File Layout
Once you are done with Global Setup, you can access this feature in I/M, Utility, New Item Import, and CSV. If you choose the "File Layout" in the drop-down menu, you will see the following window:
The layout is pretty much self-explanatory. We kept the CSV format simple so the item import can be done easily. Most of the fields in the CSV can be left blank. Also, not all item fields have a corresponding entry in the CSV layout. For those fields not in the layout, we use the same default logic as if you were adding a new item. This includes whether you have set up the "DEFAULT-ITEM" for default values.
Some fields worth explanation are:
A) Item No (Req): Item Number is required and must be 15 characters or less. You cannot provide an item number already on file.
B) Copy From Item#: You can enter an item similar to the new item to be added. The import will copy each of the item field values to be the default for the new item. Any values in the corresponding columns of the CSV file will override the default values.
C) Desc 1 (Req): Item Description 1 is required.
H) FOB Cost: Manually adding an item in Elliott will default all three cost fields, Average, Last and Standard, to the same value. That means we can use one cost field in the import instead of breaking them up into three separate fields. Column H represents all three FOB Cost fields and column I represents all three Landed Cost fields.
If you are unsure the import will work the way you expect, the best and most prudent way for you to proceed is to initially import one item only and see how the values are written to the Item Master. Based on those results, you can figure out the proper way to populate each field before committing to a mass import.
Pre-Interface and Interface
When you choose to "Pre-Interface" or "Interface" an import item CSV file, you will see the following screen layout:
Always Pre-Interface first to make sure there aren't any errors (or even warnings) before you proceed with the Interface. The following is an explanation of each field on this screen:
1. CSV File Contains Header Row? This field defaults to the Global Setup value. You can override it to be consistent with the import file.
2. Import Item From: The file name and path default are established in Global Setup and you can be overridden. You can right-click on the field to browse to the import file.
3. Add Inv Loc Automatically ? A "Y" value will cause the import to not only create the new item record upon Interface, it will also create the associated Inventory Location (IMINVLOC) records of all locations.
4. Print Base Data? You can control how much data you want to print on the import edit list and journal. A value of "N" will only print the data provided in the CSV file. A "Y" value will print the base data columns, similar to the Item Edit List, even if the data was not provided in the import file. This option may be helpful if you created the item based on copying from an existing item (column B).
5. Print Extra Data? Similar to above. A value of "Y" will print the Extra data columns, similar to Item Edit List.
6. Sort By (S)eq No Or (I)tem No ? This field defaults to the Global Setup value and can be overridden.
7. Auto Assign UPC Code? If Elliott is configured in Global Setup to enable the UPC feature, you can automatically assign the UPC number when the import creates a new item.
8. Create BOMP Structure When Applicable ? If you provide a value in column B, Copy From Item #, the import will add the corresponding BOMP product structure if this is a manufactured item.
9. Create Kit Structure When Applicable ? If you provide a value in column B, Copy From Item #, the import will add the corresponding kit structure if this is a kit item.
10. Create Feature/Option Str When Appl? If you provide a value in column B, Copy From Item #, the import will add the corresponding Feature/Option structure if this is a Feature/Option item.
What Is Considered an Invalid Item?
The New Item CSV Import will check the provided values to make sure the import item can be created. The import will not allow an invalid item to be created. Invalid Items can occur as a result of various conditions. The following are common scenarios:
- Item Number Is Not Provided: Column A, Item No, is required. If you do not provide a value, then it is an invalid item row.
- Item Number Is Already On File: The provided item number is already on file. This utility only supports adding a new item. If you wish to update an existing item, use the "Change Existing Item Import" utility.
- Invalid Copy From Item Number: This occurs when an invalid item number is specified in column B, Copy From Item#.
- Failed to Provide Item Description 1: Column C, Desc 1, is required. If you do not provide this value, then it is an invalid item row.
- Numeric Value Size Too Large: This error occurs when the import value is larger than the field in Elliott. For example, the unit price is a maximum of 6 digits with four decimal places, 999,999.9999. If you provide a unit price value bigger than this, like 2,345,678 or 12.345678, then the import item is considered invalid.
- Negative Value at Positive Value Field: If you provide a negative value in a field that can only store a positive value, the item is invalid.
- Exceeding Maximum String Size: Exceeding the string size for some fields will trigger a warning, like item description, but most string fields will result in an invalid item when the string size is exceeded.
- End Item Code "F" without BOMP Module: If you set Item End Code to "F" (Feature), then you must use the BOMP module or it is an invalid condition.
- Invalid Foreign Key Lookup: Some columns refer to a master table and providing a value will cause the import to validate against the table. For example, a value in column J, Prime Vendor #, will cause the import to see if that is a valid vendor. If not, then this is an invalid item.
- Duplicate Vendor Item Number: A value in column K, Vendor Item #, will cause the import to see if the value is already used in the Vendor Item File. Duplicates are not allowed and will cause the item to become invalid.
Modified Programs: IMIMSITM, IMIMPCSV
EMK