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).
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
| 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 by default. | 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.ExcelToTable_conversion("data.xls", "outgdb.gdb", "Sheet1")
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')