Jump to content

 
Photo

xls spreadsheet to shapefile conversion

- - - - -

  • Please log in to reply
11 replies to this topic

#1
fmark

fmark

    Newbie

  • New Member
  • Pip
  • 1 posts
  • Australia

I have an excel spreadsheet I want to convert to an ESRI shapefile programmatically. It contains X and Y coordinates in two columns, as well as a variety of attribute data in other columns. The spreadsheet is in excel 97 format (i.e. not .xlsx).

I would like to be able to convert this to a point geometry shapefile, with each row's x,y pair representing a point. Ideally, I would like to have a third column specifying the coordinate system of the x,y coordinate pair, and have the excel file contain heterogenous coordinate systems.

How can I convert this excel spreadsheet (.xls) to a shapefile programmatically? Preferably in Python, but other implementations will be accepted.

#2
bjMustapha

bjMustapha

    Newbie

  • New Member
  • Pip
  • 2 posts
  • Morocco

hallo!

i guess you can do that without programming, you can use the command : Add XY data, it's preferable to convert your xls file to a .dbf..

Mustapha,

bjmustapha@gmail.com

#3
dsl

dsl

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 225 posts
  • Gender:Male
  • Location:Denver
  • United States

Since version 9.2 of ArcGIS you can load excel spreadsheets without a problem. Which is nice, because in Excel 2007 you can't save out to a DBF file anymore.

If you don't have ArcGIS, the GDAL library will give you the tools you need to write out a shapefile. Here are some excellent tutorials on getting started with GDAL and python. Here is an example on how to read an excel file.

Hope that helps.

Cheers,
David

#4
Charles Syrett

Charles Syrett

    Ultimate Contributor

  • Validated Member
  • PipPipPipPipPipPip
  • 537 posts
  • Canada

