Difference between revisions of "Main Page/Research/Importing ZCTA census data"

Revision as of 21:22, 26 January 2009

<source lang="vb"> Option Compare Database Sub ImportGeo()

   ' variables
   Dim strSpec As String
   Dim strOutTbl0 As String
   Dim strTempTable As String
   Dim strInAscii As String
   Dim rs As Recordset
   Dim i As Integer
   Dim iStr As String
   'Dim tdef As TableDef
   Dim CurrentDb As DAO.Database
   Dim strSF3GEO As String
   Dim tbl As New ADOX.Table
   Dim col As New ADOX.Column
   Dim cat As New ADOX.Catalog
   
   cat.ActiveConnection = CurrentProject.Connection
 
   Set CurrentDb = DBEngine.Workspaces(0).Databases(0)
   Debug.Print (CurDir)
   
   asciidir = "D:\users\phurvitz\gisdata\us_census\all_wa\"
   outdbdir = "D:\users\phurvitz\gisdata\us_census\all_wa\zcta\"
   
   ' import geo if necessary
   strSF3GEO = "SF3GEO"
   strSF3GEO_temp = "strSF3GEO_temp"
   
   ' does the temp import table exist? We need to import all records first
   If TableExists(strSF3GEO_temp) Then
       Debug.Print ("not import geo")
   Else
       Debug.Print ("import geo")
       DoCmd.TransferText acImportFixed, strSF3GEO & " Import Specification", strSF3GEO_temp, _
           asciidir & "wageo.txt", No
   End If
   
   ' do this for each table that exists
   For Each tbl In cat.Tables
   
       If tbl.Name = strSF3GEO_temp Then
           
           ' block groups---------------
           If (boolRunGeoZCTA = 6) Then
               If TableExists(strSF3GEO) Then
                   droptable (strSF3GEO)
               End If
               If Not TableExists(strSF3GEO) Then
                   strSQL = "SELECT " & strSF3GEO_temp & ".* INTO " & strSF3GEO & " FROM " & strSF3GEO_temp & _
                       " WHERE (((" & strSF3GEO_temp & ".SUMLEV)='881'));"
                   DoSQL (strSQL)
               End If
           Else
               GoTo skipend
           End If
       End If

   Next
   
               

   Debug.Print ("")
   Debug.Print ("Done with " & strSF3GEO)

skipend:

End Sub Sub ImportTables()

   ' variables
   Dim strSpec As String
   Dim strOutTbl0 As String
   Dim strTempTable As String
   Dim strInAscii As String
   Dim rs As Recordset
   Dim i As Integer
   Dim iStr As String
   'Dim tdef As TableDef
   Dim CurrentDb As Database
   
   ' current db
   Set CurrentDb = DBEngine.Workspaces(0).Databases(0)
   
   ' geo table name
   strSF3GEO = "SF3GEO"
   
   asciidir = "D:\users\phurvitz\gisdata\us_census\all_wa\"
   outdbdir = "D:\users\phurvitz\gisdata\us_census\all_wa\"
   strOutDB = outdbdir & "SF3_zcta.mdb"
   
   ' run on which aggregation units?

' boolRunBG = MsgBox("Create Block Geographic Identifier tables for Block Groups?", vbYesNo, strSF3GEO) ' boolRunTract = MsgBox("Create Block Geographic Identifier tables for Tracts?", vbYesNo, strSF3GEO) ' boolRunZCTA = MsgBox("Create Block Geographic Identifier tables for ZCTAs?", vbYesNo, strSF3GEO)

   '-------------------------------------------------------------
   ' import all  files
   i = 1
   Do While i < 76
   

       ' define increment values
       ' pad with zero
       iStr = right("0" & i, 2)
       ' import specification name
       strSpec = "SF300" & iStr & " Import Specification"
       ' table name
       strTempTable = "junk"
       strOuttable = "SF300" & iStr
       ' input ascii file
       strInAscii = asciidir & "wa000" & iStr & ".txt"
       
       ' delete any existing temp table
       If TableExists(strTempTable) Then
          droptable (strTempTable)
       End If
               
       ' import
       Debug.Print ("importing " & strTempTable & "(" & strOuttable & strInAscii & ")")
       DoCmd.TransferText acImportDelim, strSpec, strTempTable, strInAscii, No
       
       ' perform the selection query to make a new table (the correct one) and push this to a geodb
       strSQL = "SELECT " & strTempTable & ".* INTO " & strOuttable & " IN '" & strOutDB & "'" & _
           "FROM " & strTempTable & " INNER JOIN SF3GEO ON " & strTempTable & ".LOGRECNO = SF3GEO.LOGRECNO;"
       DoSQL (strSQL)
       ' delete the temp file
       droptable (strTempTable)
       
       ' increase index iteration
       i = i + 1

   Loop
   
   Debug.Print ("Done")

End Sub </source>