Edit Data in SHP Layer Table

[Anonymous]

I'm new to Map 3d and GIS and I'm trying to figure out the best way to add and remove data from the SHP file Table that is available in the TASK PANE.  I have a large number of records that need to have a few columns added and I also want to remove numerous unneeded columns.  I'm sure there are many ways to do this and I'm hoping to be able to use Excel to modify the table and then somehow bring the new data back into the map.  None of the data is intended to change any of the map properties I just want it to be available when the Table is viewed.

Thanks in advance for any suggestions. 

[parkr4st]

that can be done by using the mapimport and mapexport commands

MAPIMPORT the .shp and select the data drop down and uncheck all the columns not wanted as object data. and select polygons to closed polygons if there are polygons.

The command ADEDEFDATA can be used to add OD fields at this stage if needed to add new columns in the data table of the shp file.

Select the CAD objects  and MAPEXPORT.  Select the .shp file type, new .shp file name, OD to attributes, any properties wanted which will add a column for each property selected.  Closed polylines to polygons selected if dealing with polygon data. and OK

Youtube has videos on the commands if not familiar.

There are other ways to add the columns if you can explain if you have the data in a spreadsheet for example or is data attached to other FDO files.   Explain the project more fully and there will be answers provided

dave

[ChicagoLooper · Accepted solution]

You're right, there are several ways to do this, using Excel to add columns and delete rows is not one of them. If it were me, I'd use a JOIN. A join doesn't change the existing data, it adds, or JOINS, an external file such as xlsx, xls, mdb. Other file types are also viable, I listed what i felt were the most common. Here's an explanation of how a Join would work.

Example: Using your uploaded image, row 1 of your shapefile table shows name=>278-PO-14-5-EB-A. Your external spreadsheet might have 240 rows and 3 columns. Your column headings might be a) ID, b) Condition, and c) Type. The column named ID in the spreadsheet would contain the same values as the NAME column from your shapefile. Condition and Type columns would be your new data. Condition=>good, fair, poor, or needs replacement. Type=>large medium, or small.

If a row in your spreadsheet contained a row with a) 278-PO-14-5-EB-A, b) Fair, c) Large, then Fair and Large would be added directly onto row 1 of your shapefile table and correspond to row 1's geometry. However, if your external spreadsheet does not have 27-PO-14-5-EB-A then the new columns in row 1 would be null and blank.

I'd keep the those extra columns you claim you don't need because I can work around them. If I really wanted to, I could delete them. Deleting them though, cannot be done using Excel.

Creating a JOIN does not change the existing shapefile geometry nor the existing shapefile data, it simply adds, or JOINS, additional data that's stored in an external file. Once you have the new data joined you can use this new data to isolate and label.

Examples: You may perform operations using the new data, such as:

1. Isolate all records with condition classified as 'needs replacement'
2. You may isolate and label the Type classified as 'Large' and use the NAME as an ID label for those large objects
3. You may use three different colors to differentiate between good, fair and poor

As you can see, the new data has enhanced the existing data and that increases your analytical capacity and your ability to efficiently draw high impact maps. All this is possible using an external file.

[Anonymous]

Dave,

Thank you for the reply.  I went thru the Map Import and Define Object Data steps a outlined but when I then added the SHP file via the Data button the Columns from the Object Data table were there but there were no values.

The first image above shows the OD table and the second one below shows what the table looked like after the Exported SHP file was loaded and the Table viewed.

EDITED:  After scrolling down the points with their appropriate values were in the table, for some reason it added a row for every point with no data.

This is a different question but part of the same workaround, once the OD tables have been created and are now empty of values,can they be filled by data in a spreadsheet or other type of file?  I have over a thousand points and I would like to be able to add data to their OD Fields without having to manually enter each piece of data.

Frank

[ChicagoLooper]

---

@Anonymous wrote:

.......the Columns from the Object Data table were there but there were no values......

.......once the OD tables have been created and are now empty of values,can they be filled by data in a spreadsheet or other type of file?  I have over a thousand points and I would like to be able to add data to their OD Fields without having to manually enter each piece of data.

---