For folks like me who avoid programming like the plague, you can just save the Excel file as a .csv, and then open that in something like Global Mapper or Manifold (and maybe Arc -- haven't checked that). For that matter, perhaps a GPS file converter would read it.

Charles Syrett
Map Graphics
http://www.mapgraphics.com

#5
gregory

gregory

    Key Contributor

  • Validated Member
  • PipPipPip
  • 96 posts
  • Gender:Male
  • Location:Cracow
  • Poland

For folks like me who avoid programming like the plague, you can just save the Excel file as a .csv, and then open that in something like Global Mapper or Manifold (and maybe Arc -- haven't checked that). For that matter, perhaps a GPS file converter would read it.


With manifold its even simpler. It's open xls natively. Not necessarily to safe as .csv in excel. What more... then you just have copy and paste as drawing
http://www.manifold....coded_table.htm
Don't know if its going to help you and you really need to program it otherwise consider manifold. Can do a lot of things really simply.

#6
Kathi

Kathi

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 186 posts
  • Gender:Female
  • Location:Olten, Switzerland
  • Interests:geology, GIS, cartography, famlily, reading, cooking, travelling, gardening
  • Switzerland

I have an excel spreadsheet I want to convert to an ESRI shapefile programmatically. It contains X and Y coordinates in two columns, as well as a variety of attribute data in other columns. The spreadsheet is in excel 97 format (i.e. not .xlsx).

I would like to be able to convert this to a point geometry shapefile, with each row's x,y pair representing a point. Ideally, I would like to have a third column specifying the coordinate system of the x,y coordinate pair, and have the excel file contain heterogenous coordinate systems.

How can I convert this excel spreadsheet (.xls) to a shapefile programmatically? Preferably in Python, but other implementations will be accepted.



I was about to suggest you look at the spreadsheet in ArcCatalog, right-click on it and choose "Create Shapefile from xy-Data". BUT... as far as I know a shapefile can only hold data of one single coordinate system. So I guess you would have to sort your data by coordinate system and then export each set into an individual shapefile.

I'm no expert on programming (Python or whatever), but I would do this task in FME. Since ArcGIS' ModelBuilder is related, I'd expect it could probably do this task as well.

Hope this helps.
Cheers,

Kathi

#7
lucy

lucy

    Newbie

  • New Member
  • Pip
  • 1 posts
  • No Country Selected

Since version 9.2 of ArcGIS you can load excel spreadsheets without a problem. Which is nice, because in Excel 2007 you can't save out to a DBF file anymore.

If you don't have ArcGIS, the GDAL library will give you the tools you need to write out a shapefile. Here are some excellent tutorials on getting started with GDAL and python. Here is an example on how to read an excel file.

Hope that helps.

Cheers,
David


Hi

I have arcgis 9.3 and an excel file to convert to shapefile. two columns are xy data and then the rest are results from analysis and sample names etc. not sure where i click to get the excel file loaded up. tried the "add xy data" under Tools but when I clicked on my results sheet it wouldn't recognise any of the columns in my excel file. is there something i haven't done to the excel table?

#8
James Hines

James Hines

    James Anthony Hines

  • Validated Member
  • PipPipPipPipPipPip
  • 537 posts
  • Gender:Male
  • Location:Centreville, Nova Scotia
  • Interests:Cartography, Philosophy, Politics, Psychology, Economics, Occultism, Spiritualism
  • Canada

Since version 9.2 of ArcGIS you can load excel spreadsheets without a problem. Which is nice, because in Excel 2007 you can't save out to a DBF file anymore.

If you don't have ArcGIS, the GDAL library will give you the tools you need to write out a shapefile. Here are some excellent tutorials on getting started with GDAL and python. Here is an example on how to read an excel file.

Hope that helps.

Cheers,
David


Hi

I have arcgis 9.3 and an excel file to convert to shapefile. two columns are xy data and then the rest are results from analysis and sample names etc. not sure where i click to get the excel file loaded up. tried the "add xy data" under Tools but when I clicked on my results sheet it wouldn't recognise any of the columns in my excel file. is there something i haven't done to the excel table?


Can you post a link for that excel sheet?

"There is much beauty that we fail to see through our own eyes teeming with life forms that give us that perception of our reality.  Leaves on the trees blowing gently in the wind, or scarily, the waves pounding through high surf, or lightly on a warm summer’s day; that opportunity to sit or swim in the water on a white beach.   That comfort to shout, “The universal conscious do you hear me?  I am alive, guide me dear logos towards the path of rightnesses.”  Earned what has been kept, no longer to be absorbed into a life filled with cold damn winds and  that stubborn fog clouding  my vision with nothing but darkness."


#9
19° norte

19° norte

    Contributor

  • Validated Member
  • PipPip
  • 14 posts
  • Gender:Male
  • Location:near Mexico City
  • Mexico

tried the "add xy data" under Tools but when I clicked on my results sheet it wouldn't recognise any of the columns in my excel file. is there something i haven't done to the excel table?

Probably that's a field definition issue. The columns with the x and y data are in a non-numerical format (e.g. character). The easiest way to eliminate the problem is to create two new fields in ArcGIS in format double and via the field calculator and the function val() convert the string data into numerical.
Roland W. Hardt
19° | norte
http://www.19norte.com.mx

#10
Mark Boucher

Mark Boucher

    Newbie

  • New Member
  • Pip
  • 9 posts
  • Gender:Male
  • Location:Martinez, California
  • Interests:Hydrology <br />Hydraulics<br />Watershed Analysis
  • United States

I tried this with xls 97 and it worked.

Using Microsoft Access import the xls file worksheet. It becomes a database table.

Export that table to a dbf.

In Catalog, right click on the dbf and create "Create Feature Class ... from XY"
or
Add the dbf table to your mxd and use the "Tools > Add XY data" This data maybe temporary and you may need to export it to a shp file.

Hope this helps.
Mark
"We may not be big, but we're slow."

#11
frax

frax

    Hall of Fame

  • Associate Admin
  • PipPipPipPipPipPipPip
  • 2,303 posts
  • Gender:Male
  • Location:Stockholm, Sweden
  • Interests:music, hiking, friends, nature, photography, traveling. and maps!
  • Sweden

Note that you can use an Access table directly, if it is mdb. If it is Access 2007 format (accdb) you can access that through ODBC.
Hugo Ahlenius
Nordpil - custom maps and GIS
http://nordpil.com/
Twitter

#12
cartotech81

cartotech81

    Newbie

  • New Member
  • Pip
  • 4 posts
  • United States

Note that you can use an Access table directly, if it is mdb. If it is Access 2007 format (accdb) you can access that through ODBC.


If in 9.3.1 click Tools --> Add X, Y. Load your .xls file specify which columns are your X,Y and choose a coordinate system. If you want to do this in Python you can create a model, and hen export to python, using make xy event layer tool.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->