i have got a list of excel spread sheets that i want to convert in to .dbf and save them with the same name. can someone tell me how to could you use the sheet name only from the whole path in spreadsheet list, thanks

JKBellamkonda · ‎08-26-2015

JKBellamkonda · ‎08-26-2015

sorry tried that i couldnt find where the advanced editor is. will this be helpfull?

#Import system modules

import arcpy

import os

import xlrd

#Check license for extensions

print "checking license for extensions"

arcpy.CheckOutExtension("Analysis")

#Setting workspace

print "setting up workspace"

arcpy.env.workspace = os.getcwd()

#Overwrite outputs

print "Outputs will be overwritten"

arcpy.env.overwriteOutput = True

#Input data

print "checking input data layers"

primary = "\\nz-primary-parcels\\nz-primary-parcels.gdb\\NZ_Primary_Parcels"

TitleOwners = "\\nz-property-titles-including-owners\\nz-property-titles-including-owners.gdb\\NZ_Property_Titles_Including_Owners"

Out_path = "C:\\Users\\jkrishna\\Documents\\TPG Mapping\\NZTA\\Wellington\\NZ5yearRollingPlan\\Data\\Wellington-Geodatabase.gdb"

Table = "\\Wellington.xls"

#out_join = "\\Wellington-Geodatabase.gdb\\TitleOwnerJoin"

#Adding a parcel ID Field and Project Field to primary parcels

print " Adding ParcelID field and Project field"

arcpy.AddField_management (primary, "ParcelID", "LONG", "", "", 15)

arcpy.AddField_management (primary, "Project", "TEXT", "", "", 30)

arcpy.AddField_management (primary, "SAP_Ref", "LONG", "", "", 15)

#Copying data from ID field to ParcelID

print "Copying data from id field to ParcelID"

arcpy.CalculateField_management (primary, "ParcelID", '[id]')

#Create a layer file to work with

print "Creating a primary layer file"

layerfile = "primary.lyr"

arcpy.MakeFeatureLayer_management(primary, layerfile)

#Spatial join primary parcel and titleowners

print "Spatial joining primary parcel and titleowners"

out_join = Out_path + "\\TitleOwnerJoin"

arcpy.SpatialJoin_analysis (layerfile, TitleOwners, out_join, "JOIN_ONE_TO_MANY", "KEEP_ALL", "", "WITHIN")

spreadsheet = [Table + "\\BasinReserve$", Table + "\\DowseToPetone$"]#, Table + "\\InnercityBypass$", Table + "\\M2pp$",

#Table + "\\ManaParemata$", Table + "\\MellingIc$", Table + "\\MungavinIc$", Table + "\\MuriroadPukeruabay$",

#Table + "\\MtVicTunnel$", Table + "\\NgaurangaGorge$",Table + "\\NO2LTaylorsRdtoLevin$", Table + "\\NO2LWaitarereCurves$",

#Table + "\\PP2O$", Table + "\\PaekakarikiIC$",Table + "\\ParemataCarParking$", Table + "\\PukeruaBay$",

#Table + "\\SH2BPHuttRd$", Table + "\\SH58$", Table + "\\TransmissionGully$", Table + "\\WaitangiruaCorridor$",

#Table + "\\WUMAuroraTerrace$", Table + "\\WUMMurphyToMolesworthSt$", Table + "\\WUMTinakoriRd$"]

for project in spreadsheet:

print spreadsheet

#Convert excel sheet to Dbase table

print " Converting excel sheet to Dbase table"

out_dbase = "C:\\Users\\jkrishna\\Documents\\TPG Mapping\\NZTA\\Wellington\\NZ5yearRollingPlan\\Data"

out_name = project + ".dbf"

arcpy.TableToTable_conversion (project, out_dbase, out_name)

#Create a layer file to work with

print "Creating a parceljoin layer file"

joinlayer = "parceljoin.lyr"

arcpy.MakeFeatureLayer_management(out_join, joinlayer)

#Join title owners featureclass,Project dbf and Copy data from project dbf to title owners featureclass

print" Join table using parcelid field"

arcpy.AddJoin_management (joinlayer, "ParcelID", out_name, "ParcelID", "KEEP_COMMON")

print "Starting edit session"

arcpy.da.Editor(Out_path)

print "copying SAP no"

arcpy.CalculateField_management (joinlayer, "TitleOwnerJoin.SAP_Ref", '['+project+'.SAP_Refere]')

print "Copying project info to project field"

arcpy.CalculateField_management (joinlayer, "Project", '"'+project+'"')

print "Remove join"

arcpy.RemoveJoin_management (joinlayer, name)

print "done"

RebeccaStrauch__GISP · ‎08-26-2015

