Excel To Table (Conversion)

Summary

Converts Microsoft Excel files into a table.

Usage

• Excel Workbooks (.xlsx) and Microsoft 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.

Syntax

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

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 in the Excel file to import. If unspecified, the first sheet in the workbook will be used.	String
field_names_row (Optional)	The row in the Excel sheet that contains values to be used as field names. The default value is 1. The row specified will be skipped when converting records to the output table. 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 to include. 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 column 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 rows 3 through 40. D5:X5—The values in columns D through X, for row 5. E200:ALM20000—The values in column 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 right (have a larger column letter) of the second cell. A5:G—Both cells must have a valid cell identifier: a letter and a number.	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 from 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
        # 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.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