Instructions to Import GeoNetMap into MS Access

4

oris.younger DOT

Import Instructions Access 
Importing Complaints data into MS Access
Follow these instructions 1 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_Access.pdf

Last Updated: May 2021

Page 1 of 13

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: May 2021

Page 2 of 13

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: May 2021

Page 3 of 13

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

Last Updated: May 2021

Page 4 of 13

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: May 2021

Page 5 of 13

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: May 2021

Page 6 of 13

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: May 2021

Page 7 of 13

Importing Complaints data into MS Access
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.

Last Updated: May 2021

Page 8 of 13

Importing Complaints data into MS Access
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.

Last Updated: May 2021

Page 9 of 13

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: May 2021

Page 10 of 13

20. Select OK.

Importing Complaints data into MS Access

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

Last Updated: May 2021

Page 11 of 13

Importing Complaints data into MS Access

Appendix A. Complaints File Characteristics

Field# Name

------ ---------

1

CMPLID

2

ODINO

3

MFR_NAME

4

MAKETXT

5

MODELTXT

6

YEARTXT

7

CRASH

8

FAILDATE

9

FIRE

10

INJURED

11

DEATHS

12

COMPDESC

13

CITY

14

STATE

15

VIN

16

DATEA

17

LDATE

18

MILES

19

OCCURENCES

20

CDESCR

21

CMPL_TYPE

22

POLICE_RPT_YN

23

PURCH_DT

24

ORIG_OWNER_YN

25

ANTI_BRAKES_YN

Type/Size --------CHAR(9)
CHAR(9)
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)
CHAR(1) CHAR(8) CHAR(1) CHAR(1)

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 =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 WAS INCIDENT REPORTED TO POLICE 'Y' OR 'N' DATE PURCHASED (YYYYMMDD) WAS ORIGINAL OWNER 'Y' OR 'N' ANTI-LOCK BRAKES 'Y' OR 'N'

Last Updated: May 2021

Page 12 of 13

Importing Complaints data into MS Access

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'

Last Updated: May 2021

Page 13 of 13
oris.younger Adobe PDF Library 21.1.167