ACS 2013 5-year data available in MS Access 2007

JamiRae over 9 years ago

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.

Parents

Jeffrey Naas over 9 years ago

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. ;-)
Cancel
Up 0 Down

Reply

Cancel

Reply

Jeffrey Naas over 9 years ago

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. ;-)
Cancel
Up 0 Down

Reply

Cancel

Children

No Data