topic Re: Export geodatabase data to excel in Geodatabase Questions

Export geodatabase data to excel

JohnMellor__GISP — Mon, 08 Aug 2016 17:51:45 GMT

Is there a way to export, to excel, a list of all feature dataset and feature classes? Essentially everything that is in the geodatabase. I can export to XML, but how would I get it to excel? And, how could I tell where there feature classes or tables that where installed when the LGIM was installed, but do not contain any data?

Re: Export geodatabase data to excel

JakeSkinner — Sun, 12 Dec 2021 09:25:03 GMT

Hi John,

You can print out this information using python. Below is an example. You could add on to this if you want to write it to an excel spreadsheet. To do this, you could use the XlsxWriter module.

import arcpy
from arcpy import env
env.workspace = r"D:\Temp\Python\Redlands.gdb"

for dataset in arcpy.ListDatasets("*"):
    print dataset
    for fc in arcpy.ListFeatureClasses("*", "", dataset):
        count = arcpy.GetCount_management(fc).getOutput(0)
        print "\t" + fc + ": " + str(count)

for fc in arcpy.ListFeatureClasses("*"):
        count = arcpy.GetCount_management(fc).getOutput(0)
        print fc + ": " + str(count)

Example of output written from above script:

Re: Export geodatabase data to excel

JohnMellor__GISP — Mon, 08 Aug 2016 20:22:40 GMT

Ok, thanks Jake, this looks like what I need.

Re: Export geodatabase data to excel

MikeCusi — Tue, 09 Aug 2016 05:11:07 GMT

You can also try x-ray for arccatalog.

Re: Export geodatabase data to excel

JohnMellor__GISP — Tue, 09 Aug 2016 13:03:31 GMT

I've used that XRay before; but it didn't seem to behave the way I needed it. How would you suggest I use XRay to get to my result?

Re: Export geodatabase data to excel

JenniferMcCall4 — Tue, 09 Aug 2016 13:09:58 GMT

XRay is what I use as well. It provides a very detailed output of all feature classes, subtypes, domains, feature datasets, and their relationships. It's very handy and is quick to use.

Re: Export geodatabase data to excel

JenniferMcCall4 — Tue, 09 Aug 2016 13:14:16 GMT

Hi John,

This is the way I use XRay:

Select the geodatabase you want to export the information for and hit the button highlighted below.

I find the GIS Datasets and GIS Domains output the most useful.

Re: Export geodatabase data to excel

RebeccaStrauch__GISP — Sun, 12 Dec 2021 09:25:06 GMT

If those suggestions don't get what you are aiming to get, another option is my Python addin for data inventory and “broken-link” repair. ....the second button will inventory everything, and output a .txt, .csv, and .xls with a date/time stamp in the file names, so you can run and compare. I don't have a record count, but may be something I will add. I'm actually trying to get an addin for inventorying items in a number of ways, including what's within mxd's (in a goal to know what services I need to shutdown to update a fgdb).

Anyway, the .xls output for the inventory button looks like

purposely left simple.

I think I've made a few changes since I posted the addin, so he is some code that should be able to run as a standalone....just need to change the workspace you want to inventory (need write access to same folder for the output) (I can upload the .py if this doesn't work) Even this was last worked on a while ago, so could be cleaned up a bit.

'''
---------------------------------------------------------------------------
Tool:    FCInventoryReport 
Toolbox: CheckAndFixLinks.tbx
Script:  2_InventoryFCs.py
Purpose: 
   This script will walk thru all the feature classes within a folder 
   and create a text report, comma-delimted and an Excel .xls file.
   Include shapes, coverages, rasters, tables, connections, and FGDB

   Column names:  FType    FCname    FullPath
-------------------------------------------------------------------------------
 Author:  Rebecca Strauch - ADFG-DWC-GIS
 Created on: 4/10/2013 
   last modification: August 10, 2015

 Description: To create list of the features classes within a folder, including
   coverages (pts, poly, arc, anno), shapes, grids and FGDB data. Outputs list
   to a text report file (not much formatting) in the folder being scanned named
     FCInventoryYYYYMMDD_HHMM.txt
   with the date and time as part of the name. This should always create
   a new file, unless you run it twice within same minute.  

   Must have write permissions on the output folder in question.

   Known issue (with built-in workaround): for some reason some, but not  
     all (ArcInfo) grids want to duplicate the folder name before the 
     describe. I'm now checking to make sure it exists, if not, I am removing
     the duplicate portion, and letting it run.  Seems to work.
------------------------------------------------------------------------------
 Arguments:  
   [0] theWorkspace: Folder/directory to search (walk thru, includes subfolders)
   [1] outFile: output base filename, default GDBList, script appends YYYYMMDD_HHMM

 Updates:          
---------------------------------------------------------------------------
'''
# Import modules
import arcpy
import os
from _miscUtils import *
from _gpdecorators import *

