Vote up to Idea General design and specific API questions Vote down to Idea General design and specific API questions

Rank149

Idea#240

This idea is active.
API Technical Discussions »

General design and specific API questions

Hello everyone,

This is a question requiring some general direction and also some technical help:

I would like to automatically (daily, weekly?) upload ACS reports by county (all 3200+) into a database hosted locally. My boss wants it in MS Access (this is not up for debate, I have tried).

From MS Access, we'd like to be able to extract the fields we need to populate a .xls file or any other format. Eventually, even build a website.

I started this project using basic API tools to build tables but these tables are always hosted on a web-page. Is there a way to have the tables -all- exported into the MS Access database directly? Then we can pull down from our own database for whatever needs we may have.

I'd love to try this first on a simple output such as population estimates for one year, all counties.

Perhaps I don't even need the API key to do this, but we do want this to be automated--i.e. no more going to census website to individually download every report multiple times a week.

Thank you,

L.E.V.L.

Comment

Submitted by lvanleir 2 months ago

Comments (6)

  1. I'm not clear what you want the Census Bureau to do here. You can identify the characteristics you need (you don't need to get an table or report in its entirety), issue the relvant api calls, and, using a variety of programming or shell scripting languages, create a file that can be loaded into MS-ACCESS. Are you asking the Census Bureau to do something in addition to this?

    2 months ago
  2. lvanleir Idea Submitter

    I don't want the CB to do anything... I was looking for ideas from others on how EXACTLY to accomplish this automatic search & populate. Your solution, while probably correct, is extremely vague.

    I have already been successful in getting certain statistics by county using my API access key. Now, to start:

    I'm not sure what format the output is? It doesn't look like javascript or XML?

    Also, which shell scripting language is best to create a file that is upload-able to MS Access. Even so, how would I make this collected data upload automatically?

    One reason I thought to just take the whole report/table say for population from 1990 to now (if I could find it, I can't seem to find this?) and put this in MS Access was to avoid writing any scripts since this isn't my strong suit.

    Anyway, maybe this clarifies things a bit?

    2 months ago
  3. Sorry. I misunderstood your first comment. The format of the output is in JSON. I don't use MS Access myself, but you may be able to find some tips (and maybe even code) with a google search on JSON and MS Access. I think any of the popular scripting languages could create a .csv file from the JSON which I assume is easy to import into Access. I use R, and it has a library of routines for dealing with JSON. Hope this helps.

    2 months ago
  4. Good Day - A few things:

    If all you want is US Census Bureau data locally on your system, I would recommend a very different approach.

    1) The API is really best suited for creating web applications that query discrete elements of the data, not bulk downloads.

    2) It is not clear why you'd want to scrape the site so frequently (daily/weekly) as the data are not updated that frequently. Decennial data are once every 10 yearss while ACS data are released yearly.

    3) If all you want are a bunch of data to push into MS-Access grab the data using DataFerrett (http://dataferrett.census.gov/) or static files here: http://www2.census.gov/)

    4) The Census Bureau already has shells for Decennial data (see help http://www2.census.gov/census_2010/04-Summary_File_1/0HowToUseMSAccessWithSummaryFile1.pdf)

    5) ACS shells in MS Excel are here: http://www.census.gov/acs/www/data_documentation/data_via_ftp/

    ACS has MS Access shells somewhere but I cannot remember where.

    2 months ago
    1. lvanleir Idea Submitter

      Thank you, I think this is what I'm looking for. I had a feeling that 1) regular downloads are not necessary 2) API was not the way to go about this.

      Thank you for your helpful suggestions!

      2 months ago
  5. joe

    I don't have much experience with treating MS Access as a true SQL database, but you may get some mileage out of SQL scripts we've created on some projects I've worked on for bulk import of Census data.

    ACS: https://github.com/censusreporter/census-postgres-scripts

    Decennial Census: https://github.com/censusreporter/census/tree/master/tools

    good luck.

    2 months ago

Events

  1. The idea was posted
    2 months ago