**PART ONE:**

# CREATING THE ACCESS DATABASE SHELL

From FactFinder Econ Mainpage:
<http://factfinder.census.gov/servlet/DatasetMainPageServlet?_program=ECN&_submenuId=datasets_4&_lang=en>

select [DOWNLOAD DATA VIA FTP]{.underline}

GO TO: [Reference Materials]{.underline} \> [auxil_full.zip]{.underline}

> 1\) **Open** **DD_CDAFFFILEFLD** **database file**
>
> 2\) **Open** **DD_CDAFFFILEFLD** **table**

A\) Under CSHRTNAME, RIGHT-CLICK \> Filter for: (filename)

*\*\*To find filename see "How do I find the file I want?" --page 6.*

B\) EDIT \> Select All Records \> COPY

> 3\) **Open a New Excel File**
>
> A\) EDIT \> PASTE SPECIAL

B\) PASTE AS TEXT (important so as to not loose leading zeros)

C\) DATA \> SORT (SORTCOLM -ASC)

D\) EDIT \> COPY

E\) SELECT A BLANK CELL IN FIRST ROW

F\) EDIT \> PASTE SPECIAL \> TRANSPOSE

G\) DELETE ORIGINAL COLUMNS COPIED IN

H\) DELETE ALL ROWS EXCEPT FLDNAME

I\) DELETE FLDNAME FIELD

*\*\*You should now have only the fields that make up the table's column
headers.*

J)  SAVE

\*\*\*IMPORTANT NOTE:

For data files of less than 65,536 rows, you have the option to import
the data (.csv or .txt file) into this Excel file instead of moving
everything into Access (disregard Step 3 and PART TWO).

Instead...

(Toolbar) DATA \> GET EXTERNAL DATA \> IMPORT TEXT FILE \>

DELIMITED \> COMMA \> TEXT FORMAT \> EXISTING WORKSHEET \> FINISH.

4\) **Open new Access Database**

> A\) FILE \> GET EXTERNAL DATA \> IMPORT (select file type Excel)

B\) FIND EXCEL FILE CREATED WITH COLUMN HEADERS

C\) CHECK \"1ST ROW CONTAINS COLUMN HEADINGS\"
![](media/image1.png){width="3.801388888888889in"
height="3.0770833333333334in"}

D\) CONTINUE WITH NEXT BUTTON,

> **\*\*IMPORTANT**: Do not have Access create a primary key. Select
> **"No primary key"**

E\) FINISH AFTER YOU NAME THE TABLE

**PART TWO:**

**BRINGING THE DATA INTO THE SHELL**

1)  **Change the data file type**

A\) Right click on data file \> RENAME

B\) Change .dat to .csv

> example: If data file is ec0200a1.dat
>
> then change to ec0200a1.csv

\*\***Note: Access cannot import .dat file types**

2)  **Import data into table**

A\) FILE \> GET EXTERNAL DATA \> IMPORT (select file type Text)

B\) FIND TEXT FILE CREATED WHEN FTP\'ed from AFF

C\) SELECT COMMA DELIMITED \> NEXT

![](media/image2.png){width="3.801388888888889in"
height="3.0770833333333334in"}

> D MAKE SURE THAT CHECK BOX IS **NOT CHECKED** FOR \"1ST ROW CONTAINS
> FIELD NAMES\"

![](media/image3.png){width="3.801388888888889in"
height="3.0770833333333334in"}

E\) STORE IN TABLE CREATED EARLIER WITH COLUMN HEADERS

![](media/image4.png){width="3.801388888888889in"
height="3.0770833333333334in"}

**PART THREE:**

**ADDITIONAL INFORMATION...**

**[Key to symbols: User can Find/Replace in Access
database:]{.underline}**

1\) From FactFinder Econ Mainpage: select [DOWNLOAD DATA VIA
FTP]{.underline}
(http://factfinder.census.gov/servlet/DatasetMainPageServlet?\_program=ECN&\_submenuId=datasets_4&\_lang=en)

GO TO: [Reference Materials]{.underline} \> [auxil_full.zip]{.underline}

2\) **Open** DD_SYMBOLS

**[Key to Finding the File you want to Download:]{.underline}**

*What are the main folders?*

![](media/image5.wmf){width="3.9319444444444445in"
height="4.013194444444444in"}

*How do I find the file I want?*

GO TO: [Reference Materials]{.underline} \> [auxil_full.zip]{.underline}
\> [DD_PUBS]{.underline}

-Pubnum column corresponds to the file name.

-Longname column corresponds to the table/file title.

\*\*Using RIGHT CLICK, Filter for: \[ \] you can search for the name of
the file you are looking for.

Example: I want to download the economic census Mining data for Alaska:

1\) In LONGNAME filter on "\*Alaska\*" (wildcard =\*)

-you can see that file name is EC0221AAK

![](media/image6.wmf){width="4.317361111111111in"
height="2.9659722222222222in"}

EC tells you that it's in the EC folder

21 tells you that it's in the sector 21 folder

A tells you that it's in the geographic area series data files

-ec0221a1.dat

-ec0221a2.dat

-ec0221a3.dat

*What are some other Reference Files and how can I use them?*

DD_SYMBOLS: detailed explanation of what symbols on data mean

DD_PUBS: full name of publications

DD_NAICS...: full description of NAICS codes

DD_GEOREF: full description of codes applying to geography (ST, COUNTY)

DD_CDAFFFILEFLD: what fields are in each file, what files contain a
particular field

DD_FIELD (longname): source of all column titles and other information
about them

DD_CDAFFFILE: name of each file

DD_PRODSERV: full descriptions of product codes

DD_VALIDVAL: source of other dimension of the files