ACS 2013 5-year data available in MS Access 2007

I would be happy to see the documentation on how to import the files, i am more familiar with excel macro (VBA) than MS Access, i know how to import txt, log or other files into excel using macro, but i have never done it using MS Access.

Hi Girma - what state(s) do you need? I am going ahead and creating the access files for every state and will put them on my server for download. if you let me know what state you need then I will be sure to get to that one first. I'm currently writing up documentation on how to use the files. Because it's such a large amount of data, and Access has a filesize limitation, the data will take at least 4 Access files per state.

I work here in Maryland, so, file(s) on MD would be nice.

thank you so much!

Hi All,

I've started creating the state files in MS Access 2007. I have some documentation but will be creating more detailed documentation on how to use the files over the next week. For now there is some basic documentation to get started. If you understand Summary Levels, how to find the sequence file for the table you want, and how to query in Access you should be fine with what I've got so far.

As of now the following states are available:
Arizona, Maryland, Oregon, Virginia, and District of Columbia (Wash D.C.)

I'll be adding more each day until I get them all done.

Get the files here:
gisdiva.com/.../

If you use these files and find any errors, please let me know so I can fix them. thanks!

[Updated on 12/5/2014 9:30 AM]

this is really great! thanks a lot!

JamiRae do you happen to know anyone who has scripts and/or documentation for importing ACS data to SQL? Also, did you know the Census Bureau has provided geodatabases of all of the detailed tables by state, but it usually takes them a few months to make them available.

Hi Gwen - I know of someone who has imported select tables into SQL I'll see if he has a script he can share that can be adapted.

And yes, I'm aware of the geodatabases, though for the 2012 5year data I dont think they were available until sometime in the spring and I've not seen a release schedule for the 2013 5-year data in gdb format. Have you? or anyone? That would certainly save me time! :)

I will be attempting to import the data also into a MySQL database, so if that is successful I can share my methodology if you or anyone else is interested.

Hi everyone,
I'm a database architect with PolicyMap.com - my colleague Elizabeth is a member on this board and pointed me to this discussion.

We load all of the ACS 5 year data into SQL Server every year. We haven't yet downloaded it all because I understand there were some issues with the table shells Excel file (I'm not surprised; I found some myself 2 years ago and reported it to Census). But once we download it all, I then generate SQL Server integration Services packages to import everything into a SQL Server database. This process takes a couple weeks - mostly because I need to determine correct data types for each column (I'm not aware of any documentation on this. Is there any?). We obviously keep all the import packages.

If this sounds like something that can help you, please let me know and I'll see what we can do to help out.

For anyone it helps:

our Census Reporter project (http://censusreporter.org) has scripts for loading data into Postgres. The scripts are slightly database specific, although they could be adapted.

The scripts are at github.com/.../census-postgres but also, we provide dump files that are easily imported into a Postgres database: censusreporter.tumblr.com/.../easier-access-to-acs-data

We haven't gotten ACS 2009-2013 (5-year) yet, but the page above will be updated when we have, and we'll tweet about it from @CensusReporter

We also load all ACS data into database tables (MySQL) using utility scripts, mostly written in Python. I'm glad to share them with anyone who asks nicely. :)

Glenn-- yes, please! Could you please share the MySQL/python scripts with me? what do you need? email address?

thank you in advance!

@Dominic - I created SPEC files in Access to import all the tables (specifications). I should be able to export them if you think it is helpful, though I also have excel files with them it - but that may be cumbersome to deal with, I'm not sure. attached are the Excel files if anyone thinks they can use them. I created them in Excel then pasted them into the mysysIMEXColumns (hidden system files) in Access. In case you're unfamiliar with these, the SPECID field links to the MysysIMEXSpecs file to create the spec tables. These are the specs that you can pull up in the "advanced" section when importing text files It may appear to be a convoluted process, but it worked for me.


[Updated on 12/5/2014 3:36 PM]

Here are the 5 main scripts I use to import flat Bureau data files, combine the seq files and populate a MySQL database. Some caveats:

1. These were written for our processing environment and include some references to custom Python libraries that you won't have. Chief among these are a database helper library. You would need to replace any references to this with your own DB library, or add code directly to the scripts for DB authentication, open, and query.

2. MySQL has limitations on the number of columns you can have in any table, as well as a row size limit of 64k. I found the practical limit to be around 1000 columns. Therefore, you can't have a single giant MySQL table with all of the ACS variables. My scripts break up the ACS data into multiple MySQL tables (e.g. T_00_05 has columns for all variables in ACS tables B00001 through B05999).

3. import_basetables.py uses Parallel Python (PP) for parallel processing of state data. The more CPUs you can use, the faster. It also requires running a webserver (start_webserver.py) on localhost to serve up the state files as needed by the parallel workers.

merge_state_seqs.py is a helper script to combine the paired sequence files used in the 5-year data releases. That is, it will combine the "all geographies except tracts/BGs" with the "tracts and block groups only" sequence files for each state, by seq number and type (estimates/MOEs).

I don't worry about data types. All data columns start out as floats, and I go back later and use PROCEDURE ANALYSE to retroactively determine the best data type for each column. (That's what optimize_basetables.py does.)

And, I should point out that I'm mostly sharing these scripts as guidance. It would probably take a lot of work to get these running in your particular environment. But, they might give you some ideas about how to write or improve your own ACS data processing scripts.

Thanks Glenn! I figured it would mostly be guidance to adapt to my particular environment.