Wednesday, January 30, 2013

GAMS/VBScript Tool to view DBF table structure

When receiving DBF files I usually look at the structure by loading it into Excel. In this case I received DBF files that were much too large to be handled by Excel. Before writing queries it is useful to be able to look at the column names and types. That is what this GAMS job does:

$ontext

  
Describe database tables

  
Gives description of DBF files in project directory

$offtext

$set dir  %system.fp%
$set connection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%dir%;Extended Properties=dBASE IV;"

$onecho > db.vbs

WScript.Echo "------------------------------------------------------------------------"

Const adSchemaTables = 20
Const adSchemaColumns = 4

Set objConnection = CreateObject("ADODB.Connection")
WScript.Echo "ADO Version:",objConnection.Version
objConnection.ConnectionString = "%connection%"
WScript.Echo "ADO Connection:",objConnection.ConnectionString
Wscript.Echo
objConnection.Open
Set rs1 = objConnection.OpenSchema(adSchemaTables)
if (rs1.Eof) then
  
WScript.Echo "No tables found"
end if
Do Until rs1.Eof
   
TableName = rs1("Table_Name")
   
Set rs2 = objConnection.OpenSchema(adSchemaColumns,Array(Null, Null, TableName))
   
Wscript.Echo UCase(TableName)


   
Do While Not rs2.EOF

       
ColumnName = rs2("Column_Name")
       
ColTypeNum = rs2("Data_Type")
       
ColClen    = rs2("Character_Maximum_Length")

       
select case ColTypeNum
          
case 20   ColTypeName = "BigInt"
          
case 128  ColTypeName = "Binary"
          
case 11   ColTypeName = "Boolean"
          
case 129  ColTypeName = "Char"
          
case 6    ColTypeName = "Currency"
          
case 7    ColTypeName = "Date"
          
case 135  ColTypeName = "TimeStamp"
          
case 14   ColTypeName = "Decimal"
          
case 5    ColTypeName = "Double"
          
case 72   ColTypeName = "GUID"
          
case 9    ColTypeName = "Dispatch"
          
case 3    ColTypeName = "Integer"
          
case 205  ColTypeName = "LongVarBinary(Image,Blob)"
          
case 201  ColTypeName = "LongVarChar(Memo,Text)"
          
case 203  ColTypeName = "LongVarWChar(Memo,Text)"
          
case 131  ColTypeName = "Numeric"
          
case 4    ColTypeName = "Single"
          
case 2    ColTypeName = "SmallInt"
          
case 17   ColTypeName = "UnsignedTinyInt(Byte)"
          
case 204  ColTypeName = "VarBinary"
          
case 200  ColTypeName = "VarChar"
          
case 12   ColTypeName = "Variant"
          
case 202  ColTypeName = "VarWChar"
          
case 130  ColTypeName = "WChar"
          
case else ColTypeName = ColTypeNum
       
end select

       
Wscript.Echo  "    ", ColumnName & ", " & ColTypeName & ", " & ColClen
       
rs2.MoveNext
   
Loop
   
rs2.Close

   
Wscript.Echo
   
rs1.MoveNext
Loop

rs1.Close
objConnection.Close

WScript.Echo "------------------------------------------------------------------------"

$offecho

$call cscript db.vbs

The output looks like:


--- Job describe.gms Start 01/30/13 15:24:38 WEX-VS8 23.9.5 x86/MS Windows       
GAMS Rev 239  Copyright (C) 1987-2012 GAMS Development. All rights reserved
Licensee: Erwin Kalvelagen                               G120717/0001CV-WIN
          Amsterdam Optimization Modeling Group                      DC4572
--- Starting compilation
--- describe.gms(80) 2 Mb
--- call cscript db.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

------------------------------------------------------------------------
ADO Version: 6.1
ADO Connection: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\projects\ulrike - ifpri\gdx\;Extended Properties=dBASE IV;

SPAM2005_HARVA_TALL_BETA
     ALLOC_KEY, WChar, 8
     CELL5M, Double,
     CR_NAME_SH, WChar, 4
     CR_VALUE, Double,
     ISO3, WChar, 3
     PROD_LEVEL, WChar, 7
     REC_TYPE, WChar, 1
     TECH, WChar, 1
     UNIT, WChar, 6

SPAM2005_HARVA_TALL_BETA_1
     ALLOC_KEY, WChar, 8
     CELL5M, Double,
     CR_NAME_SH, WChar, 4
     CR_VALUE, Double,
     ISO3, WChar, 3
     PROD_LEVEL, WChar, 7
     REC_TYPE, WChar, 1
     TECH, WChar, 1
     UNIT, WChar, 6

SPAM2005_PROD_TALL_BETA_1
     ALLOC_KEY, WChar, 8
     CELL5M, Double,
     CR_NAME_SH, WChar, 4
     CR_VALUE, Double,
     ISO3, WChar, 3
     PROD_LEVEL, WChar, 7
     REC_TYPE, WChar, 1
     TECH, WChar, 1
     UNIT, WChar, 6

------------------------------------------------------------------------
--- describe.gms(81) 2 Mb
--- Starting execution - empty program
*** Status: Normal completion
--- Job describe.gms Stop 01/30/13 15:24:38 elapsed 0:00:00.316

I looked for some free DBF viewers, but they largely originate from obscure Russian web sites and Google warned not to open the downloaded zip files. This ADO based approach should at least create no problems like introducing viruses.