Hot answers tagged ms-excel
15
Here's a simple script that uses the OGR python bindings:
import ogr,csv,sys
shpfile=r'C:\Temp\test.shp' #sys.argv[1]
csvfile=r'C:\Temp\test.csv' #sys.argv[2]
#Open files
csvfile=open(csvfile,'wb')
ds=ogr.Open(shpfile)
lyr=ds.GetLayer()
#Get field names
dfn=lyr.GetLayerDefn()
nfields=dfn.GetFieldCount()
fields=[]
for i in range(nfields):
...
11
Introduction
Because this issue (of discrepancies in standard deviations, variances, or other statistical summaries) comes up periodically, especially when a thoughtful and careful GIS analyst checks their work, I thought it would be good to share the "forensic analysis" of the discrepancy so that readers can carry out similar checks in their own ...
9
I'd recommend using OGR/GDAL, which is part of the GDAL library. OGR supports a virtual format which allows specification via an XML file. If you convert your Excel worksheet into a CSV, you can generate a VRT to access the data.
Assuming you have something like this example.csv:
Lat,Long,Year,Name
34.0,-120.0,2010-05-01,Off Santa Rosa Island
You can ...
9
If you are going to ArcMap 10.1 you could create a python add-in. The add-in gives you access to an "on open" function that will run code when you open the mxd.
The help here explains how to create one and has a sample that adds a base layer to the mxd when opening.
8
Assuming the columns appear in time order, the first row (for example) indicates that total construction through each period went
0, 0+45 = 45, 45+135 = 180, 180+405 = 585, 585+1010 = 1595, ..., 2230+0 = 2230.
Construction was halfway through at 2230/2 = 1115. This occurred during period 4, because at the end of period 3 the total was 585, at the end of ...
8
I've seen this behaviour as well. If Zachary's solutions don't work (which have usually worked for me in the past), the other thing to try is to export the Excel sheet to a DBF and join that instead. I've only done this in ArcGIS, your results may vary depending on what software you're using.
7
For opening and editing, Open Excel 2007 and simply drag the dbf file to it.
To create a new DBF file (http://www.excelforum.com/excel-2007-help/643473-save-as-dbf.html):
In Excel 2007, Go to "file > Save As.." and choose .csv
Now open Access 2007 and Choose import data and select the csv file
The data then loads into a table and from there you can export ...
7
If you convert your shapefile to spatialite, you should be able to do the following:
1) Experiment with SQL to test the output:
ex.
SELECT col1, col2, col3, AsKml(geometry_column)
FROM tab
2) Once you are satisfied with the result, you can export it to CSV format:
http://stackoverflow.com/questions/5776660/export-from-sqlite-to-csv-using-shell-script
...
7
This excellent tutorial explains how to do just that.
Note that if your data is in UTM:
You can follow the same process, but choose the appropriate UTM CRS in
the Coordinate Reference System Selector instead of WGS84.
6
Do you receive the same error by copy/pasting records?
This is from memory, but if you right-click on the gray row indicator box, your menu should have "Copy Selected Records". Then you can paste directly into an Excel spreadsheet.
A caveat: if your selections are in a related table that are a result of a feature selection, you might actually have ...
6
Like whuber says, you have to write out the headers explicitly. I loaded up dbfpy and xlwt in a virtualenv and ran this:
from xlwt import Workbook, easyxf
import dbfpy.dbf
from time import time
def test1():
dbf = dbfpy.dbf.Dbf("pipelines.dbf", readOnly = True)
header_style = easyxf('font: name Arial, bold True, height 200;')
book = Workbook()
...
5
I looked for but could not find a help page on Display XY Data in the web help but the process is simple.
Use Add Data (or drag and drop from Catalog window) to browse for your worksheet (within the spreadsheet) and add it to your map
Right-click to choose and use Display XY Data to add an event layer
Right-click on the event layer just created and use ...
5
Although not from the command line, you could do this within ArcGIS App.
Since 9.3.1, just add the XLS directly into ArcMap.
Make an XY Event Layer from the XLS (i.e. pick the X, Y Columns and set ur spatial reference)
Then export this XY Event Layer to a new .shp
This could easily be automated with a simple python/VBA script or a model in modelbuilder.
...
5
The first thing I would try would be to open the Excel spreadsheet and clean out any text or cell formatting that are in it. If that does not work, export the Excel spreadsheet to a text file, then import the text file into a new Excel spreadsheet. I've had similar issues in the past and both solutions have worked. Also, make sure that the fields you are ...
5
the attributes of a shapefile are stored in an extra dbase-file. for example: mypolygons.shp contains geometries, mypolygons.dbf contains attributes. via an id the attributes are connected to the polygons. so just simple load your dbf file into capable software (e.g libreoffice) , edit attributes and your done
5
I do this using R. There is a package called foreign which enables reading and writing of dbf files.
library(foreign) # load the package
data <- read.dbf('myfile.dbf') # read the dbf as a dataframe
In this case I just wanted to change the rounding of a column pop_den:
data$pop_den <- round(data$pop_den) # trim data (removing significant figures)
...
5
You can control your tabular data with XYtools plugin.
The aim of the xy-tools-plugin is to fill an x- and y-column of a
given attribute table by clicking on a (reference) map. After 'filling
the x and y column' you are able to export the table to a point shape
file. Since version 0.2 you can also open Excel files, and using an
x- and y-column ...
5
GRASS GIS r.out.xyz tool
You can use the r.out.xyz tool in the GRASS toolbox in QGIS. The function exports a raster map as a list of x,y,z values into an ASCII text file, skipping x,y coordinates for raster cells containing a NULL value. For more information, see the r.out.xyz help file. The disadvantage is that you need first to create a GRASS database and ...
4
my problem is that every time and
every way i try to import this data to
a shapefile
Set your map document coordinate system to WGS84 then use the 'Add XY Data' tool to add the excel spreadsheet to the map; choose the same coordinate system and the X and Y fields.
You can add a base map from ArcGIS online for visual reference.
If this does not ...
4
Here is a technical document describing some of the basic VBA calculator functions, with links to some short walk-throughs. And here is some narrative (with examples) on using the Field Calculator and VBA.
Remember that using VBA in the field calculator is only valid for 9.3.1 and not 10 (which uses python and VBScript).
4
A possible solution would be to use postgresql, postgis, pgrouting and osm2pgrouting.
Insert your fixed locations in a postgis database.
Insert the real road network in your database for the area that you need with an import of OSM data using osm2pgrouting.
Optional: find the closest point on your road network from the user defined location.
Use pgrouting ...
4
did you try stretching?
I know I have had this issue with the number of rows; and stretching allowed me to see them all.
I had more rows than would fit on my page.
So I had to copy the object several times and stretch to different sections of my data.
Then place them so they read like columns (or sub-tables).
In fact I just tested it with an sde table ...
4
In a shapefile, numeric zeros are stored as text "0", while empty cells will be stored as empty strings "". However, ArcGIS interprets an empty string as a 0.
Because you cannot do anything about this, you need to use a work-around. A standard method, which is quite resistant to corruption from the GIS (or any other software), is to put an extremely ...
4
You can try the public beta of 'Esri Maps for Office'
"Esri Maps for Office is an add-in for Microsoft Office that brings mapping capabilities into Microsoft Excel and Microsoft PowerPoint. With Esri Maps for Office, you can easily create a geospatial view of your organization's data by creating an interactive map that includes data from Excel and ArcGIS ...
4
If you already have your data in excel make sure of the following
a)Data must be in decimal degrees
b)First row of the file has the name of the field (this is just an example)
then you have to save the file as a csv comma delimited (not msdos or mac). After opening Qgis you have to look for the "add delimited text layer plugin (a blue postit with commas ...
4
If you don't need some kind of routine or script, here is the simple procedere using QGis (Master 1.9, but 1.8 should also work).
Add your shapefile as vector layer in QGIS
Add your table (can be .csv or .xls files) to QGis in the same way -> Add Vector Layer
Both, table and layer should now be visible in the layer table of contents. Now make a simple Join ...
4
There is an article from ESRI, which describes how to Join an Excel spreadsheet (.xls) to a feature class in ArcMap: http://support.esri.com/en/knowledgebase/techarticles/detail/31793
The article contains information about possible formatting problems and solutions. Since you want to use the model builder you should have a look on the Add Join Tool ...
3
Are the types the same (i.e. both integers/both doubles) both in Excel and ArcMap? Also you may need to make the excel file into a database or CSV in order to join them (just use 'save as' in Excel to change the file type). I've had trouble with Excel files in the past, sometimes it is also because it's the .xlsx (new filetype for the new MS Office) rather ...
3
There's always the adventurous option. You could write the shape file yourself. ESRI publishes the spec for shapefiles, which you can read here:
http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
You would prob also need to write a DBF file. You can read about the format here:
http://www.clicketyclick.dk/databases/xbase/format/dbf.html#DBF_STRUCT
...
3
I'm not sure that this helps with your specific question, and you may well have already seen it, but this is another useful link: http://blogs.esri.com/Support/blogs/esritrainingmatters/archive/2008/11/24/formatting-excel-data-for-use-in-arcgis-desktop.aspx
Only top voted, non community-wiki answers of a minimum length are eligible
