Export geodatabase data to excel

5102
13
Jump to solution
08-08-2016 10:51 AM
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
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
Highlighted
New Contributor III

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

0 Kudos
Highlighted
Occasional Contributor II

You can also try x-ray for arccatalog.

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

Highlighted
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
Highlighted
MVP Esteemed Contributor

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.