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