Inventory Master Export / Import Utility

This utility provides the ability to...

Add new Inventory Master Records via a CSV file.
Update (virtually) all editable fields in the Inventory Master file via a CSV file.

This utility is free for use by licensed HITS BPOS clients on support. However, due to the complexity and power of this utility, ancillary support fees for assistance may be chargeable. Only clients with a thorough knowledge of Excel, OpenOffice, or other CSV compatible spreadsheet programs should attempt to use this utility.

Access to this utility requires that password permission PN11 be set as follows:

PN11=1 Permits general access and use of the utility
PN11=2 Permits the user to update Average Cost even when there is existing quantity on-hand or in-process quantities for an item. A user with this permission understands and accepts that certain Inventory Value reports will be affected by changing Average Cost on items with quantities other than zero.

The utility provides three primary modes:

Export of a "template" CSV (for use when creating a new CSV of items) (See below for sample template snippet.).
Export of existing Inventory Master items based on selection criteria.
Import of data from a (compatible) CSV file to the Inventory Master file.

Sample (snippet) of a template CSV from this utility:

Downloading and Uploading

By default, the download and upload function uses the filename DATAX.CSV as the CSV file. However, you can change the filename saved to your location workstation. When up upload your local CSV file, the utility will automatically change the name back to DATAX.CSV for processing. Do not attempt to upload a .xls or .xlsx file. This utility only works with the CSV file type.

Import Options

Prior to updating your Inventory Master file using the Import option, there are two options you may wish to consider using as follows:

View - The View option will display the contents of your uploaded CSV in a "raw" state. This is simply to give you the ability to spot check and ensure that the CSV file you intended to upload for import is in fact the correct file.
Proof - The Proof option performs all the same checks and validations that are performed during an actual "import" without updating any Inventory Master file data. When a Proof or actual import is performed, this utility builds an ERROR log. By reviewing the ERROR log of a Proof, you and see what data may need to be corrected. Therefore, we strongly recommend that you run the Proof option every time before you do an import. You can run the Proof option, review the ERROR log, edit your CSV, and re-run the Proof as many times as needed to fine tune the accuracy of the data to be added or updated to your Inventory Master file. (More on the ERROR log further below.)

How The Import Works

An exported CSV, whether as a template or regular export of existing items, is created with two header rows as follows:

Row #1 is the master "header" row. It contains the field names of the fields in the Inventory master file that can be added or edited by this utility. Do NOT change the names on the header row or the import (and Proof) will fail. This utility uses these header row names to know what data to update and where in the Inventory Master file.
Row #2 is the "specifications" row. Row#2 provides the requirements of each field in order for data to be correctly imported to the Inventory Master file. This specifications row is provided to assist you in understanding the technical requirements of the data that can be updated to each field. The specifications row is comprised of three specification types:
1. _ASC(5) - Indicates a field that can contain letters, numbers, and (in some cases) special characters. The "ASC" stands for ASCII, a computer term for text and number data. The (5) indicates that the field can contain up to 5 characters (letters, numbers, special characters). There are ASCII fields of various lengths in the Inventory Master file. For example (5), (10), (20), (25), etc., are all listed as ASCII field length specifications.
2. _NUM(6.2) - Indicates a numeric only field of data. The (6.2) indicates that the value of the number can be up to 6 places to the left of the decimal and 2 places to the right of the decimal. There are numerous decimal formats used in the Inventory Master file. For example, (6.2), (8.2), (10.2), etc. are all listed as field specifications for various numeric fields in the Inventory Master file.
3. _DATE - Indicates a date field with a format of MM/DD/YYYY such a 10/15/2020.

Upon outputting your first template or regular export, you will notice that the field names on row #2 are prefixed with an underscore. For example, instead of "ASC(5)", the field name is output as "_ASC(5)". This makes it easier to sort your spreadsheet by whatever column you wish for analysis or editing purposes, and then so you can re-sort the CSV back to its original order, if needed. You can leave row#2 in place or delete it if you wish. If the import utility encounters the specifications row#2 anywhere in the CSV file, it will simply ignore it as a non-import row.

Notes, Warnings, and Instructions

