Reading Excel sheet in ArcPy script

Question

I'm writing Python code to convert X,Y points in Excel to shapefile. In this process I have to read xy points from sheet1.

What is the step i have to include in my process to read data from sheet1 of an excel work book(97-2003)?

I wrote code as follows:

import arcpy  
from arcpy import env  
import Xlrd  
env.workspace="E:\"  
input_table="123.xls\Sheet1" 

but it's not working.

Answer 1

It doesn't work because you haven't called the Xlrd modules to read the Excel spreadsheet. Implement it something like this:

import xlrd
workbook = xlrd.open_workbook('my_workbook.xls')
worksheet = workbook.sheet_by_name('Sheet1')

This will allow you to read an XLS file with Python. However, ArcPy will read XLS without Xlrd. You can consider the Excel workbook to be a workspace containing potentially many tables (worksheets). So you could do something like:

arcpy.env.workspace = r'E:\123.xls'
input_table = 'Sheet1$'

...or cut to the chase with:

arcpy.MakeXYEventLayer_management(r'E:\123.xls\Sheet1$',lat,long,layername,SpatialRef)

Answer 2

You do not mention your ArcGIS for Desktop version, but if it is 10.2 (or later) then you should be able to use the Excel To Table tool which:

Converts Microsoft Office Excel files into a table.

Its syntax is:

ExcelToTable_conversion (Input_Excel_File, Output_Table, {Sheet})