| Label | Explanation | Data Type |
Input Excel File
| The 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 |
Row To Use As Field Names
(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 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:
The following are examples of invalid cell ranges:
| String |
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
arcpy.conversion.ExcelToTable(Input_Excel_File, Output_Table, {Sheet}, {field_names_row}, {cell_range})| Name | Explanation | Data Type |
Input_Excel_File | The 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 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:
The following are examples of invalid cell ranges:
| String |
Code sample
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")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')