Automate Exporting of Layer Joined to Excel Data?

[AnthonyGallo]

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]

At least it is a pile of Excel and not a pile of something else 🙂

Just an idea: use a File Geodatabase to as the data container for all of this data management processing you wish to do.  I've simply resigned to the fact that the system engineers at ESRI have tightly coupled the GDB to ArcGIS for a reason and therefore perhaps it is a good idea to follow.  That is, I've found that moving to the gdb model (for example, moving my attribute tables into this container) a good chunck of my problems simply go away.

I say it may be worth the effort to skip this idea of tightly coupling your GIS processing to Excel as it can be a management nightmare (field mapping, nulls, field naming,  changes to the base excel tables, $Sheet management, blah, blah, blah) just is downright unappealing IMO.

I'd look into making my first Python effort that of converting the Excel tables to a FGDB (it can hold 2 TB from last I heard).  All of your joins and such will be on their best behavior then and you will have a lot more control over that source data. 

Does that make sense?

I'd like to hear other suggestions, but personally this would be my first move.

Edit: just to add to my post.  I'd also consider doing all of your joins and follow-up exports within the "IN_MEMORY" workspace if possible.  You will gain peformance and can easily cleanup the residual data left behind, while generating your desired output to disk.

[AnthonyGallo]

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?

[JamesCrandall]

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?

[AnthonyGallo]

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]

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.