Help

Excel To Table

Summary

Converts Microsoft Excel files into a table.

Usage

  • Excel To Table supports Excel Workbooks (.xlsx) and Microsoft Excel 5.0/95 Workbook (.xls) formats as input.

  • This tool assumes vertically ordered tabular data. The first row is taken as the field names for the output table. These field names may be renamed during the validation process to prevent any errors or duplicate names. Empty columns between data are maintained and given a generic field name (field_4, for example).

  • Each field should contain a uniform data type. Supported data types include float, text, date, and Boolean. However, Boolean fields are stored as short integer.

Syntax

ExcelToTable_conversion (Input_Excel_File, Output_Table, {Sheet})
ParameterExplanationData Type
Input_Excel_File

The Microsoft Excel file to convert.

File
Output_Table

The output table.

Table
Sheet
(Optional)

The name of the particular sheet within the Excel file to import. If unspecified, the first sheet in the workbook will be used by default.

String

Code sample

ExcelToTable example (Python window)

The following Python window script demonstrates how to use the ExcelToTable function in immediate mode.

import arcpy
arcpy.env.workspace = "c:/data"
arcpy.ExcelToTable_conversion("data.xls", "outgdb.gdb", "Sheet1")
ExcelToTable example 2 (stand-alone script)

Import each sheet in a Microsoft Excel file into individual tables in a geodatabase.

import os
import xlrd
import arcpy

def importallsheets(in_excel, out_gdb):
    workbook = xlrd.open_workbook(in_excel)
    sheets = [sheet.name for sheet in workbook.sheets()]

    print('{} sheets found: {}'.format(len(sheets), ','.join(sheets)))
    for sheet in sheets:
        # The out_table is based on the input excel file name
        # a underscore (_) separator followed by the sheet name
        out_table = os.path.join(
            out_gdb,
            arcpy.ValidateTableName(
                "{0}_{1}".format(os.path.basename(in_excel), sheet),
                out_gdb))

        print('Converting {} to {}'.format(sheet, out_table))

        # Perform the conversion
        arcpy.ExcelToTable_conversion(in_excel, out_table, sheet)

if __name__ == '__main__':
    importallsheets('c:/data/data.xls',
                    'c:/data/outgdb.gdb')

Environments

Licensing information

  • ArcGIS for Desktop Basic: Yes
  • ArcGIS for Desktop Standard: Yes
  • ArcGIS for Desktop Advanced: Yes

Related topics