Importing New American Fact Finder Data

The CSV files have the data.  If there are two CSV files, you do not want the one with annotations which will have "ANN" in the file name.

Open the file in the spreadsheet.  The field labels are not very well laid out.  You should keep the web page open, where they will be clearer.

The MOE columns are "margin of error", whose statistical use is beyond what is covered here.  Unless you know what you are doing, those columns can be deleted.

The EST columns are estimates, because this data is based on limited sampling.		  
If you selected the option to include descriptive element names, the second row will have a very long title for the column.  You will have to widen the column to see it, but it can help to devise a good short name for the field. You will have to remember to delete this row before you import the CSV file into MICRODEM.
If you did not select the option to include descriptive element names, use can use the second CSV in the file, which has "Metadata" in the file name.  This has the field names in the export, and the same long, descriptive titles in the second column.
View of the data on Fact finder.  Note that this is tract 7011,01 and 02, which is on rows 2  and 3 in the spreadsheet.  This is also transposed from the spreadsheet.  

Edit field names in row 1, using the Rules for field names  which are very picky. 


We will first:

  • Delete column A
  • Remove the commas in Column C (search and replace "," with a space or nothing).
  • Since this data set is entirely with a single county, we could also remove the County Name with a search and replace

Then add field names.  The fields go across (the first ones are the total population, then the under 5 then the 5 to 9, and so on.

You can delete columns that you do not want.  The margin of error columns, if present, might be removed if you do not want to use them in your analysis.

GEOID with leading Zero

  


Save in spreadsheet format in case there are problems and your CSV files becomes corrupted.

Save as a CSV file.

Close the spreadsheet, since the GIS program cannot access the file at the same time.		  
Open database without map to convert the CSV file to a database.  You can open on a map, but because there are no coordinates, the file will not plot on a map.

This cannot be plotted on the map, because it has no geometry attached.

A field has been added in the database, with the ratio of male to female earnings.		  
If there are problems with the import, you can look at the CSV file in Notepad or WordPad, and it should look like this. 

You might be able to make some simple fixes here.  The global search and replace can be much faster in WordPad than in NotePad.		  
The male to female population ratio for the county, displayed with Join/Link databases and then Plot,  Color code by DB field.  The values range from 77 to 479, and the color highlight the extreme high values which have many more men than women.  You should be curious about what kind of a tract can have almost 5 times as many men as women.

The map on the right shows where women outnumber men, by setting all values over 100 of the gender ratio to the maximum color, and seeing the variation in the parts of the country that are all blue in the map on the left.		  

 

Fixing ID field in Scientific Notation
Note that this file has the GEOID field as a floating point number with an exponent (and that all the values are the same).  This will not work.  Before you save, you must reformat the column so that it is a number with 0 decimal places.
After correcting the IDs.

CSV file import problems

 

Join/Link database examples

Last revision 9/13/2018