Sqlite3 import csv column ordering

Question

I have a situation where I have CSV files with column names in the first row, which perfectly match the tables in my SQLite3 db, except they are in a different order. By default, with .import table1.csv table1 SQLite3 will just treat the column names as a data row. I want it to not treat it as a data row, but use it to determine which column the data should be added to. Is this possible?

Edit: example table and CSV (however I need a solution that will work with any arbitrary table and CSV that shares the same number of columns and same names, just different ordering, like below):

CREATE TABLE crate_owners (
 crate_id INTEGER NOT NULL,
 owner_id INTEGER NOT NULL,
 created_at TEXT NOT NULL,
 created_by INTEGER NOT NULL,
 owner_kind INTEGER NOT NULL
);

Csv:

crate_id,created_at,created_by,owner_id,owner_kind
208787,2020年02月14日 12:34:33.454958,6128,6128,0
201200,2020年01月23日 09:51:46.202886,50019,50019,0
201211,2020年01月23日 10:27:14.127394,19568,19568,0
201212,2020年01月23日 10:27:15.818358,19568,19568,0
...

• Gerard H. Pille: A realistic sample of one of your CSV files would be helpful.
• Max888: @GerardH.Pille I've added a sample.
• Gerard H. Pille: Just to be sure: no commas but those between values? I'll be back later in the year, er, day.
• Max888: yep, just the commas as shown

Answer 1

create an awk script, eg. /tmp/Max888.awk, containing

BEGIN {
 FS=","
 C["crate_id"] = 1
 C["owner_id"] = 2
 C["created_at"] = 3
 C["created_by"] = 4
 C["owner_kind"] = 5
}
NR == 1 {
 # determine the New Order
 for (N=1;N<=NF;N++) {
 NO[N] = C[$N]
 }
 for (CO in NO) {
 NCO[CO] = NO[CO]
 }
}
NR > 1 {
 OFS=","
 # print 1,ドル2,ドル3,ドル4,ドル5ドル
 print $NCO[NO[1]],$NCO[NO[2]],$NCO[NO[3]],$NCO[NO[4]],$NCO[NO[5]]
}

If your CSV data is contained in the file /tmp/test.dat, and your database in /tmp/test.sq3, you can run

sqlite3 -csv /tmp/test.sq3 '.import "|awk -f /tmp/Max888.awk /tmp/test.dat" crate_owners'

Answer 2

Execute the following commands or place them in a file and use .read <filepathname> command:

--Adjust path to location of the csv file 
.cd B:/datadir 
DROP TABLE IF EXISTS crate_owners_tmp; 
CREATE TABLE crate_owners_tmp (crate_id,created_at,created_by,owner_id,owner_kind); 
.mode csv 
.import data.csv crate_owners_tmp --skip 1 
INSERT INTO crate_owners(crate_id,created_at,created_by,owner_id,owner_kind) SELECT * FROM crate_owners_tmp; 
DROP TABLE IF EXISTS crate_owners_tmp;