I have three separate Excel tables that I'm interested in joining/bringing into an SDE spatial view so that the data in the tables can be opened/viewed by all at the same time. I'm wanting the data to update dynamically from the Excel tables, which is why I'm interested in using a spatial view. I have three questions: 1) Can a spatial view read non-SDE data (such as Excel tables saved on a network drive) 2) How would I point the spatial join description/query to the location on the network drive if that is possible and 3) Is there a better solution for this altogether?
I would recommend developing an ETL script (Python, or other) that would regularly update tables in your database from your excel files. I would include some value checks for fields to make sure the excel files do not include unsupported characters.
Run the script every day or weekly, or whatever interval you desire. That way you can then interact with your data in the database, and utilize any combination of views, etc.
Thanks for the quick reply, Alex. That would work - to an extent. We don't have FME server or anything that would handle the automation for us outside of Python (which I don't have time to figure out for this project). We were also hoping for something that was truly dynamic within the GIS (again, why I'm curious about whether the spatial view would work how I've assumed it to work). We would want real-time edits reflected in the SDE view based on the data in the Excel files. Again, I'm not sure if this is possible or not...
1) No, a spatial view can only see what SQL can see, which is tables in the database
2) Not possible, but using network drives is a poor solution, even for situations where it is possible.
3) The only solution is to load the data into the database. Note that Excel is not a database format, and shouldn't be considered one; working with data coming out of Excel requires an awful lot of error handling. Real-time or near-real-time updates based on a flat file are extremely difficult to detect and manifest - a mediocre implementation could take weeks to stabilize, and a robust solution might involve months and custom modifications to the database itself.
Thanks, Vince. I didn't think it was an option, but wanted to be sure. I think we're just going to utilize an SDE versioned non-spatial table to accomplish what we need to and load it with the Excel data to start.
And as far as using network drives, I really don't think there's a company that exists that doesn't use them for non-spatial data