Automate Exporting of Layer Joined to Excel Data?

Question

Hey all,

Odd problem here arising from being given a large pile of pretty crappy data in Excel. I have 35 Excel files which need to be joined to 35 corresponding feature classes; this part I'm able to accomplish with a Python script. However, I cannot figure out how, using Python, to export this data to DBF without either (a) losing the spreadsheet data I originally joined to or (b) having all of the resulting rows be NULL. The only table export method that is giving me what I want is manually going to the drop-down menu for the attribute table and exporting to DBF.

I suppose I could get around this and not burn too much time by just having my automated process create the 35 joined tables, and then manually export each one. If there's a way to automate the exporting, however, that would be great. Thanks.

JamesCrandall · Answer

Edit: if I add the spreadsheet I want to join to my map, then export to GDB, THEN join my feature class to the GDB table and run "table to table (conversion)", it successfully exports the table with the joined information.

Yep.  That's exactly what I was talking about in my OP.

You could just use in_memory as your workspace to get the Excel sheet(s) as a gdb table, run the join and export (copy rows) to your desired output.

I think you need to validate the time required to develop this vs. just doing in manually.  Also consider future useage in your estimate too.

AnthonyGallo · Answer

Can you manually perform the task?

Load your layer, join it to your excel spreadsheet. Right-click the layer and choose Data-->Export Data.

Does the resulting layer contain the data contained in the excel sheet?

Yes, for one shapefile-spreadsheet join (out of 35), this will work. I would like to automate this for the 35 files I have to save a few minutes of time (and more in the future). I don't believe there is an exact geoprocessing tool/function in Python replicate the "Right-click the layer and choose Data-->Export Data" function. The closest task I could find was "copy rows" or "table to table (conversion)". If I am exporting the feature class joined to the original Excel sheet, copy rows doesn't work because it only copies the original feature class fields and table-to-table doesn't work because it exports NULL values for all of the Excel sheet fields.

Edit: if I add the spreadsheet I want to join to my map, then export to GDB, THEN join my feature class to the GDB table and run "table to table (conversion)", it successfully exports the table with the joined information.

JamesCrandall · Answer

Interesting. I already have my feature classes in a GDB feature dataset, but the Excel files are all hanging out in a folder elsewhere. You're suggesting that I (have my script) pull in each of those Excel files into GDB tables and then do the join / table-to-table conversion export?

Edit: Absolutely I'd consider it because you already manage the Feature in the GDB.  Why not the related attributes too?

It is something I'd consider if there is not too much processing overhead.  However, if you do not have any issues with performing the joins to Excel and just need to do it programatically, then that is doable too.

Can you manually perform the task?

Load your layer, join it to your excel spreadsheet.  Right-click the layer and choose Data-->Export Data.

Does the resulting layer contain the data contained in the excel sheet?