User:Rmccown - Revision history

Rmccown: Blanked the page

2019-07-03T17:40:34Z

Blanked the page

← Older revision		Revision as of 12:40, 3 July 2019
Line 1:		Line 1:
	~~Note: Since I don't have permissions for creating new pages, I'm putting info here first.~~

	~~== TPI Exports and Their Quirks ==~~
	~~Companies.xlsx~~
	* May come with two tabs of data: one that appears to have collapsed/multiple rows per customer, a second called "Flat Data"
	* There may be duplicates, depending on how the customer entered their data. Strangely, you may see multiple almost identical rows with important information spread between the multiple entries.
	* In the example referenced for this wiki entry, the company file included vendors and customers as indicated in the `Company Type` column with the following types: Customer, E-Commerce, Other, Service Provider, Supplier

	~~Myinventory-xxx.xlsx~~
	* `PartID` appears to be the unique ID from TPI
	* Make, Model, Manufacturer, and additional information fields are spread out across 50+ columns for every part type which will make later steps a bit more difficult
	* Some cells in the original exports may have carriage returns which can make import into SQLyog behave unexpectedly
	* Despite having an `On Hand` column, this information seems to conflict with another file called something like 'inventory_deleted.xlsx' that will need to be resolved later

	~~Inventory_deleted.xlsx~~
	* This file appears to show a list of inventory items that have been deleted, sold, or "adjusted"
	* A column labeled `Transaction` may be the most useful and has the following options for each entry: Add Inventory, Invoice, Stock Adjsutment, Credit Note, Purcahse Order, Item Receipt, Build Order, Sales Receipt

	~~Trucks_xxx.xlsx~~
	* `TruckID` appears to be the unique ID from TPI
	* As with the inventory export, some of the cells have extra lines
	* The only reference between this and the inventory table is the vehicle's `Lot Number` and in most cases has a similar VIN. Unfortunately, there's no checks for duplicates or spelling so the data doesn't match up in all cases

	~~AR-AgingReport-xxx.csv~~
	* This file had the most obvious information for company names and balances
	* Due to the number of duplicates in the 'companies' spreadsheet, the files don't pair up very well and may have to be entered manually.

	~~== TPI excel sheet cleanup ==~~
	* Before importing the raw csv or xlsx sheet from TPI into SQLyog, it may be worth running the =CLEAN() function in Excel to remove line breaks
	* The allowable field length for TPI data doesn't match up well to Enterprise so longer fields like description or notes will either need the CLEAN function or most likely a LEFT() in SQLyog queries

	~~== TPI Category ID ==~~
	* TPI Category IDs will need to be mapped to the inventorytypeid used in Enterprise and by HTP.net
	* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'

	~~{{Tip\|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion ATI conversion case file] has an sql script that will convert TPI part types to ITrack part types}}~~

	~~== Importing the External Data straight into SQLYOG ==~~

Rmccown at 17:01, 3 July 2019

2019-07-03T17:01:13Z

← Older revision		Revision as of 12:01, 3 July 2019
Line 37:		Line 37:

	== Importing the External Data straight into SQLYOG ==		== 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.

	~~<source lang='mysql'>~~
	~~#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`;~~

Rmccown at 21:17, 2 July 2019

2019-07-02T21:17:40Z

← Older revision		Revision as of 16:17, 2 July 2019
Line 1:		Line 1:
	Note: ~~This import process only refers to importing TPI into ITrack Enterprise~~.		Note: Since I don't have permissions for creating new pages, I'm putting info here first.

	== TPI Exports and Their Quirks ==		== TPI Exports and Their Quirks ==

Rmccown at 21:13, 2 July 2019

2019-07-02T21:13:47Z

Rmccown at 21:02, 2 July 2019

2019-07-02T21:02:47Z

← Older revision		Revision as of 16:02, 2 July 2019
Line 34:		Line 34:
	* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'		* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'

	{{Tip\|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion ATI conversion case] has an sql script that will convert TPI part types to ITrack part types}}		{{Tip\|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion ATI conversion case file] has an sql script that will convert TPI part types to ITrack part types}}

	== Importing the External Data straight into SQLYOG ==		== Importing the External Data straight into SQLYOG ==

Rmccown at 21:02, 2 July 2019

2019-07-02T21:02:29Z

← Older revision		Revision as of 16:02, 2 July 2019
Line 34:		Line 34:
	* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'		* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'

	{{Tip\|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion ~~company~~ case] has an sql script that will convert TPI part types to ITrack part types}}		{{Tip\|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion ATI conversion case] has an sql script that will convert TPI part types to ITrack part types}}

	== Importing the External Data straight into SQLYOG ==		== Importing the External Data straight into SQLYOG ==

Rmccown: Created page with "Note: This import process only refers to importing TPI into ITrack Enterprise. == TPI Exports and Their Quirks == companies.xlsx * May come with two tabs of data: one that ap..."

2019-07-02T21:01:40Z

Created page with "Note: This import process only refers to importing TPI into ITrack Enterprise. == TPI Exports and Their Quirks == companies.xlsx * May come with two tabs of data: one that ap..."

New page

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

