Friday, September 13, 2013

Excel to Table (10.2) Tool

New at ArcGIS 10.2 is the Excel to Table tool.. This tool is found under the conversion toolbox, and it a quick way to easily convert your Excel Workbooks (.xlsx files) and MS Excel 5.0/95 Workbook (.xls files) into geodatabase tables.
  • Supports xlsx and xls file extensions
  • First row is considered the field names, and can be renamed so a valid table is generated
  • It assumes that each field contains uniform data types and the data is not mixed
ExcelToTable() takes the following inputs:
  1. Input_Excel_File - MS Office Excel file path 
  2. Output_table - export table name and path
  3. Sheet (optional) - Name of the sheet to export.  If none is provide the 1st sheet will be exported
Sample:

import arcpy
from arcpy import env
if __name__ == '__main__':
    env.overwriteOutput = True
    xls_file = r"c:\temp\somedata.xls"  # supports xlsx and xls
    dest_gdb = env.scratchGDB
    arcpy.ExcelToTable_conversion(xls_file,
                        os.path.join(dest_gdb, "sheet"))

Pretty easy.  The biggest advantage I see with this tool is if you are in an environment where you cannot install 3rd party modules like xlrd.

Enjoy

2 comments:

Hornbydd said...

Hi Andrew, Interesting new tool. I do not have 10.2 yet but I was wondering what defines the type the field is when you import from Excel? Say you had a column in Excel that is all numbers but you actually want them as text is the tool intelligent enough to read the cell type rather than the value?

Andrew said...

Hey thanks for your comment. So I created two columns each with the value of 1234, but one was declared a TEXT column and the other a Number column in Excel. When I exported the data to a table, the Excel to Table tool converted the TEXT field into a TEXT field in my FGDB and the other column was assigned the value of Long Integer.

Hope this helps.