Excel To Table (Conversion)—ArcGIS Pro

Summary

Converts Microsoft Excel files into a table.

Usage

Excel Workbooks (.xlsx) and Excel 5.0/95 Workbook (.xls) formats are supported as input.
The output field data type is based on the values and cell formatting in the input column. Output field data types include double, long integer, text, and date. If an input column contains more than one type of data or formatting, the output field will be of type text.
A value of "#N/A" in an input Excel cell will be converted to null. If the output table format (such as a dBASE table) does not support null, another value (0 for integer fields or an empty string for text fields) will be used.

Parameters

Label	Explanation	Data Type
Input Excel File	The Excel file that will be converted.	File
Output Table	The output table.	Table
Sheet Or Named Range (Optional)	The name of the particular sheet or named range in the Excel file that will be imported. If unspecified, the first sheet in the workbook will be used. Named ranges are only supported for .xlsx files. Only named ranges with a single range are supported (for example, Sheet!A1:B5); named ranges with multiple ranges are not (for example, Sheet!A1:B5,Sheet!D1:D5).	String
Row To Use As Field Names (Optional)	The row in the Excel sheet that contains values that will be used as field names. The default value is 1. The row specified will be skipped when converting records to the output table. If the Sheet Or Named Range parameter value is a named range, this parameter's value will be set to the minimum row of the named range. The value will also be limited to the minimum and maximum row number of the named range. To avoid using any row's values as field names, set this parameter to 0, which will name the output fields using the column letter (for example, COL_A, COL_B, COL_C). If a cell in a particular column is empty, the output field name will be based on the column letter. For example, if the input has three columns, and the row contains "city", "", and "country" in columns A, B, and C, respectively, the output table's field names will be city, COL_B, and country.	Long
Cell Range (Optional)	The cell range. This parameter is disabled when a named ranged is selected. If a cell range is not specified, the tool will use the start and end cells of the specified sheet. A cell is the intersection of a row and a column. Columns are identified by letters (A, B, C, D), and rows are identified by numbers (1, 2, 3, 4). Each cell has an address based on its column and row. For example, the cell B9 is the intersection of column B and row 9. A cell range defines a rectangle using the upper left cell and lower right cell, separated by a colon (:). Cell ranges are inclusive, so a range of A2:C10 will include all values in columns A through C and all values in rows 2 through 10. The output field names are derived from cell values in row 1, regardless of the rows specified in the cell range. For example, if the cell range specified is B2:D10, the field names will be based on the values in cells B1, C1, and D1. The following are examples of valid cell ranges: A2:C10—The values in columns A through C, from row 2 through 10 B3:B40—The values in column B, from row 3 through 40 D5:X5—The values in columns D through X, for row 5 E200:ALM20000—The values in columns E through ALM (1000th column), from row 200 through 20000 The following are examples of invalid cell ranges: A20:C10—The first cell cannot be lower (have a larger row number) than the second cell. Z3:B5—The second cell cannot be to the left (have a smaller column letter) of the first cell. A5:G—Both cells must have a valid cell identifier: a letter and a number. This parameter is only active when the Sheet Or Named Range parameter value is a sheet.	String

arcpy.conversion.ExcelToTable(Input_Excel_File, Output_Table, {Sheet}, {field_names_row}, {cell_range})

Name	Explanation	Data Type
Input_Excel_File	The Excel file that will be converted.	File
Output_Table	The output table.	Table
Sheet (Optional)	The name of the particular sheet or named range in the Excel file that will be imported. If unspecified, the first sheet in the workbook will be used. Named ranges are only supported for .xlsx files. Only named ranges with a single range are supported (for example, Sheet!A1:B5); named ranges with multiple ranges are not (for example, Sheet!A1:B5,Sheet!D1:D5).	String
field_names_row (Optional)	The row in the Excel sheet that contains values that will be used as field names. The default value is 1. The row specified will be skipped when converting records to the output table. If the Sheet parameter value is a named range, this parameter's value will be set to the minimum row of the named range. The value will also be limited to the minimum and maximum row number of the named range. To avoid using any row's values as field names, set this parameter to 0, which will name the output fields using the column letter (for example, COL_A, COL_B, COL_C). If a cell in a particular column is empty, the output field name will be based on the column letter. For example, if the input has three columns, and the row contains "city", "", and "country" in columns A, B, and C, respectively, the output table's field names will be city, COL_B, and country.	Long
cell_range (Optional)	The cell range. This parameter is disabled when a named ranged is selected. If a cell range is not specified, the tool will use the start and end cells of the specified sheet. A cell is the intersection of a row and a column. Columns are identified by letters (A, B, C, D), and rows are identified by numbers (1, 2, 3, 4). Each cell has an address based on its column and row. For example, the cell B9 is the intersection of column B and row 9. A cell range defines a rectangle using the upper left cell and lower right cell, separated by a colon (:). Cell ranges are inclusive, so a range of A2:C10 will include all values in columns A through C and all values in rows 2 through 10. The output field names are derived from cell values in row 1, regardless of the rows specified in the cell range. For example, if the cell range specified is B2:D10, the field names will be based on the values in cells B1, C1, and D1. The following are examples of valid cell ranges: A2:C10—The values in columns A through C, from row 2 through 10 B3:B40—The values in column B, from row 3 through 40 D5:X5—The values in columns D through X, for row 5 E200:ALM20000—The values in columns E through ALM (1000th column), from row 200 through 20000 The following are examples of invalid cell ranges: A20:C10—The first cell cannot be lower (have a larger row number) than the second cell. Z3:B5—The second cell cannot be to the left (have a smaller column letter) of the first cell. A5:G—Both cells must have a valid cell identifier: a letter and a number. This parameter is only enabled when the Sheet parameter value is a sheet.	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.conversion.ExcelToTable("data.xls", "outgdb.gdb", "Sheet1")

ExcelToTable example 2 (stand-alone script)

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

import os
import xlrd
import openpyxl
import arcpy

def importallsheets(in_excel, out_gdb):
    if in_excel.endswith(".xlsx"):
        workbook = openpyxl.load_workbook(in_excel)
        sheets = workbook.sheetnames
    elif in_excel.endswith(".xls"):
        workbook = xlrd.open_workbook(in_excel)
        sheets = [sheet.name for sheet in workbook.sheets()]
    else:
        print("An Excel Workbook of format .xls or .xlsx is required.")
        return

    print('{} sheets found: {}'.format(len(sheets), ','.join(sheets)))
    for sheet in sheets:
        # The out_table is based on the input Excel file name
        # an 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.conversion.ExcelToTable(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