ACS 2013 5-year data available in MS Access 2007

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