1

I'm trying to import the geonames database (http://download.geonames.org/export/dump/ specifically the unzipped allCountries.zip file) as follows: 

-- yet another attempt to create geonames "quickly" wo a perl script 

CREATE TABLE geonames ( 
geonameid, 
name, 
asciiname, 
alternatenames, 
latitude, 
longitude, 
featureclass, 
featurecode, 
countrycode, 
cc2, 
admin1code, 
admin2code, 
admin3code, 
admin4code, 
population, 
elevation, 
dem, 
timezone, 
modificationdate DEFAULT '' 
); 

.bail OFF 
.separator "\t" 
.import allCountries.txt geonames

I get this error:

Error: allCountries.txt line 374993: expected 19 columns of data but found 18

I understand the error: some lines in allCountries.txt have fewer than 19 fields (the last field(s) are assumed blank/null).

Can I easily force SQLite3 to treat missing import fields as blank/null?

I realize I can write a Perl script, etc, but I'm curious if this can be done purely in SQLite?

Note that neither ".bail OFF" nor setting a default for the missing field works.

EDIT: I'm still curious about the answer, but it turns out this is a red herring: line 374993 contains a quotation mark, which (correctly) throws sqlite3 import off (see sqlite3 import with quotes). All lines contain 19 columns of data, however.

Community
  • 1
  • 1

1 Answers1

0

If you remove two characters " and ” characters with ' or remove them altogether, it will work. You need to do it for the entire file.

This is prevalent in some of the other geoname database files also. I did it that way, but changing some setting in the sqlite could also do the job.

Note: The second character is a utf-8 character so better to copy-paste it from here.

binit
  • 476
  • 1
  • 5
  • 15