Do NOT insert commas within a field (cell) of data in the CSV. CSV stands for "Common Separated Values". That means that each column of data is separated (within the CSV) with a comma when saved by your spreadsheet program. If you "embed" a comma in something like a description, size, etc., it will cause this utility to fail.
Do NOT use dollar signs ($), trademark symbols, copyright symbols, or such. This will cause the utility to fail to import the data from that column. Use the formatting commands in your spreadsheet program to format any fields with dollar signs to be just numeric fields. Other than commas and dollar signs, you can use pretty much any of the other special characters on a standard Windows keyboard.
A blank cell will delete data! Example: If you have a value for Price4 for an item and you include the Price4 column in your CSV with no entry or a zero for Price4 for that item, Price4 will be set to zero. Be careful. (Required fields such as TAX, Goods/Services, Stock, etc. are blocked from blank cell edits.)
ALL lowercase character data (aka: letters) get uppercased during import. If you enter all or part of a (cell) of data in the CSV file in mixed case, that cell of data will get uppercased on import.
This utility CANNOT be used to CHANGE Product#s (SKUs). We have a separate utility for changing SKUs that uses a two column CSV of existing SKU and new SKU. Contact the HITS Technical Support Center for information and assistance with that utility.
Data Validation - This utility will attempt to validate various aspects of the data in your CSV to ensure it is compatible and correct. For example, The entry for the TAX(able) field in the Inventory Master can be either "Y" or "N". If you attempt to import other than "Y" or "N", either intentionally or accidentally, the utility will not update the TAX field.
ERROR Log - As mentioned earlier, this utility creates an ERROR log (DATABAD.CSV), which is itself a CSV. When an import completes, you will be prompted to download and check this log to see which SKUs were not updated and why. The ERROR log will list entries as either "Warning" or "FAILED". An example of a "Warning" would be where your CSV contains an entry that is too long to fit in the field in the Inventory Master. For example, the size field in the system is currently limited to 13 characters. If your CSV contains an entry for size that is longer than 13 characters, that row on the ERROR log CSV will be listed as a "Warning". The utility will update the size with the first 13 characters, but still report the error as a "Warning" for your information. Correct "Warnings" and re-import at your discretion. On the other hand, if a field cannot be updated because of required or invalid data, that field will be listed in the ERROR log as "FAILED". For example, if you attempt to update Price1 for a SKU with a price that is larger than can be saved to the Price1 field, the field will NOT be updated and the ERROR log will report that entry as "FAILED". That means that the ERROR log can contain multiple entries for the same SKU; some that are just "Warnings" and some that are "FAILS". "FAILED" entries should be corrected in the DATAX.CSV and re-imported. Below is a sample ERROR log (DATABAD.CSV).
Selected Columns - Often times users only want to update a single column of data, or just a few selected columns. This utility supports up to 80 columns of Inventory Master fields, and it will output all 80 when the export option is used. However, it is not necessary to keep all the columns in the CSV. In fact, other than the columns of data you wish to update, the only column that is required to stay on your CSV and stay in its original position is the 1st column, Product_num column (aka: Product# or SKU). You can delete any other columns from a template or exported CSV that you do not need to edit or use for reference.
Order of Columns - You can move columns to facilitate easier editing. When you go to import the data, the utility will figure out what columns remain on the CSV and to which field in the Inventory Master file each column of data should be updated (mapped), regardless of column position. The exception is that you should NOT move the Size, Description, and Mfg# columns or the Raw Size column to be out of sequence order with each other. The Special Size and Description functions described further down in this document rely on being in sequence. For example, if you move the Description column to be in front of the Size column and then use the special function to build Size data based on the first 13 characters in the Description column, processing on the Size column will overwrite the Size data created from the Description column.
Ignore Processing Option -By default, the Size, Description, and Mfg# (aka: extended description) columns are NOT processed by this utility. However, you can override this default to enjoy the extended functions. (Special Size and Description options are covered further down in this document.) Of course, you can also simply remove the Size, Description, and Mfg# columns from a DATAX.CSV if you do not need them for reference.
Product# "padding" - When you use the Export option to extract Inventory Master data from your system, any Product# that has a leading zero(es) will be prefixed with a left parenthesis "(" (aka: parens). This is called "padding" and it is done to prevent spreadsheet programs from automatically removing leading zeroes on Product#s. You do not need to remove the left parens. They will be automatically stripped off during import. Also, you do not have to include a left parens on new Product#s that do not have leading zeroes. This utility only strips left parens from Product#s (and the Alt and Mfg-SKU columns) that are found as the first character of the Product#.
Start with a small number of rows. This utility is very powerful and while it will attempt to validate as much data as possible, it cannot catch everything. Therefore, we recommend that you start out with just a few rows of SKUs, make some edits, import them, and check the fields. If you do make a major mistake, do NOT try to fix it on your own. Call the HITS Technical Support Center. Depending on the data to be corrected, we may be able to do a partial retrieval of data from a previous day's backup. Please note that ancillary charges may apply.
Oil Grades. If you are using the VSOC (Vehicle Specific Oil Change) module in ServiceCAT and wish to import oil grades, put your two character oil grade codes in Column M - Load Range. The Load Range field stores and displays oil grades when Rawsize=OIL.

Special Size and Description Options:

When adding new or updating existing inventory master records from external sources, such as a tire manufacturers' product list spreadsheet, the Size and Description data in the external source rarely matches the format of the Size and Description of the HITS BPOS Inventory Master file for those same fields of data.

This utility therefore provides some special operation capabilities that enable you to capitalize on external data sources for adding or updating Size, Description, and Mfg# data, as well as building "raw size" data from either the Size of Description columns. These special capabilities include the ability use the DATAX.CSV to:

Create Size data from the Size column (up to 13 characters)
Create Description data from the Description column (up to 29 characters, 42 characters if also using Description to build Size data)
Create or Update "Raw Size" from the Size column (with the option to skip existing items with a Raw Size)*
Create Size data from the Description column (using up to the first 13 characters)
Specify a "character position" in the Description column from which to build Description data
Create or Update "Raw Size" from the Description column (using up to the first 10 characters with the option to skip existing items with a Raw Size)*
Force the updating of "Raw Size" from the Size column.*
Force the updating of "Raw Size" from the Description column (using up to the first 10 characters)*

* The above Raw Size options are only available when your DATAX.CSV does NOT include a Raw Size column.

Reminder: In order to utilize these special Size, Description, and Raw Size options, you must leave these three columns in their original sequence order in your DATAX.CSV file. Changing the order of these columns relative to one another will yield unexpected results.

How To Access The Utility

Currently, this is a "legacy" mode utility that will eventually be converted to BPOS (point-and-click). If you are not comfortable using legacy mode or are not familiar with a spreadsheet program such as Excel or OpenOffice, do not attempt to use this utility.

To access this utility, on the HITS BPOS main menu, click the File menu option in the upper left hand corner of the BPOS screen and take the option to "Launch M/S HITS". Then, from the HITS M/S (legacy) menu, take option #6 - Run A Program, and enter the program name: SSINVMST. After the program launches, follow the screen prompts provided. (This legacy utility will be added to a HITS BPOS utility menu in a future release.)

Please report any issues or errors you discover using this utility.