import x y coordinates lat always 0

Question

Hello,
I am trying to import x Y coordinates into my gis map using 10.1. I have done this several times and it works with other tables. But for some reason it won't work with this table (extract see below). I have tried many things from copying the table into the table that work and copying its formatting. In the table of contents I can read everything and the points plot along the longitude values but the latitude valuse are always 0!!!! I made it work correctly once when I deleted all the other lines. at some point for some reason it worked. But I need the other info as well! Does anyone pleas have an idea what would help? I have really tried a lot already

Site  Reference  Lat Long
Achany Glen Dugmore et al. (1996) 57.983 -4.396
Beinn Eighe Dugmore et al. (1995) 57.63 -5.3

Any help would be appreceated.

ChristinaBonanati · Answer

Hey Duncan thanks a lot this works!
I remember doing this with the old GIS versions. And it is annoying and I don't understand why its sometimes does and sometimes does not work with excel and can't find the formatting issue. 
anyway I will always do it with csv now.

D_Hornby_ddh_uni · Answer

Aah... there is your problem, you are using Excel. Best bit of advice I can give to anyone is to avoid Excel like the plague! Because you can type anything into it people usually do and its always a formatting issue. I also find it flakey when trying to import data.

So select the cells, headers and rows that contain the data and save as a CSV file. Use that to create a XY layer.

ChristinaBonanati · Answer

Christina,

If  the following extract is as exactly as you say it is then I'm guessing it is some sort of text file?Site Reference Lat Long
Achany Glen Dugmore et al. (1996) 57.983 -4.396
Beinn Eighe Dugmore et al. (1995) 57.63 -5.3

The problem is all the spaces and the computer is not intelligent enough to realise that the space in "et al." is part of "et al." and not a field delimiter.

You need to ensure a consistent structure to your table with recognised delimiters such as a comma. I would recommend restructuring your text file to:"Site","Reference","Lat","Long"
"Achany Glen","Dugmore et al. (1996)",57.983,-4.396
"Beinn Eighe","Dugmore et al. (1995)",57.63,-5.3

Each field is separated by a comma and text is enclosed in "".

Duncan

Hello, and thank you very much for the reply. Sorry I wasn't clear enough, no it is actually an excel sheet that I have. I attatched an extract, maybe you can help me anyhow. Or does what you say also apply for the excel sheet?

thank you!
Christina

Mappe4.xlsx

Hard_Light_Blend.png