I have used this method frequently, it's one of my go-to ways to create Object Data. Doing it this way, however, is not suitable for your specific purpose. Why? Because as your astute observation has already noticed, you must manually fill in the object data on a row-by-row basis, using of course, specific Object Data pertinent to that row and only that row.

The empty tables you have so efficiently created are not filled in because this process only creates the 'Table' such that it's a placeholder waiting for OD to be filled in. If you have around 30 entities, filling in the OD row-by-row is feasible, but if you have 1000 or more, then it's not.

Think about it. How is Map3D  suppose to know what specific record gets this OD and not that OD? What is the mechanism to prevent Map3D from flip-flopping the OD between two separate entities? There must be a way in your procedure to tie-in a specific row in your Excel sheet with a specific row in your shapefile Table and once tied-in, how is the data copied from the Excel sheet over to the shapefile Table. This method, the one you have chosen, is inappropriate for your particular task.

A more appropriate solution would be a JOIN.

[Anonymous]

Thank you for your reply, this looks like it has good possibilities for my project.  I've looked up JOIN in the Help and it looks like the first thing I will have to do is set up an ODBC connection.  I looked that up but the documentation was thin for someone who's never done that before but I'll Google it and see if I can figure it out.

Is that the way to connect to Excel before you can perform JOIN or am I on the wrong track?

Frank

[ChicagoLooper]

Yes, you're correct.Set up that ODBC connection.

You'll need to download the ODBC drivers from Microsoft and install them. They are not easy to install, especially with security software that looks at everything you download, what site you're downloading from and is apprehensive about giving you permission to download something that may infect the entire network! When my workstation is upgraded, I always have IT install them for me. Do you have an IT department? If you do, have them earn their $S$ and put in a ticket informing them you need it now and can use their expertise installing it.

There's more than one driver. The driver you download and install will depend on your operating system, your Microsoft Access/Excel version, and your AutoCad version. Knowing your version and bitness is a must. Guess wrong and you may download and install the wrong driver. When IT installs it for me, they always have issues with network security software but are able to resolve them. How they resolve those issues is over my head, way over. That's why I never do it myself. You can download the driver >>HERE<<

This driver will work on both Access and Excel.

----------------------------------------------------------------------------------------------

On Windows 10, the properly installed driver will appear in Apps section. (This driver will allow an ODBC with both Access and Excel.)

[Anonymous]

I'm kind of on my own with this project so I'll have to figure out the download and install it myself.

Thanks,

Frank

[Anonymous]

OK, so this is where I ended up.  As far as being able to link the Excel file, it appears that since I have 32 bit Excel on a 64 bit computer the ODBC setup won't work.  When I get back to my office in a week or two I will have to revisit this setup.

I took my Excel file to a free program, QGIS and used their Create Layer from .csv  function to turn the file into a layer, then used their Save Layer as function to save it as a SHP file.  I then connected to this new SHP file in Map and performed the JOIN and it worked just like you indicated.  The new columns are there with the data.

I now have two questions:

1-If I'm able to connect to the Exel file via ODBC in the future, will any updates to the Excel file show up in the JOIN or will that need to be disconnected and re-connected?

2-Is there a simple way to turn this csv data into an SHP file in Map?  I'm familiar with the PNEZD (Thanks Looper for the earlier post) but it is limited in the number of columns that you can import.  This method that I used, while requiring a third party application, is quick, straight forward and very simple.

Is it OK to reference other software in this blog?  Hope I didn't cross any line there.

Thanks again for the help,

Frank

[ChicagoLooper]

If you have 64-bit Autocad, you can install the driver even if you're running 32-bit office. Yes, it's confusing, and this is why I don't like installing these drivers. Confusing? Yes. Did I mention I don't like installing these drivers? No matter the combination of bitness in Windows, Autocad and MS Office, Microsoft offers a driver and you can download it. You just have to recognize it when you see it. (And that's just downloading it, you still have to install it.)

You can read about it >>HERE<<.

[Anonymous]

Thanks for your reply.  I've got so much to work on the next couple of weeks that this will most likely have to wait until I'm back in the office.

Best regards,

Frank