Last week I got the following question; how can I link a Shape-file and an Excel-sheet and create a resulting Shape-file with just a subset of the combined attributes.
In the Shape-file was the geometry of road area’s incl. road-number, road-area-number and road-area-sub-number. In the Excel-sheet there where the same numbers and other details about the road area’s. This Excel-sheet was exported from road management software.
If the AutoCAD Map user is an senior ArcGIS user, than you’ve a challange as consultant.
We did the following in the form of a handson workshop:
To link both files, we use the FDO-function: Create a Join. We need to connect both as FDO-connection into AutoCAD Map and there has to be one or more common fields to use as matching columns.
To make an FDO-connection with an Excel-sheet it is easier to import the sheet into an Access-table first. Than an FDO-connection can be created to the Access-table using the following Connection String: Driver={Microsoft Access Driver (*.mdb)};DBQ=pathnamefilename.mdb When the Excel-data is inside Access, it is also easy to make the columns used to join of the same database-type ( Text or Numbers ).
In the screen below is the FDO-connection to the Shape-file selected as Primary table and the FDO ODBC-connection to the Access-table as Secondary table. For the Join we linked the following columns:
WEG_NR002 -> Weg
WEGV_NR002 -> Vak
WEGVONR002 -> Ond
In the FDO DataGrid is the combination of all columns from both sources visible. To create a resulting Shape-file with just a subset of the attributes is not an easy task in AutoCAD Map. In ArcGIS a piece of cake, is in AutoCAD Map quite a challenge.
To create a new Shape-file with in the DBF-database the content of the original file supplemented with the Excel-sheet, it has to be exported into an SDF-file first. This local geospatial database has the complete combined structure. With FDO Schema Editor it is possible to create a new Shape-file with just the columns needed. With FDO BulkCopy funtionality the content of the temporary SDF-file can be copied over into the empty Shape-file, where just a subset of the columns can be selected.
