I have been importing data into PostgreSQL tables all day using ogr2ogr and then spatially enabling the data with no issues up to now. I have been cutting and pasting the code and this is what I have been using:
ALTER TABLE mytable ADD COLUMN geom geometry(POINT,4326); UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(cast (lon AS double precision),cast (lat AS double precision ) ),4326);
On the last table, I suddenly get this error:
ERROR: invalid input syntax for type double precision: "#VALUE!"
Nothing has changed, at least as far as I know, I was importing a csv file, same as all the others.
I checked the csv file for spaces or unusual characters but it looks in order
Column lon, lon are of type character varying
here is a sample of the data
id,name,name_alt,lat,lon,description 1,Abbott Seamount,,31.8,174.3,A seamount located north of Midway Islands (US) in the North Pacific Ocean; name origin unknown. 2,Abra Canyon,,17.51667,120.36667,An undersea canyon located off the northwest coast of the Philippine island of Luzon in the South China Sea; named after the Abra River 3,Abraham Canyon,Abraham Sea Valley,52.61667,172.33333,An undersea canyon in the area of the Aleutian Islands in the North Pacific Ocean; named in association with Abraham Bay; Attu Island; Alaska (US). 4,Abrolhos Bank,,-18.5,38.75,A bank located off the coast of Brazil in the Atlantic Ocean; upon which sits the Arquipélago dos Abrolhos (archipelago). 5,Abrolhos Seamounts,Abrolhos Ridge; Cadeia dos Abrolhos,-17.33333,36.5,Seamounts located off the coast of Brazil in the Atlantic Ocean; named in association with the Abrolhos Bank and Arquipélago dos Abrolhos (archipelago). 6,Abubacer Ridge,,36.8,1.65,An undersea ridge located off the coast of Spain in the western Mediterranean Sea; named for Ibn Tufayl (known as Abubacer to Europeans); 12th-century Spanish-Arab philosopher and physician. 7,Aby Canyon,,4.25,-3.66667,An undersea canyon located off the coast of Côte d'Ivoire; named in association with Lagune Aby; Côte d'Ivoire (lagoon)
What am I missing here?