Importing Retail Stock and Inventory Data from Excel into EVE

Learning Outcome

On successful completion of this course module, students will know how to import retail stock and inventory data from Excel in EVE.

Lesson

You can import stock and inventory data from Microsoft Excel into EVE.

From the top menu in EVE go to File / Import then check the Import from Excel box and click on Import where you will then see:

You must ensure that the data in your Microsoft Excel spreadsheet is in the correct format before attempting to import it.

A Microsoft Excel template (containing some example data) for is provided in the EVE import folder (C:\Program files\Integrated Scuba Systems\Import).  You can view the template by clicking the Show Template button.

The first row of each template contains column headers.

Your file does not need to include all columns listed in the template.  However, if your file does include a listed column, the column header in your file must exactly match the column header in the template (for example, your column containing the item description  must have the text ‘Description’ in the first row).  Columns in your file do not need to be in the same position as in the template.  Before importing, EVE scans your file and locates the positions of each column based on the text in the first row.  Some columns are mandatory and these columns are identified with a red header name. These are:

• Description
• StockNo
• Manufacturer
• StockType

Any row containing data in these columns will be imported into EVE, regardless of whether there is any data in any of the other columns.

Each row (after the header row) in your Microsoft Excel spreadsheet corresponds to one record to be imported.  Each column in your Microsoft Excel spreadsheet corresponds to one attribute of the record (for example, Description, Manufacturer etc.).

EVE will continue to import data until it encounters 10 consecutive blank rows. This means you can have a few blank rows in your file and EVE will just skip those rows and continue.

Once it has successfully imported a row, EVE writes an X to the first column of that row.  This is so that if the import process is interrupted, it can be resumed at a later date from where it left off.  All rows containing an X in the first column are skipped so the same data is not imported more than once.  Be careful to ensure that the first column is blank before you start the process.  It does not matter what the column header is. Column A therefore should be blank in the data file.

The level of additional detail displayed may be decided by the Manufacturer in the case of those price lists supplied directly by the Manufacturer.

Please ensure that you have the correct Cost Price Level indicated in EVE for any supplier prior to import. You’ll find this area in Inventory / Suppliers / Find and then Search for the relevant supplier

Then go to the Details tab of the supplier record and enter the Cost Price Level – whether 1,2,3, etc. as appropriate.

The Cost Price Level that you wish EVE to use by default should be set to the same as the appropriate Cost Price Level in the spreadsheet.

EVE allocates the Purchase Tax Band at the Stock Type level. You may wish to check that the Stock Types you have in EVE are associated with the correct Tax Band prior to Import. You can find this area in Inventory / Stock Types / Find and then Search for the appropriate stock type(s).

Figures entered in the Retail Price column should be before sales tax.

You may wish to check the Stock Types that are used on the spread sheet.

If they aren’t what you use in store you can either change on the spreadsheet or during the import when EVE will ask you to Create… a new stock type or Use Selected… stock type during the import.

Note: this will also happen if Regulators is what you use when the spreadsheet contains Regulator; you’ll be pleased to know that you can check a box telling EVE to apply whatever stock type you select every time EVE comes across a Regulator / Regulators issue, for example.

You can mark stock items you don’t want to import by placing an X against that / those stock item(s) in column A.

Before any import please take a Backup of your EVE database.

Handling Duplicates

You may encounter duplicates when importing data.  Some types of data in EVE require that each record has one or more unique fields.  For example, Stock Items must have a unique stock number and a unique barcode number – the stock number of an item must be different than the stock number of all other items and the barcode number must be different than the barcode number of all other items.

If you try to import a record into EVE that would violate the uniqueness requirements, EVE will prompt you how you want to continue.

In the figure below, a stock item record is being imported that has the same stock number as an existing item.  You must tell EVE how to continue by choosing an option in the action section and optionally changing the value in one or more of the white fields.  You can scroll down to see all fields on the original and import row.

Click Overwrite if you want to overwrite the existing record with data on the import record.  You must decide what you want to happen if there is no data in a particular column on the import row.  You can choose to update the corresponding column on the existing record to be blank or you can choose to leave the existing column alone.

The decision you make may depend on the circumstances: for instance, if you are importing a supplier stock list to update prices on Stock Items from that supplier, you will probably leave the No In Stock column blank on all rows in the import file.  In this case you will not want to update the No In Stock on all existing records as this would wipe out all your stock levels.

Not all columns have the option of being handled this way. For instance, columns that are mandatory can never have a blank value on the import record.  EVE will put an icon next to column names for which this handling is valid beneath the query symbol.  By default, the icon will be implying that the existing value will be left unchanged if the import value is blank.  Click on the tick to change it if you want the existing value to be blanked out if the import value is blank.

When doing a stock item import it is possible that the record being imported has the same Stock No as one existing record and the same Barcode No as a different existing record.  In this case, EVE will report that duplication errors have occurred on both stock # and barcode # and it will present both existing records to you as well as the import record (there will be two icons next to the existing item text showing a one and a two.  Click on each icon to show the corresponding existing record).  Be careful when handling this case – you will need to choose carefully whether you want to update the existing record(s) and/or the import record before resolving the duplicates.

Click Retry if you want to instruct EVE to try again to import the same record.  There is no point in doing this unless you have made a change to the data in one of the white text boxes of the import item.  For example, you could change the stock # so that it is no longer the same as any existing stock #s.

Click New if you want to instruct EVE to create a new record.  Again, there is no point in doing this unless you have made a change to the data in one of the white text boxes of the import item – otherwise you will simply get another duplication error.

Click Abort to abandon the import process.  All data already imported will remain on the database.