== TPI Exports and Their Quirks ==
companies.xlsx
* May come with two tabs of data: one that appears to have collapsed/multiple rows per customer, a second called "Flat Data"
* There may be duplicates, depending on how the customer entered their data. Strangely, you may see multiple almost identical rows with important information spread between the multiple entries.
* In the example referenced for this wiki entry, the company file included vendors and customers as indicated in the `Company Type` column with the following types: Customer, E-Commerce, Other, Service Provider, Supplier

myinventory-xxx.xlsx
* `PartID` appears to be the unique ID from TPI
* Make, Model, Manufacturer, and additional information fields are spread out across 50+ columns for every part type which will make later steps a bit more difficult
* Some cells in the original exports may have carriage returns which can make import into SQLyog behave unexpectedly
* Despite having an `On Hand` column, this information seems to conflict with another file called something like 'inventory_deleted.xlsx' that will need to be resolved later

inventory_deleted.xlsx
* This file appears to show a list of inventory items that have been deleted, sold, or "adjusted"
* A column labeled `Transaction` may be the most useful and has the following options for each entry: Add Inventory, Invoice, Stock Adjsutment, Credit Note, Purcahse Order, Item Receipt, Build Order, Sales Receipt

trucks_xxx.xlsx
* `TruckID` appears to be the unique ID from TPI
* As with the inventory export, some of the cells have extra lines
* The only reference between this and the inventory table is the vehicle's `Lot Number` and in most cases has a similar VIN. Unfortunately, there's no checks for duplicates or spelling so the data doesn't match up in all cases

AR-AgingReport-xxx.csv
* This file had the most obvious information for company names and balances
* Due to the number of duplicates in the 'companies' spreadsheet, the files don't pair up very well and may have to be entered manually.

== TPI excel sheet cleanup ==
* Before importing the raw csv or xlsx sheet from TPI into SQLyog, it may be worth running the =CLEAN() function in Excel to remove line breaks
* The allowable field length for TPI data doesn't match up well to Enterprise so longer fields like description or notes will either need the CLEAN function or most likely a LEFT() in SQLyog queries

== TPI Category ID ==
* TPI Category IDs will need to be mapped to the inventorytypeid used in Enterprise and by HTP.net
* An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'

{{Tip|The [https://isoftdata.fogbugz.com/f/cases/35902/ATS-AZ-Data-Conversion company case] has an sql script that will convert TPI part types to ITrack part types}}

== 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.

<source lang='mysql'>
#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`;

← Older revision		Revision as of 16:13, 2 July 2019
Line 2:		Line 2:

	== TPI Exports and Their Quirks ==		== TPI Exports and Their Quirks ==
	~~companies~~.xlsx		Companies.xlsx
	* May come with two tabs of data: one that appears to have collapsed/multiple rows per customer, a second called "Flat Data"		* May come with two tabs of data: one that appears to have collapsed/multiple rows per customer, a second called "Flat Data"
	* There may be duplicates, depending on how the customer entered their data. Strangely, you may see multiple almost identical rows with important information spread between the multiple entries.		* There may be duplicates, depending on how the customer entered their data. Strangely, you may see multiple almost identical rows with important information spread between the multiple entries.
	* In the example referenced for this wiki entry, the company file included vendors and customers as indicated in the `Company Type` column with the following types: Customer, E-Commerce, Other, Service Provider, Supplier		* In the example referenced for this wiki entry, the company file included vendors and customers as indicated in the `Company Type` column with the following types: Customer, E-Commerce, Other, Service Provider, Supplier

	~~myinventory~~-xxx.xlsx		Myinventory-xxx.xlsx
	* `PartID` appears to be the unique ID from TPI		* `PartID` appears to be the unique ID from TPI
	* Make, Model, Manufacturer, and additional information fields are spread out across 50+ columns for every part type which will make later steps a bit more difficult		* Make, Model, Manufacturer, and additional information fields are spread out across 50+ columns for every part type which will make later steps a bit more difficult
Line 13:		Line 13:
	* Despite having an `On Hand` column, this information seems to conflict with another file called something like 'inventory_deleted.xlsx' that will need to be resolved later		* Despite having an `On Hand` column, this information seems to conflict with another file called something like 'inventory_deleted.xlsx' that will need to be resolved later

	~~inventory_deleted~~.xlsx		Inventory_deleted.xlsx
	* This file appears to show a list of inventory items that have been deleted, sold, or "adjusted"		* This file appears to show a list of inventory items that have been deleted, sold, or "adjusted"
	* A column labeled `Transaction` may be the most useful and has the following options for each entry: Add Inventory, Invoice, Stock Adjsutment, Credit Note, Purcahse Order, Item Receipt, Build Order, Sales Receipt		* A column labeled `Transaction` may be the most useful and has the following options for each entry: Add Inventory, Invoice, Stock Adjsutment, Credit Note, Purcahse Order, Item Receipt, Build Order, Sales Receipt

	~~trucks_xxx~~.xlsx		Trucks_xxx.xlsx
	* `TruckID` appears to be the unique ID from TPI		* `TruckID` appears to be the unique ID from TPI
	* As with the inventory export, some of the cells have extra lines		* As with the inventory export, some of the cells have extra lines