I agree with Nathan on the technical side of things. For this type of
use and data applications, while directly connecting to the database can
or cannot be done with the "Get External Data" functions in Excel or
Access, via ODBC or similar database connection technology in a data
analysis package such as Stata or R, it might be preferable to use a
database management tool such as Microsoft SQL Server Management Studio.
Indeed it is preferable and better to directly connect to the database
through either a database management tool such as SQL Server Management
Studio (Express), as compared to a data analysis package such as Excel,
Access, Stata or R. If connecting through a data analysis package, the
database is usually accessed by either setting up an ODBC Data Source
(under Administrative tools under Control Panel) or on-the-fly via a
connection string. For example, the ODBC connection string for the ARC
database on our ARCTESTDATABASE server is:
Driver={SQL Server}; Server= ARCTESTDATABASE; Database=ARC; Integrated
Security=SSPI
If using a .Net client to connect to the database, the connection string
is slightly different:
Data Source= ARCTESTDATABASE; Initial Catalog=ARC; Integrated
Security=SSPI
Once connected to the database, you can execute SQL statements against
the database to perform custom analysis.
Here are some Database Back-end references we use here for this type of
work:
* Microsoft SQL Server, http://www.microsoft.com/sqlserver
* SQL Server Management Studio Express,
http://msdn.microsoft.com/en-us/library/ms365247.aspx free as Nathan
stated
* T-SQL,
http://msdn.microsoft.com/en-us/library/ms189826(v=sql.90).aspx
* WebORB, http://www.themidnightcoders.com
Thanks,
Guy
Guy Rousseau
Surveys & Transportation Model Development Manager
Atlanta Regional Commission
40 Courtland Street, NE
Atlanta, GA 30303
404-463-3274 (work)
678-986-4344 (cell)
grousseau(a)atlantaregional.comwww.atlantaregional.com <http://www.atlantaregional.com>
CONFIDENTIALITY NOTICE: This email message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information. Any unauthorized review, use
or disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender immediately and destroy all copies
of the original message.
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Erlbaum, Nathan
(DOT)
Sent: Wednesday, March 09, 2011 12:29 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
>From a database size issue on solution might be for you to try SQL
server express
(
http://www.google.com/url?sa=t&source=web&cd=1&ved=0CBwQFjAA&url=http%3A
%2F%2Fwww.microsoft.com%2Fexpress%2FDatabase%2F&ei=KLd3Tc36HoKE0QHxtf3fB
g&usg=AFQjCNHjeaYABXz3Vpzmoqyz_kqbTWCfuA )
it may be a chore, but it is free and you can spend endless hours loving
Microsoft. It will let you get the files in, since the size limit is MS
Access is not the issue and then you can access them by MS access. It
may be a steep learning curve for you but it is another option and it
works because its free. Your other option is SAS or SPSS or maybe R or
simply wait until someone else does it for you.
In any event what we have here a situation where the total lack of
serious though leadership on the part of the Census Bureau would have
resolved hundreds of hours of efforts on the part of many to do what the
CB could have provided for in advance.
But then what can we expect, they're from the government and they're
here to help you, I believe that is how the expression goes.......
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Wednesday, March 09, 2011 12:06 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
Thank you Robert.
It turns out the files are massive and that appears to be the problem.
No matter which order I import in, Access can't import the third file.
I've adopted your method and I've created two Access databases, one for
the first PL data table and another for the second table. I was able to
import the geo table into both and can query the data for our region's
counties for use in GIS.
Thank you for saving me from a lot more frustration.
Anais Schenk | Planner | AMBAG
Direct 831.264.5088 | Fax 831.883.3755
________________________________
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Allen, Robert.
Sent: Wednesday, March 09, 2011 7:00 AM
To: 'ctpp-news(a)chrispy.net'
Subject: [CTPP] RE: Problem Importing CA PL data
What size files are getting in Access when you try to import the files?
In Texas, the files were too big to import all three at once into
Access. Access has a code limited total data file size of 2 GB. I
could import two of the files in any sequence and Access would always
choke on the third, saying that the file was unrecognized or corrupt,
but the actual problem was that the 2 GB limitation was being exceeded.
Using a 64-bit version did not help as the 2 GB is a Microsoft Access
program code limit, not a computing power limit. I finally had to hook
the geoheaders up to the two data files in two steps, then cut down the
resulting databases into substate areas that could be handled within the
coding limitations of Access and Excel before hooking the data up to the
census geography files in our GIS system.
Robert R. Allen, AICP
Abilene MPO
400 Oak St. #102, Abilene, TX 79602
Phone 325-676-6243
Fax 325-676-6398
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access
2003? I keep getting an error message saying that access doesn't
recognize or find the database. I followed the instructions in the
"0File_Structure" pdf exactly. I got the other two tables imported just
fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
Thanks for the tip. I'll check it out next time I have an issue. For the moment my problem has been resolved by creating the two separate databases. Free is good. We are a small agency and can not justify the expense of SAS software at the moment.
Anais Schenk | Planner | AMBAG
Direct 831.264.5088 | Fax 831.883.3755
<http://www.commutealternatives.info>
________________________________
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Erlbaum, Nathan (DOT)
Sent: Wednesday, March 09, 2011 9:29 AM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
>From a database size issue on solution might be for you to try SQL server express
( http://www.google.com/url?sa=t&source=web&cd=1&ved=0CBwQFjAA&url=http%3A%2F… )
it may be a chore, but it is free and you can spend endless hours loving Microsoft. It will let you get the files in, since the size limit is MS Access is not the issue and then you can access them by MS access. It may be a steep learning curve for you but it is another option and it works because its free. Your other option is SAS or SPSS or maybe R or simply wait until someone else does it for you.
In any event what we have here a situation where the total lack of serious though leadership on the part of the Census Bureau would have resolved hundreds of hours of efforts on the part of many to do what the CB could have provided for in advance.
But then what can we expect, they're from the government and they're here to help you, I believe that is how the expression goes.......
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Wednesday, March 09, 2011 12:06 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
Thank you Robert.
It turns out the files are massive and that appears to be the problem. No matter which order I import in, Access can't import the third file. I've adopted your method and I've created two Access databases, one for the first PL data table and another for the second table. I was able to import the geo table into both and can query the data for our region's counties for use in GIS.
Thank you for saving me from a lot more frustration.
Anais Schenk | Planner | AMBAG
Direct 831.264.5088 | Fax 831.883.3755
________________________________
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Allen, Robert.
Sent: Wednesday, March 09, 2011 7:00 AM
To: 'ctpp-news(a)chrispy.net'
Subject: [CTPP] RE: Problem Importing CA PL data
What size files are getting in Access when you try to import the files? In Texas, the files were too big to import all three at once into Access. Access has a code limited total data file size of 2 GB. I could import two of the files in any sequence and Access would always choke on the third, saying that the file was unrecognized or corrupt, but the actual problem was that the 2 GB limitation was being exceeded. Using a 64-bit version did not help as the 2 GB is a Microsoft Access program code limit, not a computing power limit. I finally had to hook the geoheaders up to the two data files in two steps, then cut down the resulting databases into substate areas that could be handled within the coding limitations of Access and Excel before hooking the data up to the census geography files in our GIS system.
Robert R. Allen, AICP
Abilene MPO
400 Oak St. #102, Abilene, TX 79602
Phone 325-676-6243
Fax 325-676-6398
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
[cid:image001.jpg(a)01CBDE41.59B33B70]
>From a database size issue on solution might be for you to try SQL server express
( http://www.google.com/url?sa=t&source=web&cd=1&ved=0CBwQFjAA&url=http%3A%2F… )
it may be a chore, but it is free and you can spend endless hours loving Microsoft. It will let you get the files in, since the size limit is MS Access is not the issue and then you can access them by MS access. It may be a steep learning curve for you but it is another option and it works because its free. Your other option is SAS or SPSS or maybe R or simply wait until someone else does it for you.
In any event what we have here a situation where the total lack of serious though leadership on the part of the Census Bureau would have resolved hundreds of hours of efforts on the part of many to do what the CB could have provided for in advance.
But then what can we expect, they're from the government and they're here to help you, I believe that is how the expression goes.......
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Wednesday, March 09, 2011 12:06 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
Thank you Robert.
It turns out the files are massive and that appears to be the problem. No matter which order I import in, Access can't import the third file. I've adopted your method and I've created two Access databases, one for the first PL data table and another for the second table. I was able to import the geo table into both and can query the data for our region's counties for use in GIS.
Thank you for saving me from a lot more frustration.
Anais Schenk | Planner | AMBAG
Direct 831.264.5088 | Fax 831.883.3755
________________________________
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Allen, Robert.
Sent: Wednesday, March 09, 2011 7:00 AM
To: 'ctpp-news(a)chrispy.net'
Subject: [CTPP] RE: Problem Importing CA PL data
What size files are getting in Access when you try to import the files? In Texas, the files were too big to import all three at once into Access. Access has a code limited total data file size of 2 GB. I could import two of the files in any sequence and Access would always choke on the third, saying that the file was unrecognized or corrupt, but the actual problem was that the 2 GB limitation was being exceeded. Using a 64-bit version did not help as the 2 GB is a Microsoft Access program code limit, not a computing power limit. I finally had to hook the geoheaders up to the two data files in two steps, then cut down the resulting databases into substate areas that could be handled within the coding limitations of Access and Excel before hooking the data up to the census geography files in our GIS system.
Robert R. Allen, AICP
Abilene MPO
400 Oak St. #102, Abilene, TX 79602
Phone 325-676-6243
Fax 325-676-6398
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
[cid:image001.jpg(a)01CBDE55.8687BC20]
Today--the Census Bureau published its final rules concerning the Count
Question Resolution (CQR)Program. The CQR Program will address requests
for corrections to the 2010 Census count of housing units and/or group
quarters (GQs) and associated population, based on three types of
challenges (1) boundary, (2) geocoding, and (3) coverage. According to the
Notice, the CQR Program is not a mechanism or process to challenge or
revise the population counts sent to the President by December 31.
http://edocket.access.gpo.gov/2011/2011-5217.htmhttp://edocket.access.gpo.gov/2011/pdf/2011-5217.pdf
--
Ed Christopher
FHWA Resource Center Planning Team
4749 Lincoln Mall Drive, Suite 600
Matteson, IL 60443
708-283-3534 (V)
708-574-8131 (C)
What size files are getting in Access when you try to import the files? In Texas, the files were too big to import all three at once into Access. Access has a code limited total data file size of 2 GB. I could import two of the files in any sequence and Access would always choke on the third, saying that the file was unrecognized or corrupt, but the actual problem was that the 2 GB limitation was being exceeded. Using a 64-bit version did not help as the 2 GB is a Microsoft Access program code limit, not a computing power limit. I finally had to hook the geoheaders up to the two data files in two steps, then cut down the resulting databases into substate areas that could be handled within the coding limitations of Access and Excel before hooking the data up to the census geography files in our GIS system.
Robert R. Allen, AICP
Abilene MPO
400 Oak St. #102, Abilene, TX 79602
Phone 325-676-6243
Fax 325-676-6398
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
[cid:image001.jpg(a)01CBDE37.633CD610]
Hello: there are three files in the release of data in Louisiana.
I parsed the file you are having trouble with the newest version of XL. But here is a tip that you might have not known.
I had trouble unzipping the data files (PL1, PL2) and (PL3, PL4) and the geo code file. Pl files were comma delimited files. The geocode file was space deliminted. I had to unzip the files on a linux machine because when I unzipped them on a windows machine, the end of record code was not seen. The instructions I received in Louisiana said that they were complied on a linux machine and I had to add a character to unjumble the data if I unzipped on a Window machine.
I quote the readme file:
Once the package is unzipped, the files are in flat ASCII format. The geographic header file contains fixed fields while the data files are in comma-delimited format. Both the geographic header and the data files contain geographic linking fields. These files have been constructed in a LINUX environment. They use an ASCII linefeed, chr(10), to indicate a new record.
For successful use with many programs running in a Windows environment, these files need to be modified to use the ASCII carriage return/linefeed sequence, chr(13) + chr(10) as a record terminator. This is an easy step in the UnZIP process using any UnZIP software which offers the conversion option. Winzip for Windows, version 14.5 has been tested.
The resulting file will meet the ANSI MS-DOS/Windows standard used by Access 2000 and Access 2007 and other MS Windows-based programs. If the data are being processed in a LINUX environment, they can be unzipped using any standard Linux ZIP/UnZIP package.
Mike LeBlanc, AICP<http://www.planning.org/AICP/>
Planning Manager, Special Projects Division
Rosa Parks Transportation Center
Lafayette Consolidated Government
Department of Traffic and Transportation
P.O. Box 4017-C
101 East Cypress Street
Lafayette, LA 70502
Ph: 337-291-7179
Fx: 337-291-8458
Planning for the 21st Century<http://www.lafayettelinc.net/>
From: ctpp-news-bounces(a)chrispy.net [mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
[cid:image001.jpg(a)01CBDE31.31018CA0]
Confidentiality Notice: This e-mail message, including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any receipt and/or response to this email may be considered a PUBLIC RECORD. If you have received this email in error, please notify the sender immediately. Any unauthorized review, use, disclosure, or distribution is prohibited.
Lafayette Parish Consolidated Government
705 W. University Avenue
Lafayette, LA 70506
http://www.lafayettela.gov
Hi Everyone,
My name is Elizabeth He and I am DBA at DVRPC (Delaware Valley Regional Planning Commission). Recently, I have developed a Java Program that import ACS data files into Oracle database. You can watch the demo video from http://www.youtube.com/watch?v=vWvtr5sLGkQ
I am willing to share the program with anyone who needs it. Just send me an email at ehe(a)dvrpc.org<mailto:ehe(a)dvrpc.org>.
Elizabeth He, Database Administrator
Delaware Valley Regional Planning Commission
190 N. Independence Mall West, 8th Floor
Philadelphia, PA 19106
Phone: 215-238-2889
I'm not sure what you're asking, but 3 year ACS data may be available at the TAD level, while 5 year data will be available at the TAZ level. There is a recorded webinar with training on delineating TAZ and TAD, it is currently available at: http://fhwa.adobeconnect.com/p11971019/
-----Original Message-----
From: ctpp-news-bounces(a)chrispy.net on behalf of Webster, Daniel
Sent: Tue 3/8/2011 2:32 PM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] TAZ/TAD delineation
What are the other States/MPO's approach to defining TAD's? Is there
data that might be available at the TAD level and not at the TAZ level?
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
[cid:image001.jpg(a)01CBDDA9.1A122780]
I think that 2010 Census Summary File 1 scheduled for release in June
2011 has it.
Bob Schwartz, Data Administrator Phone: (412) 391-5590 Ext
336
Southwestern Pennsylvania Commission FAX: (412) 391-9160
Regional Enterprise Tower E-mail:
schwartz(a)spcregion.org
425 Sixth Avenue, Suite 2500 SPC web address:
www.spcregion.org
Pittsburgh, PA 15219-1852 My normal hours:
Mon-Fri 7:15-3:30
-----Original Message-----
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Kevin Byrnes
Sent: Tuesday, March 08, 2011 1:18 PM
To: ctpp-news(a)chrispy.net
Subject: RE: [CTPP] Rules for 2010 Count Question Resolution Program
Does anyone know the expected schedule for producing the block-level
data with group quarter counts by block? It would seem that this won't
be released until after this QRP effort.
Kevin F. Byrnes, AICP
Director of Regional Planning & Regional Demographer
George Washington Regional Commission (GWRC)
406 Princess Anne St
Fredericksburg VA 22401
Ph (540) 373-2890 (ext 18)
Fax (540) 899-4808
-----Original Message-----
From: ctpp-news-bounces(a)chrispy.net
[mailto:ctpp-news-bounces(a)chrispy.net] On Behalf Of Ed Christopher
Sent: Tuesday, March 08, 2011 10:46 AM
To: ctpp-news(a)chrispy.net
Subject: [CTPP] Rules for 2010 Count Question Resolution Program
Today--the Census Bureau published its final rules concerning the Count
Question Resolution (CQR)Program. The CQR Program will address requests
for corrections to the 2010 Census count of housing units and/or group
quarters (GQs) and associated population, based on three types of
challenges (1) boundary, (2) geocoding, and (3) coverage. According to
the
Notice, the CQR Program is not a mechanism or process to challenge or
revise the population counts sent to the President by December 31.
http://edocket.access.gpo.gov/2011/2011-5217.htmhttp://edocket.access.gpo.gov/2011/pdf/2011-5217.pdf
--
Ed Christopher
FHWA Resource Center Planning Team
4749 Lincoln Mall Drive, Suite 600
Matteson, IL 60443
708-283-3534 (V)
708-574-8131 (C)
_______________________________________________
ctpp-news mailing list
ctpp-news(a)chrispy.nethttp://www.chrispy.net/mailman/listinfo/ctpp-news
_______________________________________________
ctpp-news mailing list
ctpp-news(a)chrispy.nethttp://www.chrispy.net/mailman/listinfo/ctpp-news
