12

I'm writing a python code to convert X,Y points in excel to shapefile. In this process i have toread xy points from shhet1. 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 a code as follows.. 

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

But its not working.

Improve this question
4
  • Try r"E:\" or "E:/" – Aaron♦ Jul 2 '13 at 14:08
  • ya i tried already. – Satya Chandra Jul 2 '13 at 14:12
  • import xlrd not working. The error as follows no module named xlrd. – Satya Chandra Jul 3 '13 at 4:19
  • I have found openpyxl to be very useful as well for reading in and manipulating Excel sheets in conjunction with arcpy. – Cindy Jayakumar Jul 3 '13 at 5:25
29

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)
Improve this answer
4
  • k, thanks then how we i can include sheet1 in process. My step is as follow arcpy.MakeXYEventLayer_management(input_table,lat,long,layername,SpatialRef). I have to include that sheet1 into input_table. How i can? – Satya Chandra Jul 2 '13 at 13:52
  • See my edits - you can treat an .xls or xlsx as a workspace. – MappaGnosis Jul 2 '13 at 15:11
  • 6
    +1 for pointing out that an XLS spreadsheet it actually considered a WORKSPACE in ArcGIS, rather than a file. A "sheet" within an XLS file could be considered analogous to a "table" within a geodatabase. – RyanKDalton Jul 2 '13 at 15:37
  • I've found using the format xls=r"E:\123.xls\Sheet1$" to be fairly dodgy - sometimes it works, sometimes it doesn't (I've tried it on two copies of the same workbook - it worked for the other one, the script crashed with "does not exist" on the other). – Cindy Jayakumar Jul 3 '13 at 5:15
6

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})
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.