topic Editing Shapefile databases in MS Excel 97-2003 in Data Management Questions

Editing Shapefile databases in MS Excel 97-2003

DanielBrenner — Wed, 05 Dec 2018 17:02:08 GMT

I do an extensive amount of editing of shapefile databases (.dbf) format for Quality Control with numerous attributes in MS Excel for compatibility with shapefiles in ArcGIS 10.6.x. While I clearly see the need for maintaining a high quality end product (shapefile to be used with other applications) this is also frustrating and is leads to a larger amount of time for achieving my end result.

I have a number of questions for this subject, but one of them is this:

How do I maintain the same order of fields in MS Excel 97-2003 that I have in the .dbf in ArcGIS?

As in:

ArcGIS: a b c d

MS Excel: d b a c

Thank you,

Mr. Excel

Re: Editing Shapefile databases in MS Excel 97-2003

DanPatterson_Retired — Wed, 05 Dec 2018 22:08:14 GMT

"in the day" that was accomplished by adding new fields and calculating values using existing fields in the order you wanted them, then deleting the originals. shortcuts included joining reordered dbf files to the FID and deleting the originals. I am sure you are aware of the tricks.

Even Pro doesn't allow for permanent reordering of fields as of yet.

Just keep a copy of an old excel around or export your important data to csv or other formats while you can

Re: Editing Shapefile databases in MS Excel 97-2003

DanielBrenner — Wed, 05 Dec 2018 22:54:18 GMT

How does exporting a shapefile’s dbf table (with the correct order of fields) to csv answer my question?

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Re: Editing Shapefile databases in MS Excel 97-2003

DanPatterson_Retired — Wed, 05 Dec 2018 23:01:43 GMT

This has been a long standing issue for you

https://community.esri.com/thread/161964

You missed the preceding comments in my post which are salient.

Also, as some may note be aware, dbf is no longer supported in current versions of excel (out of the box) as many have discovered.

Other file formats have a longer shelf-life, such as, but not restricted to,

csv, hdf, netcdf and npy

These file formats also have a wider selection of readers/writers available

EDIT

As for the field reordering, it has to do with the order in the index file in the shapefiles specification.

I haven't tried to edit it to retain order and the links on workarounds are now long buried in the ArcView2.x/3.x archives.

https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf