Preparing Your Data For Importing Into BarScan

Introduction

Importing into BarScan can originate from many different computer applications.   For each of your imports, you will need to create an Import Format in BarScan.  The Import Format provides detailed instruction to BarScan so that it can properly understand the external file that you wish to import and how the file will populate the corresponding BarScan database.

So what is an Import Format?

The Import Format is a set of instructions that you create in a BarScan Screen that tells BarScan how you wish to import your data.  It does not involve creating any computer code, rather it takes your instructions on how to import and applies them to your file.

Once you have created your Import Format instructions, you can use it over and over again.  This is especially useful if you import information regularly, i.e. on a regular monthly basis.

You can even use BarScan’s XML Instruction File feature to execute recurring imports in a fully automated timed manner.

Creating an Import Format

You can only import to one BarScan table at a time, however, it is not uncommon to import to several BarScan tables as part of one import process.  For example, to add a new asset, you will import into either the Transaction Table, or the Asset and Location History Table.  To import into the Asset and Location History Table, you will create an Import Format for each table.

BarScan will not allow you to import information if it means that the information creates an incomplete asset record.  For example, BarScan will not allow you to import new asset information into the Asset Table if you are not also importing the corresponding Location History, Catalog, and Location, information.  Of course, you can import new asset information if the corresponding Catalog, Location, and Organization information already exists in their respective tables.

For this reason, it is often easier to import new asset information into the Transaction Table as the import rules are less restrictive.

The Import Method

The are two Import Methods supported, File and OLE (Object Linking and Embedding).  Since OLE is a Microsoft standard, it is only supported in imports from Microsoft Excel or ODBC (Open DataBase Connectivity).

ODBC is a standard database access method developed by Microsoft Corporation.  The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to translate the application’s data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant.  That is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them. Since version 2.0, the standard supports SAG SQL.

Preparing Your Data

While you can prepare your data in a number of different formats, e.g. delimited, SDF, etc., a structured file like Microsoft Excel is the most popular. The following are some important Excel format issues.  These are not BarScan import issues.

While BarScan can import from multiple Excel Files and multiple sheets within each Excel File, it is much easier to combine all of your data into one Excel File of only one sheet.  This reduces the amount of work in creating your Format Files.  Of course, in cases where different Excel Files contain different pieces of information that different, you would not combine them.  For examples, one Excel File can contain your assets and another Excel File contain your list of all employees.

If the column width in your spreadsheet is narrow enough to hide data, the hidden data may be cut off.  Excel is exporting your data based on what you see on your screen.  This problem exists in older  versions of Excel.  Stretching the columns to display all of the data while you are still in Excel resolves this problem.

In order to avoid other potential problems, it is important to format all of your columns in your spreadsheet as either “Text”, “Date” or “Number”.  Do not leave them in the Excel default “General”.  While not formatting your columns may have no impact, it is always a good habit to take this extra step.  For example, if you do not change a column containing dates to be a “Date” field, internally Excel saves the information as a number and would also export the information as a number rather than a date.  BarScan will not import a number into a date field and you would be unexpectedly left with a blank date.

Have only one row of column titles at the top of your spreadsheet.  Making them explanatory is very helpful since these titles are used in the Format File.  For example, use titles like “SerialNo”, “PurchaseCost”, “GrantNumber”.

Other than one top row of titles, it is best to keep titles, boxes, graphics, calculations, totals, and other formatting out of the Excel Files that you will be importing into BarScan.  Keep you spreadsheet as simple as possible.
BarScan can ignore columns during the import.  There is no need to delete columns that you do not wish to import so you need not spend time removing columns.

BarScan can also ignore rows during the import.  For example, you can instruct BarScan to not import an asset if the value column shows that the value of that asset is less than $500.

BarScan can adjust its data structure to your Excel File data.  Using our User Defined Fields Module, you can stretch or shrink or format or rename any field in BarScan.  For example, if you have an Excel File column called ‘Vehicle License Plate’, you can create a new field in BarScan to hold this information.  You can even create special rules for this field such as ‘The License Plate field must be blank if the asset is not a vehicle’.  Finally, it is important to remember that if you rename a field, any information in the BarScan documentation will still refer to the old field name.

BarScan Main Tables

There is a minimum number of three tables that you need to import into in order to create your BarScan database.  These are:

Asset Table (usually via the Transaction Table)
Catalog Table
Location Table

Here are some items to consider for each of the three tables.

Asset Table – at a minimum, you will need an asset number OR quantity and the asset’s description.  You do not need to have an asset’s location, although in most cases this is desirable.

Catalog Table – This is where some effort might be needed to format your Excel File information to match our Catalog Table.   This table stores all of your asset’s descriptions in the most uniform way possible.  This is done by separating the descriptions into parts.  The main parts are the Category, Type, Description and Manufacturer.  While filling in all of these fields is not mandatory, it is desirable.   In most cases, it is easier to do this work in your Excel File prior to import, rather than after you import.  Here is an example.  You have two descriptions as follows:

Dodge Cargo Van
RAM ProMaster Cargo Van

These happen to be the same vehicle but the descriptions were created in a non-uniform way which can cause confusion if the descriptions were imported as-is.  This is how the data should be adjusted in your Excel File for both descriptions:

Category: Vehicle
Type: Van
Description: RAM ProMaster Cargo
Manufacturer: Dodge

Location Table – Like the Catalog Table, BarScan separates the location information into parts.  While filling in all of these part is not mandatory, it is desirable.  The main parts are the Region, Building, Floor, and Room.  Optional fields are the Room Tag (barcode to identify the location) and Location Description.  If your location is one building with one floor, a Region and Floor would not be necessary in your case.  Locations can also include ‘In Transit’, ’At Vendor’, ’Home Office’, etc.  What ever meets your needs.

BarScan has a separate schema for warehouse locators which are not discussed in this Knowledge Base but for warehouses, the above information is superseded.     Other Tables – Depending on your data, you may also wish to populate additional tables such as the Personnel Table, Organization Table (for cost centers), Work Order Table, etc.

BarScan Validation Rules

BarScan applies it’s validation rules to your Excel File during the import process.  Depending on the severity of the problems in your Excel File, the import may be rejected or you may be asked to do some corrections manually.  For example, two different assets cannot share the same asset number.  While you do not have to make sure that your data is spotlessly clean, obvious issues should be addressed prior to the import.  If you import is rejected, a Log Report can be generated showing the reason(s) why.

Images

BarScan supports images linked to Assets, Catalogs, etc. and of multiple types, e.g. jpeg, pdf, etc. Images cannot be imported with Format Files. Individual images can be imported directly from the BarScan application once you complete your data import.

ODBC Linking

Do not use an ODBC link to change BarScan data as we cannot provide technical support for your BarScan system. A better way to import is to use the Import/Export Module, as BarScan will automatically check all data for compatibility prior to doing the import. Importing data directly into BarScan using methods other than the Formatted Data is not encouraged and is not supported.

For More Information

This Knowledge Base is designed as an introduction for preparing your data for importing.  There is additional information in the BarScan User Manual Chapter 14 – Import and Export and in this website Knowledge Base.