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
...

A realistic sample of one of your CSV files would be helpful. — Gerard H. Pille
– Gerard H. Pille, Commented Jan 2, 2022 at 9:55
Just to be sure: no commas but those between values? I'll be back later in the year, er, day. — Gerard H. Pille
– Gerard H. Pille, Commented Jan 2, 2022 at 10:10

Gerard H. Pille · Accepted Answer · 2022-01-02 18:45:00Z

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'

mustaccio · Accepted Answer · 2022-01-11 13:41:59Z

0

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;

edited Jan 11, 2022 at 13:41

mustaccio

28.9k24 gold badges60 silver badges77 bronze badges

answered Jan 11, 2022 at 9:41

PChemGuy

1012 bronze badges

Add a comment |

Stack Exchange Network

Sqlite3 import csv column ordering

2 Answers 2

Your Answer

Hot Network Questions

Sqlite3 import csv column ordering

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions