Excel To Table (Conversion)

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

• The output field data type is based on the values and cell formatting found within the input column. Output field data types include float, text, and date. If an input column contains more than one type of data or formatting, the output field will be of type text.

Syntax

ExcelToTable(Input_Excel_File, Output_Table, {Sheet})

Parameter	Explanation	Data 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.	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

Current Workspace, Scratch Workspace, Output CONFIG Keyword

Licensing information

• Basic: Yes
• Standard: Yes
• Advanced: Yes