ACS Data Users Group

Hi all,

I will be importing the 5year estimate summary files for Oregon, Arizona, and Washington D.C. into an Access database (2007 version). I've set up a macro to run the import routine so it wouldn't be too difficult to do additional states. The files are large so, as you can imagine, it actually takes multiple Access files to import them all. Ideally these should be used on a SQL server, though I currently don't have access to SQL so this is my method for now.

If anyone would like the Access files for any state, let me know and I can import it for you or send you documentation on how to do it for yourself. I will need to test my import routine when the data are released on Thursday so I don't want to give out the documentation files until I know this works properly. I did this for the 2012 5year estimates, so I know it works, but I've had to make adjustments due to changes in the Sequence Files so I want to make sure I got the adjustments correct before I distribute it.

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.

Jami, I am coding an automated process to load the text files into the Excel Summary File Templates and subsequently load the entire batch into Access. It's all nearly done except for a bit of clean-up for the Excel load sub (writing a switch for the "e" and "m" files and a better error-handler). So yes, it works now but only for the "e" or "m" set separately. I'll post when it's more bullet-proof. For the Access import, I slightly modified code I found on the web to load the entire batch of Excel files (the hard work is literally already done thanks to this page).
www.accessmvp.com/.../EXCEL_Import.htm

[Updated on 12/5/2014 6:26 PM]

Jeffrey, that is awesome! I think I tried excel to access before and I had trouble with the field types not being correct from excel - but then I'm not a pro at any of this, I just figure out ways to make it work as best I can. I didn't have this group last year so super glad to see some smart people in here sharing ways to get the data into databases.

With Access, I run into filesize limitations so are you getting around that by programming it to load into multiple access files as needed? I put the data in multiple files then have one master database that links the tables into it from the other files.

Thanks everyone for all your help! @Dominic - Is there any chance of getting copies of your SQL Server integration Services packages to import everything into a SQL Server database?

Jami,

I will post what I have with the understanding that you can only use this with either the "e" or "m" files separately. Here is the Excel import part. You will need to download and unzip the summary template files into a folder.
www2.census.gov/.../2013_Summary_FileTemplates.zip
Set the path to your folder in the DataIn Sub although you can do this at runtime too.
I'll get the Access section up next. I had not run into size limitations because I was using link rather than import although this may not be optimal. I will need to create modify the routine to import sets of the sequence of files into separate database (if I ever find time). I had not encountered the problem with wrong data type yet, but I know this can be a problem. I may need to add more code to the Excel part to check if the first row is blank and insert zeroes into blank data fields. That should force those fields into number data type.

To use the Excel code hit Alt+F11, insert a new module and paste in the code I have attached. I created a button for my toolbar to run the macro, but you can also run by hitting F5 or the play button within the VB editor.
Have fun.
Jeff
P.S. I am not a programmer, only a dabbler, so keep that in mind. ;-)

Here is revised Excel code that puts zeroes in the first row if they are blank. Also, here is the code to link the Excel templates to Access. You can change to import by changing acLink to acImport in the code. You will probably need to create several databases because of the size issue. Each database would then load files from a separate folder containing a portion of the total files to load (say Seq1 through Seq30, etc). Please leave the last letter of the folder either "e" or "m" though, because this used in the code to determine which tab to load. I will try to improve all of this when time permits, but I would welcome any help or advice from VBA gurus.
Jeff

looks good, Jeff, thanks.

I've already got the state files I need imported into Access so I'm using those for now. I also imported the US file. As of now, I have 11 states (include D.C.) plus the U.S. in Access 2007 databases.

so if these are helpful to anyone, feel free to grab the files you need. (or let me know if you need a state that is not on there yet).
Here's the link again to the Access databases: some of them are huge and require multiple zip files.
gisdiva.com/.../

Jeff, I'm going to take a look at your code when I get a chance and see if it will be more efficient for me when I need to get more state data into Access. Thanks again for sharing ! I'm not a programmer either, just know enough of how to work my way around code to get something to work-- usually. :)

thanks!
Jami

Hi all,
For some reason my profile settings didn't keep me notified of new activity on this thread. Sorry about questions to me that went unanswered.
In any case, it took a little bit longer due to the holidays but I finished creating the SSIS project to import into SQL Server. We use this internally to import all 122 of the template tables in the ACS data - (actually, 244; estimates and margins of error), for all geographies. It's laid out in a way where one could just load the particular template table/geography as desired.

If this is something you're interested in, write me and I'll send you a link. I'll do as much as I can to help out, but if you're not an SSIS user and don't have access to someone in your place of work, this probably doesn't work for you. Sorry.

-Dominic