You won't be able to get the advanced editor if you are reading it thru your GeoNet Inbox. you will need to select on the Thread title and open it directly in a tab. Then you should be able to see it

Without it being posted that way, the spacing gets all out of whack, which messes with the python processing.

JKBellamkonda · ‎08-26-2015

just few changes in that, it was meant to be "print project" instead of "print spreadsheet" and "project" instead of "name" in last indentation

JKBellamkonda · ‎08-26-2015

#Import system modules
import arcpy
import os
import xlrd

#Check license for extensions
print "checking license for extensions"
arcpy.CheckOutExtension("Analysis")

#Setting workspace
print "setting up workspace"
arcpy.env.workspace = os.getcwd()

#Overwrite outputs
print "Outputs will be overwritten"
arcpy.env.overwriteOutput = True

#Input data
print "checking input data layers"
primary = "\\nz-primary-parcels\\nz-primary-parcels.gdb\\NZ_Primary_Parcels"
TitleOwners = "\\nz-property-titles-including-owners\\nz-property-titles-including-owners.gdb\\NZ_Property_Titles_Including_Owners"
Out_path = "C:\\Users\\jkrishna\\Documents\\TPG Mapping\\NZTA\\Wellington\\NZ5yearRollingPlan\\Data\\Wellington-Geodatabase.gdb"
Table = "\\Wellington.xls"
#out_join = "\\Wellington-Geodatabase.gdb\\TitleOwnerJoin"


#Adding a parcel ID Field and Project Field to primary parcels
print " Adding ParcelID field and Project field"
arcpy.AddField_management (primary, "ParcelID", "LONG", "", "", 15)
arcpy.AddField_management (primary, "Project", "TEXT", "", "", 30)
arcpy.AddField_management (primary, "SAP_Ref", "LONG", "", "", 15)

#Copying data from ID field to ParcelID
print "Copying data from id field to ParcelID"
arcpy.CalculateField_management (primary, "ParcelID", '[id]')

#Create a layer file to work with
print "Creating a primary layer file"
layerfile = "primary.lyr"
arcpy.MakeFeatureLayer_management(primary, layerfile)

#Spatial join primary parcel and titleowners
print "Spatial joining primary parcel and titleowners"
out_join = Out_path + "\\TitleOwnerJoin"
arcpy.SpatialJoin_analysis (layerfile, TitleOwners, out_join, "JOIN_ONE_TO_MANY", "KEEP_ALL", "", "WITHIN")


spreadsheet = [Table + "\\BasinReserve$", Table + "\\DowseToPetone$"]#, Table + "\\InnercityBypass$", Table + "\\M2pp$",
             #Table + "\\ManaParemata$", Table + "\\MellingIc$", Table + "\\MungavinIc$", Table + "\\MuriroadPukeruabay$",
             #Table + "\\MtVicTunnel$", Table + "\\NgaurangaGorge$",Table + "\\NO2LTaylorsRdtoLevin$", Table + "\\NO2LWaitarereCurves$",
             #Table + "\\PP2O$", Table + "\\PaekakarikiIC$",Table + "\\ParemataCarParking$", Table + "\\PukeruaBay$",
             #Table + "\\SH2BPHuttRd$", Table + "\\SH58$", Table + "\\TransmissionGully$", Table + "\\WaitangiruaCorridor$",
             #Table + "\\WUMAuroraTerrace$", Table + "\\WUMMurphyToMolesworthSt$", Table + "\\WUMTinakoriRd$"]


for project in spreadsheet:
                  print spreadsheet

#Convert excel sheet to Dbase table
                  print " Converting excel sheet to Dbase table"
                  out_dbase = "C:\\Users\\jkrishna\\Documents\\TPG Mapping\\NZTA\\Wellington\\NZ5yearRollingPlan\\Data"
                  out_name = project + ".dbf"
                  arcpy.TableToTable_conversion (project, out_dbase, out_name)

#Create a layer file to work with
                  print "Creating a parceljoin layer file"
                  joinlayer = "parceljoin.lyr"
                  arcpy.MakeFeatureLayer_management(out_join, joinlayer)


#Join title owners featureclass,Project dbf and Copy data from project dbf to title owners featureclass
                  print" Join table using parcelid field"
                  arcpy.AddJoin_management (joinlayer, "ParcelID", out_name, "ParcelID", "KEEP_COMMON")


                  print "Starting edit session"
                  arcpy.da.Editor(Out_path)


                  print "copying SAP no"
                  arcpy.CalculateField_management (joinlayer, "TitleOwnerJoin.SAP_Ref", '['+project+'.SAP_Refere]')


                  print "Copying project info to project field"
                  arcpy.CalculateField_management (joinlayer, "Project", '"'+project+'"')


                  print "Remove join"
                  arcpy.RemoveJoin_management (joinlayer, name)

print "done"