Import_Instructionsx Import Instructions NHTSA

User Manual:

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

Download
Open PDF In Browser	View PDF

Importing Complaints data into MS Access

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

Last Updated: September 2015

Page 1 of 14

Importing Complaints data into MS Access

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.

Last Updated: September 2015

Page 2 of 14

Importing Complaints data into MS Access

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.

Last Updated: September 2015

Page 3 of 14

Importing Complaints data into MS Access

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.

9.

Select Blank Access database, then select OK.

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.

Last Updated: September 2015

Page 4 of 14

Importing Complaints data into MS Access

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.

Last Updated: September 2015

Page 5 of 14

Importing Complaints data into MS Access

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.

Last Updated: September 2015

Page 6 of 14

Importing Complaints data into MS Access

13.

Select the FLAT_CMPL.txt file, and then select Import

14.

Select Delimited, then select Next.

Last Updated: September 2015

Page 7 of 14

Importing Complaints data into MS Access

15.

16.

Select Tab as the delimiter as our data are stored as tab delimited quoted
text, then select Next.

Select ‘In a New Table’, then select Next.

Last Updated: September 2015

Page 8 of 14

Importing Complaints data into MS Access

17.

1
2
3
4

-

Using the scroll arrow or slider, scroll right to Field20.
Select Field20.
Change Data Type to Memo using the dropdown arrow.
Select Next.

3

2

1

4
1

Last Updated: September 2015

Page 9 of 14

Importing Complaints data into MS Access

18.

Select ‘Let Access add primary key’, then select Next.

19. Select Finish (Import may take 5 minutes or longer, depending on system
characteristics.).

Last Updated: September 2015

Page 10 of 14

Importing Complaints data into MS Access

20.

Select OK.

This concludes the import process. You can now view all the data in Access.

Last Updated: September 2015

Page 11 of 14

Importing Complaints data into MS Access

Appendix A.

Complaints File Characteristics

Field#
-----1

Name
--------CMPLID

Type/Size
--------CHAR(9)

2

ODINO

CHAR(9)

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

MFR_NAME
MAKETXT
MODELTXT
YEARTXT
CRASH
FAILDATE
FIRE
INJURED
DEATHS
COMPDESC
CITY
STATE
VIN
DATEA
LDATE
MILES
OCCURENCES
CDESCR
CMPL_TYPE

CHAR(40)
CHAR(25)
CHAR(256)
CHAR(4)
CHAR(1)
CHAR(8)
CHAR(1)
NUMBER(2)
NUMBER(2)
CHAR(128)
CHAR(30)
CHAR(2)
CHAR(11)
CHAR(8)
CHAR(8)
NUMBER(7)
NUMBER(4)
CHAR(2048)
CHAR(4)

Description
-------------------------------------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.
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.
MANUFACTURER'S NAME
VEHICLE/EQUIPMENT MAKE
VEHICLE/EQUIPMENT MODEL
MODEL YEAR, 9999 IF UNKNOWN or N/A
WAS VEHICLE INVOLVED IN A CRASH, 'Y' OR 'N'
DATE OF INCIDENT (YYYYMMDD)
WAS VEHICLE INVOLVED IN A FIRE 'Y' OR 'N'
NUMBER OF PERSONS INJURED
NUMBER OF FATALITIES
SPECIFIC COMPONENT'S DESCRIPTION
CONSUMER'S CITY
CONSUMER'S STATE CODE
VEHICLE'S VIN#
DATE ADDED TO FILE (YYYYMMDD)
DATE COMPLAINT RECEIVED BY NHTSA (YYYYMMDD)
VEHICLE MILEAGE AT FAILURE
NUMBER OF OCCURRENCES
DESCRIPTION OF THE COMPLAINT
SOURCE OF COMPLAINT CODE:
CAG =CONSUMER ACTION GROUP
CON =FORWARDED FROM A CONGRESSIONAL OFFICE
DP
=DEFECT PETITION,RESULT OF A DEFECT

PETITION
EVOQ
EWR
INS
IVOQ
LETR
MAVQ
MIVQ
MVOQ
RC

=HOTLINE VOQ
=EARLY WARNING REPORTING
=INSURANCE COMPANY
=NHTSA WEB SITE
=CONSUMER LETTER
=NHTSA MOBILE APP
=NHTSA MOBILE APP
=OPTICAL MARKED VOQ
=RECALL COMPLAINT,RESULT OF A RECALL

RP

=RECALL PETITION,RESULT OF A RECALL

INVESTIGATION
PETITION

22
23
24
25

POLICE_RPT_YN
PURCH_DT
ORIG_OWNER_YN
ANTI_BRAKES_YN

Last Updated: September 2015

CHAR(1)
CHAR(8)
CHAR(1)
CHAR(1)

