topic Re: 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 in Python Questions

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 — Wed, 26 Aug 2015 14:41:09 GMT

Re: 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

JustinJohnson2 — Sat, 11 Dec 2021 07:23:36 GMT

If you just need to convert the filename from xls to dbf, this is how I did something similar in a recent project.

It recursively searches a directory for any files with the extension you want (in this case ".xls"), then generates a filename in an output directory with the other extension (".pdf").

import os.path

rootdir = r'C:\projects'
outpath = r'C:\output'

for (dirName, subdirList, fileList) in os.walk(rootdir):
    for filename in fileList:
        if os.path.splitext(filename)[1].lower() == ".xls":
            
            pdffilename = os.path.join(outpath, filename[:-3] + "pdf")
            // do your conversion here
            
            print pdffilename

Re: 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

DarrenWiens2 — Wed, 26 Aug 2015 16:52:25 GMT

Can you please: 1.) post your code directly (help here) so we can help without typing out your example, and 2.) provide exactly what 'print project' produces? It appears you have two different lists: "spreadsheet" and "spreadsheet_list". As it is written, we need to know what's in "spreadsheet".

Re: 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

RebeccaStrauch__GISP — Sat, 11 Dec 2021 07:23:38 GMT

Something like this will work, and I can see the dbf in Catalog, but couldn't open in Excel. It may be do to the name and/or column lengths I had in my sample. you would need to play with that. But it depends on what you are are planning on using the .dbf files for.

import arcpy
import os
theWorkspace = r"c:\__temp"
arcpy.env.workspace = theWorkspace  
sheets = ["BasinReserve", "DowseToPetone", "ManaParemta"]
table = "anExcel.xlsx"
table = os.path.join(theWorkspace, table)
print table
for sheet in sheets:
  inputTable = table + "\\" + sheet + "$"
  print inputTable
  arcpy.TableToTable_conversion(inputTable, theWorkspace, sheet )

Re: 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 — Wed, 26 Aug 2015 22:27:41 GMT

Hi Darren this is what i am trying to do, add all the information from spreadsheets to a feature class by joining tables copying information and removing join

Re: 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

DarrenWiens2 — Wed, 26 Aug 2015 22:34:26 GMT

The point of my post was that I don't particularly care what your script does, I just wanted to help without having to type out the relevant parts of your script. By posting your code directly into a code block, we can copy/paste it into our response - we can't do that with a screenshot.

In any case, it looks like Rebecca has gone to the trouble of typing out a functioning solution, so I'd go with that.

Re: 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

RebeccaStrauch__GISP — Wed, 26 Aug 2015 22:41:05 GMT

What Darren mean is, check out Posting Code blocks in the new GeoNet instead of just a screen shot. Can't help as much without the code.

Re: 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 — Wed, 26 Aug 2015 22:42:40 GMT

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"

Re: 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

RebeccaStrauch__GISP — Wed, 26 Aug 2015 22:48:38 GMT

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.

Re: 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 — Sat, 11 Dec 2021 07:23:41 GMT

#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"

Re: 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 — Wed, 26 Aug 2015 23:08:31 GMT

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

Re: 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 — Thu, 27 Aug 2015 01:40:43 GMT

Thanks Rebecca, this piece of code took me a while to get it but was helpful. Cheers

Re: 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

RebeccaStrauch__GISP — Sat, 11 Dec 2021 07:23:44 GMT

So were you able to get it to do what you needed? I haven't really dug into your code, but it sounds like you are joining a bunch of tables so you can copy attributes to one of them from the other? Have you looked at

Join Field—Help | ArcGIS for Desktop ?? That allows you to join (permanently) to tables based on a in_field matching join_field, and you can select which of the fields you want from the join_table.

Sample from help:

# Purpose: Join two fields from a table to a feature class 

# Import system modules
import arcpy
from arcpy import env

# Set the current workspace 
env.workspace = "c:/data/data.gdb"

# Set the local parameters
inFeatures = "zion_park"
joinField = "zonecode"
joinTable = "zion_zoning"
fieldList = ["land_use", "land_cover"]

# Join two feature classes by the zonecode field and only carry 
# over the land use and land cover fields
arcpy.JoinField_management (inFeatures, joinField, joinTable, joinField, fieldList)

That might be what you are trying to do?

Re: 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 — Thu, 27 Aug 2015 10:41:56 GMT

Yeah i got that working. Join field dint really seem helpful because of the long processing time and the number of fields i have to deal with.