Import Other Data Tool

Utilities ›› Data Base Tools ››
Parent Previous Next

                                                                             IMPORT OTHER DATA

       


The Import Other Data function can be used for importing customers or materials data.  The same import options screen appears, much like Import Tickets above, but contains a check box for Overwrite Customer Balance Information.  When this option is checked, it allows imported customer balance information to overwrite existing balance information.



Format and use of Advanced Import Template.

The Advanced Importer for SMSTurbo is a powerful tool that is used to import large amounts of data directly into the SMSTurbo database using a correctly formatted Excel .csv file.  This data may come from other third party or related ticketing software. For each data table that is imported, there are two files associated with the correct format and application.  A table template file and a table format file. There is also a hierarchical requirement for some tables.  For example; the Orders table has two unique keys, CustomerID and OrderID.  To create an order, the CustomerID needs to exist in the data prior to the creation of the order.  Further, the rate table has 3 unique keys, CustomerID, OrderID, and MaterialID.  Thus to create a special rate for a material in an order, the OrderID needs to exist.

Explanation of import files and their creation in Excel.

Table Template file

The example below is for the Orders import.  As SMSTurbo evolves and adds features, the database evolves with it creating changes to the layout of the data.  For each database version, the layout is listed, and the table keys are noted.  These keys, or key sets need to be unique.

The template file has the format for the data to be imported into the table within SMSTurbo.  You should note the special word, 'KEY' in the first row.  This indicates that the data in this column is the reference handle for the data, and must be unique to distinguish each row (Some tables have more than one key, discussed later).

Notice that a few of the cells are highlighted in red.  This indicates that the data MUST be present.  For columns that contain the same information for the entire dataset, a default value can be set in the import layout (shown below).

Each column of data has its own rule for what is allowed and is defined in the Format file.  

Key value explanation

Several tables are indexed by 2 KEY values.  In a similar fashion, the combination of the two KEYs must be unique.  For example;


       KEY 1                KEY 2                Other data

       ABC                XYZ                remaining data

       ABC                OPQ                this data

       ABC                STV                even more data

       JKL                XYZ                continued data

       JKL                TMX                yet more data


All the above combinations are permitted.  Even though XYZ is used twice, the combination of the first KEY makes the entry unique.

Remember that fields that are used for KEYs should only contain alpha-numeric values, and no spaces.


When the intent is to ‘link’ information from one table to another such as orders, the KEYs need to be identical.  For example;

You have created an entry in the customer table;


CustomerID

Name

Address1

City

State

Zip

A00001        

A1 Hauling

P.O. Box 12        

San Jose                      

TX                  

94707    


       In the order table, the order is linked to this customer.  


CustomerID

OrderNum

Description

Address1

City

State

A00001        

123

COMLCASH      

8200 OGONTZ AVE              

MALVERN                      

NJ                  


Using excel to test your data;


Conditional formatting to highlight trouble cells:

Occasionally a customer will provide a spreadsheet to be imported that does not fit our design rules for our tables.  The majority of the problems come from exceeding the length of a field.  One suggested method to help the customer is to add a column that tests the field length and marks the row.  Using the formula;


=IF(OR(LEN(J738)>30,LEN(K738)>30,LEN(L738)>30,LEN(M738)>30),1,"")


This tests the cells for exceeding length.  If any of the lengths are exceeded, a ‘1’ is entered in the new cell.  A summation of the column can then be made to determine how severe the corrections are.  Adding conditional formatting can locate the offending cells quickly.   The images below are examples.



Use the first cell in the range to create the formula



Special Character excel macro


This is a macro that can be run to highlight invalid characters.  You will need to know how to add macros to excel.


Background information: A customer had sent a truck table where the IDs had far too many duplicates.  They were amenable to creating new IDs.  A column was created for the SMSTurbo TruckID by concatenating the first 3 characters of their ID with the given Truck_No.  Column E contains the test for invalid characters, using the IsSpecial macro.




Public Function IsSpecial(s As String) As Long

   Dim L As Long, LL As Long

   Dim sCh As String

   IsSpecial = 0

   For L = 1 To Len(s)

       sCh = Mid(s, L, 1)

       If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Or sCh = "-" Then

       Else

           IsSpecial = 1

           Exit Function

       End If

   Next L

End Function



Table Format file

Each data element can have a different set of requirements for the data.  This file details the limitations and formatting for each element.  There are notes that explain the meanings for the Type of data each element may be.


Table Layout mapping

Once the data has been created, it must be saved in CSV format (common separated values).  It is VERY useful to include the column headers in the CSV file, so that the mapping tool can easily locate the correct columns.

Open the advanced Importer and select the CSV file.  Check off the appropriate boxes for the file you are importing.  Click the green check to continue.

ADVANCED IMPORT MAPPING

The Advanced Imported will open up the mapping tool.  Here is where the columns from the CSV file are mapped into the table.  If you would like to use a default value for ALL imported rows, set the ‘Map to’ value to ‘USE SET DEFAULT VALUE’ and enter the desired value in ‘Default Value’.  Once you have created a layout, save it, for possible use later.  The layout can also be exported as an SQL script for use on other SMSTurbo instances.