|Deletions are marked like this.||Additions are marked like this.|
|Line 50:||Line 50:|
|mdb-export -I manufacturer.mdb WMI | sed -e 's/)$/)\;/' > WMI.mysql||mdb-export -I manufacturer.mdb WMI | sed -e 's/)$/)\;/' > WMI.mysql
mdb-export manufacturer.mdb WMI >WMI.csv
How to Decode a VIN
Initial explanation taken from [http://www.angelfire.com/ca/TORONTO/VIN/VINcode.html Source1], [http://www.autoinsurancetips.com/decoding-your-vin source2]
When decoded, the VIN tells the country and year of manufacture; make, model and serial number; assembly plant; and in some cases it even identifies equipment specifications. The system goes something like this: sequence for characters is first A to Z, then 1 to 9, and last 0. The letters "I", "O" and "Q" are typically omitted from system. [http://www.access.gpo.gov/nara/cfr/waisidx_05/49cfr565_05.html VIN USA Requirements]
The vehicle identification number is divided into four parts:
- The World Manufacturer's Identification (WMI - three characters);
- The Vehicle Description Section (VDS - five characters);
- The check digit;
- The Vehicle Identification Section (VIS - eight characters).
When decoded, the VIN tells the
- Year of manufacture;
- Serial number;
- Assembly plant
- Some equipment specifications.
World Manufacturer’s Identification (WMI)
[http://www.nhtsa.dot.gov/cars/rules/manufacture/ NHTSA Manufacture's Database] [http://www.sae.org/standardsdev/groundvehicle/vin.htm SAE Standard $500 for a database]
The first character in the WMI sequence represents the country where the vehicle was manufactured. Countries like the United States (1 or 4), Canada (2) and Mexico (3) are represented by numbers while other countries such as Germany (W), Italy (Z) and Japan (J) are represented by letters.
The second character refers to the manufacturer. The characters can be either letters or numbers. For example: Jaguar (A), Dodge (B), Chrysler (C), Jeep (J), Buick (4), Cadillac (6) or Saturn (8).
The third character represents the vehicle type or manufacturing division.
apt-get install mdbtools
- [Optional] Graphic fronted so you can browse the web.
apt-get install mdbtools-gmdb
- Create mysql database and then tables
mysqladmin -u root -p create manufacturer mdb-schema database.mdb | mysql -u username -p database_name
mdb-export -I manufacturer.mdb WMI | sed -e 's/)$/)\;/' |mysql -u username -p database_name or mdb-export -I manufacturer.mdb WMI | sed -e 's/)$/)\;/' > WMI.mysql or mdb-export manufacturer.mdb WMI >WMI.csv
Vehicle Description Section (VDS) and Check Digit
[http://www.nhtsa.dot.gov/cars/rules/manufacture/ NHTSA Manufacture's Database] includes these databases:
[http://www.nhtsa.dot.gov/cars/rules/regrev/evaluate/808062.html Plasti-glass windows]
- New Tire and re-treaded tires.
[http://www.nhtsa.dot.gov/cars/rules/import/FAQ%20Site/ 565] -NHTSA information the agency will need to decipher the manufacturer’s vehicle identification number or “VIN” format
[http://www.nhtsa.dot.gov/cars/rules/import/FAQ%20Site/ 566] -letter to NHTSA identifying the manufacturer’s name, address, and the products it manufactures that are subject to the Federal motor vehicle safety standards
The vehicle description section consists of five characters (the 4th to 8th characters) which identify everything from the body style, engine type, and braking system to model, series, restraint system, etc. The 9th character is a VIN accuracy check digit which verifies the previous VIN numbers. It is determined by carrying out a mathematical computation developed by the Department of Transportation (DOT). Vehicle Identification Section (VIS)
The vehicle identification section includes the last eight characters in the VIN number. The numbers identify the model year (10th character) and the assembly plant for the vehicle (11th character). A number or letter may represent the model year. For example: 1998 (W), 2000 (Y), 2007 (7) or 2008 (8).
The 12th to 17th characters are the actual serial number of the vehicle—these last 6 digits make the vehicle unique (think of DNA). It can also help to identify whether the vehicle was the first, the hundredth, or the last vehicle off the manufacturer’s assembly line. This is valuable information for collectors.
So, the next time you see that long number printed on your registration papers or stamped across your dashboard, you’ll know how to decode it. This will give you a better understanding of where your unique vehicle actually came from!
move data to mysql
import MySQLdb >>> db=MySQLdb.connect(host="localhost",user="manufacture",passwd="manufacture",db="manufacture") >>> import csvtosql