Adding data from external csv file into a table in the SpatiaLite data base in QGIS – data base manager

Issue

This Content is from Stack Overflow. Question asked by Anuruddha Marambe

I create spatialite database in the QGIS -database manager and 3 spatial layers already to the database.
I want to add another data table (cities1000) and i already created the table. Now, i want to insert data into the table(cities1000) using external tab delimited text file, which has relevant dataset.
However, this step raising an error. I tried following so far,

INSERT INTO cities1000
(id_geoname, name, name_sort, name_alt, latitude, longitude, feature_class, 
feature_code, country_code, country_codes_alt,
admin1_code, admin2_code, admin3_code, admin4_code, population, elevation, dem_type, 
timezone_id, modification_date, geom_wgs84)
SELECT
COL001 AS id_geoname,
COL002 AS name,
CASE WHEN COL003 IS NULL THEN '' ELSE COL003 END AS name_sort,
CASE WHEN COL004 IS NULL THEN '' ELSE COL004 END AS name_alt,
CAST(COL005 AS DOUBLE) AS latitude,
CAST(COL006 AS DOUBLE) AS longitude,
COL007 AS feature_class,
COL008 AS feature_code,
COL009 AS country_code,
CASE WHEN COL010 IS NULL THEN '' ELSE COL010 END AS country_codes_alt,
CASE WHEN COL011 IS NULL THEN '0' ELSE COL011 END AS admin1_code,
CASE WHEN COL012 IS NULL THEN '' ELSE COL012 END AS admin2_code,
CASE WHEN COL013 IS NULL THEN '' ELSE COL013 END AS admin3_code,
CASE WHEN COL014 IS NULL THEN '' ELSE COL014 END AS admin4_code,
CAST(COL015 AS INTEGER) AS population,
CASE WHEN COL016 IS NULL THEN 0 ELSE CAST(COL016 AS DOUBLE) END AS elevation,
COL017 AS dem_type,
COL018 AS timezone_id,
COL019 AS modification_date,
MakePoint(CAST(COL006 AS DOUBLE), CAST(COL005 AS DOUBLE), 4326) AS geom_wgs84
FROM foreign table(path = 'C:UsersanurDownloadscities1000cities1000.txt', 
delimiter='|')
order by country_code,admin1_code, name;

The error massage is,

`near "foreign": syntax error`

I need this data set up in the database under given column of the table. Thank you!



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?