Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a fairly large excel file that I need to convert to a shapefile, but everytime I export the data, I lose a bunch of it. The excel file has about 277,000 records, but the shapefile will only contain about 260,000 records. I've tried to convert the .xlsx to .csv, which only lost about 2,000 records the first time I exported it, but lost about 20,000 the second time. Why am I losing data during this conversion?

share|improve this question
2  
What does the Geoprocessing -> Results -> Message say? – Jakub Mar 2 at 1:06
1  
Have you tried all the suggestions here? – PolyGeo Mar 2 at 5:30
4  
This is probably a data quality issue. Excel does not enforce any sort of database integrity constraints because people like to make their spreadsheets pretty with various formatting, merged rows and columns, blank lines, etc. Are you sure that's not the case in your data? If you can, go back to the original data source, which probably did start out in a relational database, and export that to something you can work with. – blah238 Mar 2 at 17:17
I believe Excel does not have an export-to-shapefile capability, so how exactly are you "exporting" this file? If your problem is in the conversion to CSV, that makes this an Excel-specific question which might have a better chance of being answered on StackOverflow--and would be more appropriate there. – whuber♦ Mar 2 at 20:35
1  
@whuber My bet for export-to-shapefile is add *.xls as table, use Add XY Display, then Data|Export Data to get shapefile. – PolyGeo Mar 2 at 21:10
show 4 more comments

2 Answers

up vote 0 down vote accepted

I had a similar problem importing .csv into QGIS.
Try to select all cells and change cell format from 'General' to 'Text'.
That works for me

share|improve this answer
A CSV file won't retain information on data types in each column. This MAY help if the CSV is saved as an Excel file then imported into GIS software. – GeoKevin Mar 3 at 0:34
this worked! thank you so much. I changed the format to text and managed to delete some unneeded records, which made the file smaller and a bit easier to work with. – linnea_n Mar 4 at 18:27
up vote 0 down vote

If you have converted the data to csv, you have to create a csvt file to tell the GIS software which data type should be assigned to the fields. And you have to make sure that the data coincides to that. Excel is not bound to put data in a column to the same data type, GIS needs that.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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