3

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?

underdark
  • 84,148
  • 21
  • 231
  • 413
linnea_n
  • 73
  • 1
  • 1
  • 5
  • 2
    What does the Geoprocessing -> Results -> Message say? – Jakub Sisak GeoGraphics Mar 02 '13 at 01:06
  • 1
    Have you tried all the suggestions here? – PolyGeo Mar 02 '13 at 05: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 02 '13 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 02 '13 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 02 '13 at 21:10
  • @Poly Thanks, that's a possibility: but for this question to remain open we really need the OP to provide these details because it's not our duty to guess what they're doing. – whuber Mar 02 '13 at 21:14
  • To add to the detail request, where is the data loss occurring? Are the records distributed throughout the dataset, or is the export simply leaving off the last 2,000 or 20,000 records? This could indicate a problem with attribute data for certain records, or a problem with a specific record that is causing the export to fail. As @whuber said, we need more detail to help diagnose the problem. – Get Spatial Mar 03 '13 at 09:11
  • Ok sorry, more details: I did add the table, displayed xy data, and exported to shapefile. There is no formatting in the table like merged rows, or blank lines. – linnea_n Mar 04 '13 at 17:30
  • oops didn't know enter would send that. I have tried all of the above suggestions except saving as a dbf table, I'll try that after I figure out where the data loss is occurring. – linnea_n Mar 04 '13 at 17:31

2 Answers2

0

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

hapa
  • 421
  • 3
  • 9
  • 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. –  Mar 03 '13 at 00: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 04 '13 at 18:27
0

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.

AndreJ
  • 76,698
  • 5
  • 86
  • 162