Insert Items

(InsertItems.exe) Program will allow the user to insert items into the Inventory Master File from a comma delimited (CSV) file, provided the file is in the correct format.

 

Under Tools, there is a selection, Create Template File, which will create the file location EhsInsertItemsTemplate.csv in the proper file path and a Warning screen will display the file name and state that it has been created. The user should write down the path that is shown in the screen so you know where to find it. Locate the file, and then double click on the file and it will open a spreadsheet with the column headers already labeled in the correct format.

The BEST way to set the format for all but pricing is as a TEXT.

Pricing is best set as currency. No symbol. Decimal is set to 2 places.

 

The correct format is to list the columns as follows, and the Column Headers must be included with the exact spelling:

ADD_UPDATE_DELETE

MAT_CODE

VENDOR_ID

DESCRIPTION1

VENDOR_CATALOG

UNIT_PURCHASE,

CONVERSION

UNIT_COST

MFG_CATALOG

MFG_NAME

LOCATION

ACCOUNT_NO

SUB_ACCOUNT

STOCK_TYPE

BIN

BIN2

BIN3

UNSPSC_SEGMENT

UNSPSC_FAMILY

UNSPSC_CLASS

UNSPSC_COMMODITY

PRODUCT_CLASS

IMF_ACTIVE

LOC_ACTIVE

PATIENT_CHARGE_NO

PATIENT_CHARGE

MINIMUM

MAXIMUM

REORDER_POINT

REORDER_QUANTITY

DESCRIPTION2

CONTAINS_LATEX (Y,N, or blank if not sure)

 

***If any field except DESCRIPTION1 is blank, the system will populate as blank.

Column Descriptions:

ADD_UPDATE_DELETE: Must be either A, U, or D   

MAT_CODE: If adding items and the MAT_CODE is blank Material Codes will automatically be generated based on the following System Options:

 MAT_CODE_PREFIX – stock Mat_Code prefix

NEXT_MAT_CODE - stock Mat_Code

NMAT_CODE_PREFIX – non-stock prefix

NEXT_NON_STOCK_MAT_CODE – next non-stock mat code

 

If adding items and the MAT_CODE is not blank, the program will try to use the entered MAT_CODE. If the entered MAT_CODE already exists in the system, an error will be generated.

  

VENDOR_CATALOG: If updating or deleting an item and the MAT_CODE is blank, the program will find the item by the VENDOR_CATALOG.

 

UNIT_PURCHASE: The Unit of Purchase MUST exist in the Unit of Measure Master File.  

  

STOCK_TYPE: Must be either:

 S Stock

 N Non-Stock  

 R Reusable

 D Direct

 C Consignment

 

1. To convert an excel file to a .CSV file:

• Remove or Replace ALL comma’s from excel file.
• If there are zeroes preceding any number the user must change the format on that cell to Text. This is an Excel rule to hold the zeroes.
• Choose File, Save As, Change the type of file to .CSV, click Save.

 

1. To import file:

• Run the Insert Items program (InsertItems.exe)
• Choose the .CSV file you just created from the "Choose the file you want to import" field or by clicking on the File icon to the right and selecting the file.
• Click Process, a Warning screen displays with the number of Records Processed, Records Added, Records Updated, and Records Deleted. Press OK when completed.

 

1. To view errors:

• Once processing is completed you can view any errors encountered by opening the .CSV file. Errors will display in the ERRORS column which is the farthest to the right on the spreadsheet (next to CONTAINS LATEX column).
• You can then modify the error records and reprocess the .CSV file.
• Note that the A (Add) records will now have the associated Material Code in the MAT_CODE field.