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 )
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
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".
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
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.
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.
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)
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"
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.
#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"
just few changes in that, it was meant to be "print project" instead of "print spreadsheet" and "project" instead of "name" in last indentation
Thanks Rebecca, this piece of code took me a while to get it but was helpful. Cheers
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?
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.
Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