SVOQ =PORTABLE SAFETY COMPLAINT FORM (PDF)
VOQ =NHTSA VEHICLE OWNERS QUESTIONNAIRE
WAS INCIDENT REPORTED TO POLICE 'Y' OR 'N'
DATE PURCHASED (YYYYMMDD)
WAS ORIGINAL OWNER 'Y' OR 'N'
ANTI-LOCK BRAKES 'Y' OR 'N'

Page 12 of 14

Importing Complaints data into MS Access

26
27
28
29

CRUISE_CONT_YN
NUM_CYLS
DRIVE_TRAIN
FUEL_SYS

CHAR(1)
NUMBER(2)
CHAR(4)
CHAR(4)

30

FUEL_TYPE

CHAR(4)

31
32
33
34
35

TRANS_TYPE
VEH_SPEED
DOT
TIRE_SIZE
LOC_OF_TIRE

CHAR(4)
NUMBER(3)
CHAR(20)
CHAR(30)
CHAR(4)

36

TIRE_FAIL_TYPE

CHAR(4)

37
38
39

ORIG_EQUIP_YN
MANUF_DT
SEAT_TYPE

CHAR(1)
CHAR(8)
CHAR(4)

40

RESTRAINT_TYPE

CHAR(4)

41
42
43
44
45
46

DEALER_NAME
DEALER_TEL
DEALER_CITY
DEALER_STATE
DEALER_ZIP
PROD_TYPE

CHAR(40)
CHAR(20)
CHAR(30)
CHAR(2)
CHAR(10)
CHAR(4)

47
48
49

REPAIRED_YN
MEDICAL_ATTN
VEHICLES_TOWED_YN

CHAR(1)
CHAR(1)
CHAR(1)

Last Updated: September 2015

CRUISE CONTROL 'Y' OR 'N'
NUMBER OF CYLINDERS
DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]
FUEL SYSTEM CODE:
FI =FUEL INJECTION
TB =TURBO
FUEL TYPE CODE:
BF =BIFUEL
CN =CNG/LPG
DS =DIESEL
GS =GAS
HE =HYBRID ELECTRIC
VEHICLE TRANSMISSION TYPE [AUTO, MAN]
VEHICLE SPEED
DEPARTMENT OF TRANSPORTATION TIRE IDENTIFIER
TIRE SIZE
LOCATION OF TIRE CODE:
FSW =DRIVER SIDE FRONT
DSR =DRIVER SIDE REAR
FTR =PASSENGER SIDE FRONT
PSR =PASSENGER SIDE REAR
SPR =SPARE
TYPE OF TIRE FAILURE CODE:
BST =BLISTER
BLW =BLOWOUT
TTL =CRACK
OFR =OUT OF ROUND
TSW =PUNCTURE
TTR =ROAD HAZARD
TSP =TREAD SEPARATION
WAS PART ORIGINAL EQUIPMENT 'Y' OR 'N'
DATE OF MANUFACTURE (YYYYMMDD)
TYPE OF CHILD SEAT CODE:
B =BOOSTER
C =CONVERTIBLE
I =INFANT
IN =INTEGRATED
TD =TODDLER
INSTALLATION SYSTEM CODE;
A =VEHICLE SAFETY BELT
B =LATCH SYSTEM
DEALER'S NAME
DEALER'S TELEPHONE NUMBER
DEALER'S CITY
DEALER'S STATE CODE
DEALER'S ZIPCODE
PRODUCT TYPE CODE:
V =VEHICLE
T =TIRES
E =EQUIPMENT
C =CHILD RESTRAINT
WAS DEFECTIVE TIRE REPAIRED 'Y' OR 'N'
WAS MEDICAL ATTENTION REQUIRED 'Y' OR 'N'
WAS VEHICLE TOWED 'Y' OR 'N'

Page 13 of 14

Importing Complaints data into MS Access

14

9/3/2015



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : Yes
Author                          : Greg.Clark.CTR
Create Date                     : 2015:09:03 15:21:41-04:00
Modify Date                     : 2015:09:03 15:21:41-04:00
XMP Toolkit                     : Adobe XMP Core 5.2-c001 63.139439, 2010/09/27-13:37:26
Format                          : application/pdf
Title                           : Microsoft Word - Import_Instructions.docx
Creator                         : Greg.Clark.CTR
Creator Tool                    : PScript5.dll Version 5.2.2
Producer                        : Acrobat Distiller 10.1.15 (Windows)
Document ID                     : uuid:56172467-d744-4e4b-8303-90af3e17a10f
Instance ID                     : uuid:808ac5ea-7885-4242-94ff-9d20320d1703
Page Count                      : 14

EXIF Metadata provided by EXIF.tools