Importing TPI Data To ITrack

Note: This import process only refers to importing TPI into ITrack Pro.

What is TPI?

TPI stands for Truck Parts Inventory, a competitor of our's with their own advertising network and inventory management system.

Why do we work with their data?

Several our customers use the Truck Parts Inventory system to manage their inventory in some fashion, in addition to ITrack. Sometimes, a customer may be changing systems and want their TPI data brought into ITrack. Whatever the case, if you're reading this article it's probably because you're having to do a TPI import into ITrack for a customer.

What's the easiest way to do it?

The easiest way is to use the Data Management screen in Pro. You can try to import the data straight into the database to massage it but problems can arise when trying to do that. The Data Management is the most straightforward way to accomplish the import but you will want to massage the data file before you try to import it.

Importing the External Data straight into SQLYOG

If you're familiar with SQLYOG this would be the quickest way to work with the data. However the character limits on some fields in the TPI data aren't small enough for them to be imported by SQLYOG as external data. This is probably due to how much information customers put in to certain fields. So if you can manage to get the data imported into SQLYOG so can massage it, great. Chances are you won't be able to though, and should edit the data in Excel or something, then import it using Data Management.

#Here are some example queries that might help with the import process:

#Import inventory from a CSV that has been loaded into the database
INSERT INTO inventory
(store, tagnum, typenum, `year`, `make`, model, pmanufacturer, pmodel,
vinnum, bottomprice, suggestedprice, category, `condition`, description,
label1, data1, label2, data2, label3, data3, label4, data4, side)
SELECT storenum, tagnum, typenum, `year`,
IFNULL(vehicle_make,''), IFNULL(vehicle_model,''), manufacturer, model, IFNULL(`serial/vin`,''),
bottomprice, suggestedprice, category, `condition`, description,
IFNULL(label1,''), IFNULL(data1,''), IFNULL(label2,''), IFNULL(data2,''), IFNULL(label3,''), IFNULL(data3,''), IFNULL(label4,''), IFNULL(data4,''),
IFNULL(side,'')
FROM `import_data_csv`;

#Importing vehicle files from a CSV that has been loaded into the database
INSERT INTO invmaster
(`year`,make, model, vinnum, mileage, intcolor, extcolor, bodystyle)
SELECT `year`, vehicle_make, vehicle_model, `serial/vin`, REPLACE(mileage, ",", '') AS mileage, internal_color, external_color, SUBSTRING(body_style,1,25) AS bodystyle
FROM `import_isoftCompletRecycling_csv`
WHERE `year` != 0
AND `serial/vin` IS NOT NULL
GROUP BY `serial/vin`;

#Associating inventory items to the corresponding vehicle record 
UPDATE inventory 
JOIN invmaster USING(vinnum)
SET inventory.stocknum = invmaster.`stocknum`
WHERE inventory.`stocknum` IS NULL;

What are the differences between ITrack and TPI data?

There are several, which in order to get the data into a Pro database, you're going to have to do some thinking about the data.

• TPI calls Part Type "Category" in their system.
• The "More" fields from ITrack are broken out by part type in TPI data. So EVERY inventory record contains fields for Engine Dimensions, Differential Ratios, Suspension Lengths, Wheel Diameters, etc...So for any given inventory line, there are a lot of useless, empty fields.
• The Make/Model/Manufacturer information is handled the same way. Every inventory line has fields for Engine Make, Engine Model, Transmission Make, Transmission Model...regardless of what part type you're dealing with.
• What this means for the import is: the data is spread out across the whole inventory file in different fields. Which makes importing it all cleanly in one go through the Data Management screen difficult.
• TPI make/model/manufacturer names rarely match Pro's exactly.

Massaging the Data File

• It's probably best to divide the data file into separate files for each part type. That way you can isolate the "More" field data. If you DON'T do this, and just try to import the entire file at once, you will be unable to correctly map each part type's "More" field data, among other things. There are so many part type specific fields jammed into the inventory records, if you want to preserve all the customer data you're going to have to break it apart this way.
• Convert Category Name into Typenum. You can ignore Category ID, those numbers will not agree with ITrack Typenums.
• Price = Sugggestedprice.
• Wholesale Price = Bottomprice.
• Internal Price is a TPI calculated price field. Ask the customer what they want that price to represent in ITrack, if anything.
• Truck Make/Model = Make/Model in ITrack.
• Part Make = pmanufacturer. The export file does not seem to contain a "Part Model" field, so you either pull it from Truck Model, leave it blank or ask the customer what they want to see. Alternatively use the model stated for the inventory item in question. (If there's an Engine Model value for example, use that.)
• Strip out any "Other" values for Makes/Models. We don't specify anything when there is no explicit make/model available in ITrack, so that's just useless text.
• Stock Number = Tagnum. However ask the customer if this is the # they want to use for Tag#.
• Part ID is TPI's primary key, I believe. This can probably be ignored as a data point.
• Condition = Category AND/OR Condition.

The last time we did a data import from TPI they had sent over an export file that had most names already pre-formatted. We shouldn't expect this to always be the case, especially because every data import is different, but it made things more convenient for us.

Tip

The TPI company case file has some queries that will convert TPI part types to ITrack part types

After Import

• If you inserted the data "as is", you'll want to run something like:

UPDATE `inventory` SET `pmanufacturer` = "CAT" WHERE `pmanufacturer` = "Caterpillar";

once the import has happened.

• You'll want do this for the major manufacturers and models, at least. The user is capable of doing it themselves but involves manually updating all inventory records by hand, which can be a pain for them.