def myMsgs(message):
  arcpy.AddMessage(message)
  print(message)


# catch_errors decorator must preceed a function using the @ notation.
@catch_errors
def main():
  """
  Main function to create text file report of all Feature Classes in folder
  """
  #setup environment
  arcpy.env.overwriteOutput = True

  # Script arguments...    
  """ If running as standalone, hardcode theWorkspace and outFile  """
  theWorkspace = arcpy.GetParameterAsText(0)
  if not theWorkspace:
    theWorkspace = r"C:\__temp" # r"D:\_dataTest"    

  outFile = arcpy.GetParameterAsText(1)
  if not outFile:
    outFile = "FCInventory" 
  # Create new output name name tagged with YYYYMMDD_HHMM
  fileDateTime = curFileDateTime()
  currentDate = curDate()

  # Create new output name tagged with YYYYMMDD_HHMM
  outfileTXT = os.path.join(theWorkspace, outFile) + fileDateTime + ".txt" #theWorkspace + "\FCInventory" + fileDateTime + ".txt"
  outFileCSV = os.path.join(theWorkspace, outFile) + fileDateTime + ".csv"  #theWorkspace + "\FCInventory" + fileDateTime + ".csv"
  outFileXLS = os.path.join(theWorkspace, outFile) + fileDateTime + ".xls"
  arcpy.AddMessage(theWorkspace + ", " + outfileTXT)
  reportFile = open(outfileTXT, 'w')
  csvFile = open(outFileCSV, 'w')
  arcpy.AddMessage(  "File {0} is open? {1}".format(outfileTXT, str(not reportFile.closed)))
  arcpy.AddMessage(  "File {0} is open? {1}".format(str(outFileCSV), str(not csvFile.closed)))
  #arcpy.AddMessage(  "File " + str(csvFile) + " is closed?  " + str(csvFile.closed))     
  arcpy.AddMessage("Writing the report to: " + outfileTXT + " and " + outFileCSV)

  outText = "List of all GIS data in " + theWorkspace + " on " + currentDate + '\n'
  outText += "  Includes coverages (pts, poly, arc, anno), shapes, and FGDB data." + '\n'
  outText += "-----------------------------------------------------" + '\n'

  reportFile.write(outText)
  csvFile.write("FType, FCname, FullPath\n")

  def inventory_data(workspace, datatypes):
    for path, path_names, data_names in arcpy.da.Walk(
      workspace, datatype=datatypes):
      if "tic" in data_names:
        data_names.remove('tic')
      for data_name in data_names:
        fcName = os.path.join(path, data_name)
        #arcpy.AddMessage("Show for debug: " + fcName)
        if not arcpy.Exists(fcName):
          # workaround for raster folder name duplicating
          fcName = os.path.dirname(fcName)
        desc = arcpy.Describe(fcName)
        #arcpy.AddMessage("debug, desc it to me: " + desc.dataType)
        yield [path, data_name, desc.dataType] #, desc]

  i = 0
  for feature_class in inventory_data(theWorkspace, "FeatureClass"):
    """ last modified data not working for gdb or FC in fgdb ..."""
    #lastMod = time.strftime('%m/%d/%Y %H:%M', time.localtime(os.path.getmtime(feature_class[0])))
    if i == 0:
      #arcpy.AddMessage("{0}  modified: {1}".format(feature_class[0], lastMod)) 
      arcpy.AddMessage("{0}".format(feature_class[0]))
      outText = ' ' + feature_class[0] + '\n'
      reportFile.write(outText)
      path0 = feature_class[0]
      i =+ 1
    elif not path0 == feature_class[0]:
      #arcpy.AddMessage("{0}  modified: {1}".format(feature_class[0], lastMod)) 
      arcpy.AddMessage("{0}".format(feature_class[0]))
      outText = ' ' + feature_class[0] + '\n'
      reportFile.write(outText)
      i = 0
    if feature_class[2] == "ShapeFile":
      shpfile = arcpy.os.path.join(feature_class[0], feature_class[1])
      lastMod = time.strftime('%m/%d/%Y %H:%M', time.localtime(os.path.getmtime(shpfile)))
      arcpy.AddMessage("       {0}: {1} modified: {2}".format(feature_class[2], feature_class[1], lastMod))
      outText = ("       {0}: {1} modified: {2}\n".format(feature_class[2], feature_class[1], lastMod))
    else:
      arcpy.AddMessage("       {0}: {1}".format(feature_class[2], feature_class[1]))
      outText = ("       {0}: {1}\n".format(feature_class[2], feature_class[1]))
    reportFile.write(outText)
    csvFile.write("{},{}, {}\n".format(feature_class[2], feature_class[1], feature_class[0]))
    

  reportFile.close()
  csvFile.close()
  arcpy.AddMessage(  "File {0} is closed? {1}".format(outfileTXT, str(reportFile.closed)))
  arcpy.AddMessage(  "File {0} is closed? {1}".format(outFileCSV, str(csvFile.closed)))

  # Creates Excel .xls file from the .csv ....easier to edit (ver 1)
  arcpy.TableToExcel_conversion(outFileCSV, outFileXLS) 

  arcpy.AddMessage('!!! Success !!!  ')

