Import_Instructionsx Import Instructions NHTSA

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 14

Importing Complaints data into MS Access
Last Updated: September 2015 Page 1 of 14
Follow these instructions1 to download the Office of Defects Investigation (ODI)
Complaints data and import them into Microsoft Access. ODI Complaints data
are available in the compressed data (ZIP) format on the ODI web site.
The system characteristics of the target machine will affect the speed and
performance of the processing needed to accomplish the downloading and
importing of the Complaints data. At least 2GB of available disk space is
required. This space requirement will increase as more data are continually
being added to the ODI database.
Detailed field descriptions of the data file are given in Appendix A.
Note: For consistency the original file name FLAT_CMPL is used throughout
these instructions.
1. Go to the following URL:
http://www-odi.nhtsa.dot.gov/downloads/index.cfm
2. Select the FLAT_CMPL.zip file.
3. Select Save.
1 The PDF version of this document is located at
http://www-odi.nhtsa.dot.gov/downloads/folders/Complaints/Import_Instructions.pdf
Importing Complaints data into MS Access
Last Updated: September 2015 Page 2 of 14
4. Select a destination folder for the file in the Save in box and then select
Save.
(This is a large file and may take a few minutes to download.)
5. Select Open.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 3 of 14
6. Extract the zipped file (FLAT_CMPL.txt) to the desired folder.
Note: The unzipped file of Complaints data is now ready to be imported into a
Microsoft Access database.
7. Open Microsoft Access by selecting Start > All Programs > Microsoft
Office > Microsoft Access.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 4 of 14
Note: The following screenshots are from the Microsoft Office 2000 version of
Access. Other versions will have similar views and functionality, but may not
look exactly the same. Use the toolbars or help feature if you have difficulty
locating a function.
8. Select Blank Access database, then select OK.
9. Select a destination folder for the database in the Save in box and
accept the default File name of ‘db1.mdb’, and then select Create.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 5 of 14
9a. (This is the window that you will see when Access opens. You will
NOT be using this window to create a table, but don’t close it.) Go to step
10.
10. Select File, then Get External Data, then Import from the Access menu.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 6 of 14
11. When the Import screen opens, select
Text Files (*.txt;*.csv;*.tab;*.asc) using the dropdown arrow for Files of
type at the bottom.
12. Browse to the folder location of the FLAT_CMPL.txt file using the
dropdown arrow for Look in.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 7 of 14
13. Select the FLAT_CMPL.txt file, and then select Import
14. Select Delimited, then select Next.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 8 of 14
15. Select Tab as the delimiter as our data are stored as tab delimited quoted
text, then select Next.
16. Select ‘In a New Table’, then select Next.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 9 of 14
17. 1 - Using the scroll arrow or slider, scroll right to Field20.
2 - Select Field20.
3 - Change Data Type to Memo using the dropdown arrow.
4 - Select Next.
1
2
3
4
1
Importing Complaints data into MS Access
Last Updated: September 2015 Page 10 of 14
18. Select ‘Let Access add primary key’, then select Next.
19. Select Finish (Import may take 5 minutes or longer, depending on system
characteristics.).
Importing Complaints data into MS Access
Last Updated: September 2015 Page 11 of 14
20. Select OK.
This concludes the import process. You can now view all the data in Access.
Importing Complaints data into MS Access
Last Updated: September 2015 Page 12 of 14
Appendix A. Complaints File Characteristics
Field# Name Type/Size Description
------ --------- --------- --------------------------------------
1 CMPLID CHAR(9) NHTSA'S INTERNAL UNIQUE SEQUENCE NUMBER.
IS AN UPDATEABLE FIELD,THUS DATA FOR A
GIVEN RECORD POTENTIALLY COULD CHANGE FROM
ONE DATA OUTPUT FILE TO THE NEXT.
2 ODINO CHAR(9) NHTSA'S INTERNAL REFERENCE NUMBER.
THIS NUMBER MAY BE REPEATED FOR
MULTIPLE COMPONENTS.
ALSO, IF LDATE IS PRIOR TO DEC 15, 2002,
THIS NUMBER MAY BE REPEATED FOR MULTIPLE
PRODUCTS OWNED BY THE SAME COMPLAINANT.
3 MFR_NAME CHAR(40) MANUFACTURER'S NAME
4 MAKETXT CHAR(25) VEHICLE/EQUIPMENT MAKE
5 MODELTXT CHAR(256) VEHICLE/EQUIPMENT MODEL
6 YEARTXT CHAR(4) MODEL YEAR, 9999 IF UNKNOWN or N/A
7 CRASH CHAR(1) WAS VEHICLE INVOLVED IN A CRASH, 'Y' OR 'N'
8 FAILDATE CHAR(8) DATE OF INCIDENT (YYYYMMDD)
9 FIRE CHAR(1) WAS VEHICLE INVOLVED IN A FIRE 'Y' OR 'N'
10 INJURED NUMBER(2) NUMBER OF PERSONS INJURED
11 DEATHS NUMBER(2) NUMBER OF FATALITIES
12 COMPDESC CHAR(128) SPECIFIC COMPONENT'S DESCRIPTION
13 CITY CHAR(30) CONSUMER'S CITY
14 STATE CHAR(2) CONSUMER'S STATE CODE
15 VIN CHAR(11) VEHICLE'S VIN#
16 DATEA CHAR(8) DATE ADDED TO FILE (YYYYMMDD)
17 LDATE CHAR(8) DATE COMPLAINT RECEIVED BY NHTSA (YYYYMMDD)
18 MILES NUMBER(7) VEHICLE MILEAGE AT FAILURE
19 OCCURENCES NUMBER(4) NUMBER OF OCCURRENCES
20 CDESCR CHAR(2048) DESCRIPTION OF THE COMPLAINT
21 CMPL_TYPE CHAR(4) SOURCE OF COMPLAINT CODE:
CAG =CONSUMER ACTION GROUP
CON =FORWARDED FROM A CONGRESSIONAL OFFICE
DP =DEFECT PETITION,RESULT OF A DEFECT
PETITION
EVOQ =HOTLINE VOQ
EWR =EARLY WARNING REPORTING
INS =INSURANCE COMPANY
IVOQ =NHTSA WEB SITE
LETR =CONSUMER LETTER
MAVQ =NHTSA MOBILE APP
MIVQ =NHTSA MOBILE APP
MVOQ =OPTICAL MARKED VOQ
RC =RECALL COMPLAINT,RESULT OF A RECALL
INVESTIGATION
RP =RECALL PETITION,RESULT OF A RECALL
PETITION
SVOQ =PORTABLE SAFETY COMPLAINT FORM (PDF)
VOQ =NHTSA VEHICLE OWNERS QUESTIONNAIRE
22 POLICE_RPT_YN CHAR(1) WAS INCIDENT REPORTED TO POLICE 'Y' OR 'N'
23 PURCH_DT CHAR(8) DATE PURCHASED (YYYYMMDD)
24 ORIG_OWNER_YN CHAR(1) WAS ORIGINAL OWNER 'Y' OR 'N'
25 ANTI_BRAKES_YN CHAR(1) ANTI-LOCK BRAKES 'Y' OR 'N'
Importing Complaints data into MS Access
Last Updated: September 2015 Page 13 of 14
26 CRUISE_CONT_YN CHAR(1) CRUISE CONTROL 'Y' OR 'N'
27 NUM_CYLS NUMBER(2) NUMBER OF CYLINDERS
28 DRIVE_TRAIN CHAR(4) DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]
29 FUEL_SYS CHAR(4) FUEL SYSTEM CODE:
FI =FUEL INJECTION
TB =TURBO
30 FUEL_TYPE CHAR(4) FUEL TYPE CODE:
BF =BIFUEL
CN =CNG/LPG
DS =DIESEL
GS =GAS
HE =HYBRID ELECTRIC
31 TRANS_TYPE CHAR(4) VEHICLE TRANSMISSION TYPE [AUTO, MAN]
32 VEH_SPEED NUMBER(3) VEHICLE SPEED
33 DOT CHAR(20) DEPARTMENT OF TRANSPORTATION TIRE IDENTIFIER
34 TIRE_SIZE CHAR(30) TIRE SIZE
35 LOC_OF_TIRE CHAR(4) LOCATION OF TIRE CODE:
FSW =DRIVER SIDE FRONT
DSR =DRIVER SIDE REAR
FTR =PASSENGER SIDE FRONT
PSR =PASSENGER SIDE REAR
SPR =SPARE
36 TIRE_FAIL_TYPE CHAR(4) TYPE OF TIRE FAILURE CODE:
BST =BLISTER
BLW =BLOWOUT
TTL =CRACK
OFR =OUT OF ROUND
TSW =PUNCTURE
TTR =ROAD HAZARD
TSP =TREAD SEPARATION
37 ORIG_EQUIP_YN CHAR(1) WAS PART ORIGINAL EQUIPMENT 'Y' OR 'N'
38 MANUF_DT CHAR(8) DATE OF MANUFACTURE (YYYYMMDD)
39 SEAT_TYPE CHAR(4) TYPE OF CHILD SEAT CODE:
B =BOOSTER
C =CONVERTIBLE
I =INFANT
IN =INTEGRATED
TD =TODDLER
40 RESTRAINT_TYPE CHAR(4) INSTALLATION SYSTEM CODE;
A =VEHICLE SAFETY BELT
B =LATCH SYSTEM
41 DEALER_NAME CHAR(40) DEALER'S NAME
42 DEALER_TEL CHAR(20) DEALER'S TELEPHONE NUMBER
43 DEALER_CITY CHAR(30) DEALER'S CITY
44 DEALER_STATE CHAR(2) DEALER'S STATE CODE
45 DEALER_ZIP CHAR(10) DEALER'S ZIPCODE
46 PROD_TYPE CHAR(4) PRODUCT TYPE CODE:
V =VEHICLE
T =TIRES
E =EQUIPMENT
C =CHILD RESTRAINT
47 REPAIRED_YN CHAR(1) WAS DEFECTIVE TIRE REPAIRED 'Y' OR 'N'
48 MEDICAL_ATTN CHAR(1) WAS MEDICAL ATTENTION REQUIRED 'Y' OR 'N'
49 VEHICLES_TOWED_YN CHAR(1) WAS VEHICLE TOWED 'Y' OR 'N'
Importing Complaints data into MS Access
14 9/3/2015

Navigation menu