Export geodatabase data to excel

9475
13
Jump to solution
08-08-2016 10:51 AM
JohnMellor__GISP
New Contributor III

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?

Tags (1)
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor

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:

Screen1.PNG

View solution in original post

13 Replies
JakeSkinner
Esri Esteemed Contributor

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:

Screen1.PNG

ERIASGroup
New Contributor III

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.

0 Kudos
ERIASGroup
New Contributor III

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... 

0 Kudos
JohnMellor__GISP
New Contributor III

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

0 Kudos
MikeCusi
Occasional Contributor II

You can also try x-ray for arccatalog.

JohnMellor__GISP
New Contributor III

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?

0 Kudos
JenniferMcCall4
Occasional Contributor III

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.

JenniferMcCall4
Occasional Contributor III

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.

0 Kudos
RebeccaStrauch__GISP
MVP Emeritus

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.