# End main function

if __name__ == '__main__':
  main()

EDIT: uploaded my _miscUtils and _gpdecorators in case that throws people off. The code above can be modified to not use them...but I use them for convenience, and often forget to remove before posting other code. Just drop the two .py in the same folder as the script above.

Re: Export geodatabase data to excel

JohnMellor__GISP — Tue, 09 Aug 2016 16:57:33 GMT

Thank you Mike, Jennifer & Rebecca; all good options, the only thing about XRay is all the different worksheets; and the only thing about Rebecca's inventory is no count. Jake has the best match to what I am looking for in a solution, however, I'm not getting the script to run properly. I am just starting with Python and am thinking I don't have the workspace set correctly.

Re: Export geodatabase data to excel

JohnMellor__GISP — Tue, 09 Aug 2016 17:06:08 GMT

Jake, I think you are pointing to a file geodatabase, where I'm trying to point to my sde geodatabase. Any suggestions?

Re: Export geodatabase data to excel

JakeSkinner — Tue, 09 Aug 2016 17:10:01 GMT

Do you a connection to the sde geodatabase under 'Database Connections'? If so, try the following:

env.workspace = r"Database Connections\<database name>.sde"

You will want to replace <database name> with what you have under 'Database Connections' in your Catalog window.

Re: Export geodatabase data to excel

George_Thompson — Wed, 10 Aug 2016 13:49:46 GMT

John - I tested Jake's Python on an Oracle enterprise geodatabase and it worked by just changing out the .sde connection information that Jake mentioned.

Re: Export geodatabase data to excel

ERIASGroup — Wed, 01 Aug 2018 00:30:00 GMT

Hi. This appears to be exactly what I need right now. I do not know Python properly but when I open the Python window in ArcCatalog, under the 'Geoprocessing' menu, I add this code to the blank window (after the >>>) then changed the line at env.workspace to point to my gdb but then what? I hit enter (to execute) but nothing happens? How do I find if it did anything or see the results as a list (as shown above)? I think I just don't know how to run Python. Any tips please would be great! Thanks.

Re: Export geodatabase data to excel

ERIASGroup — Wed, 01 Aug 2018 00:40:53 GMT

Answering my own question: Oh, put the cursor on the last line with three dots and again hit enter to execute. It then printed the results below the script. Great! Now to get it to Excel...