How to insert data from a txt file to specific SQLite table column

Question

I am trying to make the search function in mashup more versatile by creating a new column in my SQLite places table called full_location where it will hold the postal_code, place_name, and admin_name1 as a concatenated string. I have written a program to create a txt file with all the concatenated locations. I am wondering if there's a way to import from a txt file to a specific column in my places table. Kind of like what we did for US.txt, but with a specific column in mind.

Answer 1

You could do something like: make the text file have 2 columns, postal_code and full_location. Then import that file into a temp table. Then write a sql to update the places table with full_location from the temp table matching on postal_code.

Another approach would be to use sql and the concatenation operator to simply update the column in sql. The concatenation operator is || (pipe-pipe). Something like:

update places 
set full_location = postal_code || ' ' || place_name || ' ' || admin_name1

From the sqlite doc:

The || operator is "concatenate" - it joins together the two strings of its operands.