open dbf from shp file in excel using phyton

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

open dbf from shp file in excel using phyton

I've been working on this a few days now I am hoping someone can help me. I am trying to achive the following steps using a python script:

Create destination Excel sheet

In a loop

        Go to *.shp file
        Open up *.dbf in Excel

        Write the shape file name on a line in Excel sheet
        move down a row

        copy the dbf contents as rows in the excel sheet

        move down two rows from the last line of content

        loop to next shape file

Below is a modification of the python script I downloaded from the community.
When this script is run the following error message occurs.

Traceback (most recent call last):
File "C:\Users\Script\Desktop\A\SCRIPTS_ArcGIS\",
line 100, in <module>

I cannot understand why basefileDBF is not defined when line 75 clearly defines basefileDBF
as a string variable

Also instead of creating seperate work sheets I'd like to paste the contents of the dbf file one under the
each other on a single worksheet

Description: Imports individual DBF files into one Microsoft Excel Spreadsheet as seperate worksheets.
Known limitation:  too many to list
Requirements:  Python and the Python for Windows extention (win32 Extension) to be installed.

Author: Jennifer McCollom
Created: 20070531

Modified by:  Polabear
Mod 1:  20090728


import os, string, sys, arcgisscripting, win32com.client

#Create the Geoprocessor object
gp = arcgisscripting.create()  #mod 20090728 for arc 9.3

#Open MS Excel
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1

#Read in the *.dbf file names(shape file components)from the prepared
#     text file
input = open("C:\DFO_Spreadsheet\DFO_dbf.txt")
DBFs = input.readlines()

OutputFolder = "c:\desktop\Andrea\SCRITS_ArcGIS"  #the output folder for the XLS file
XLSName = "DFO_contents.xls" #name of the output XLS file
#This section below tests the DBF names to make sure they are not repeating
DBFnamelist = []        #creates an empty list
j=0                     #starts a zero based counter for the list index
for DBF in DBFs:
    if (j == 0):
        dDBF = DBF[-1]
        name = str(os.path.split(dDBF)[1])   #strips the path off of the full name to leave just the dbf filename
        DBFnamelist.append(name)            #adds the dbf filename to the new list
        j = j + 1                           #advances the counter
        dBF = DBF[-1]
        name = str(os.path.split(dDBF)[1])   #strips the path off of the full name to leave just the dbf filename
        DBFnamelist.append(name)            #appends the dbf filename to the end of the list
        cntret = DBFnamelist.count(name)    #returns a count of the number of times the current DBF filename appears in the list
        if (cntret > 1):                    #if the name appears more than once
            gp.AddMessage("\n" + "Attention!!  " + name + " is a repeating DBF filename.  Be aware this may cause errors when compiling into the final XLS file."+"\n")
        j = j + 1
#Loops through the DBFs, opens the file in MS Excel and adds the next dbf to the first dbf (as a worksheet) to compile the whole set
i = 0 #creates a one based counter for indexing the worksheets

for DBF in DBFs:
      DBF = DBFs[i]
      dDBF = DBF[:-1]
      #Open the DBF in MS Excel
      gp.AddMessage("File: " + DBF + " -Opened Successfully")
      #Set up a system to distinquish between the first file and all of the others..
      if (i == 0):          #only the first file
        DBF = DBFs[i]
        dDBF = DBF[:-1]
        basefileDBF = str(os.path.split(dDBF)[1])    #line 100 error DBFbasefile not defined save just the name (stripping off the path) of the first dbf file
        gp.AddMessage("DBF Base file is " + basefileDBF)
        DBF = DBFs[i]
        dDBF = DBF[:-1]
        filenameDBF = str(os.path.split(dDBF)[1])    #save just the name (stripping off the path) of the current dbf file
        xlApp.Workbooks(filenameDBF).Sheets(1).Move(After=xlApp.Workbooks(basefileDBF).Sheets(i+1-1))  #moved the current DBF to the last worksheet of the DBF basefile
        gp.AddMessage ("Compiled " + filenameDBF + " into " + basefileDBF + "\n")
      #Advance the integer      
      i = i + 1

      print "cannot access DBFs"

#Set up the output filename with the path and the correct extension
XLSNameLen = len(XLSName)
if (XLSName[-4:XLSNameLen] == ".xls"):
  OutFile = OutputFolder + "\\" + XLSName
  OutFile = OutputFolder + "\\" + XLSName + ".xls"

#Save the DBF basefile (with the compiled dbfs) to the output XLS
gp.AddMessage("Saved the compiled DBFs as " + OutFile + "\n")

##NameError: name 'basefileDBF' is not defined