[Bug]: Importing CSV with column headers doesn't work if columns are not in same order as table in DB

[digitalliving]

What did you do?

I have a table in my database called "Invoices" and a file called "Invoices.csv". The column order in "Invoices.csv" doesn't match the table "Invoices", but the column names match the headers in the CSV file.

What did you expect to see?

When I import with "Column names in the first line" I was expecting that the correct data from the CSV would go into the correct columns in the table. Is that not correct?

What did you see instead?

The import used the column locations (CSV column 1 went in to table column 1) rather than the column names (CSV InvoiceId to table InvoiceId).

It looks like this was surfaced in 2015 (issue #243) but it doesn't show a resolution. Given that there is a check box to include headers, I (wrongly) thought this meant it would put the correct data in the correct columns by column name/header. It would be good to highlight that this doesn't happen, as I ended up with a completely corrupt database as a result, and didn't notice until I couldn't understand why my code wasn't working!

DB4S Version

3.12.2

What OS are you seeing the problem on?

Windows

OS version

Windows 11

Relevant log output

SAVEPOINT "db4s_csvimport_17138632785216902";
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
SELECT COUNT(*) FROM "main"."Invoices"
SELECT "_rowid_",* FROM "main"."Invoices" LIMIT 0, 49999;
ROLLBACK TO SAVEPOINT "db4s_csvimport_17138632785216902";
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
RELEASE "db4s_csvimport_17138632785216902";
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
SELECT COUNT(*) FROM "main"."Invoices"
SELECT "_rowid_",* FROM "main"."Invoices" LIMIT 0, 49999;

Prevention against duplicate issues

• I have searched for similar issues

[chrisjlocke]

Importing a csv 'with headers' is mainly used to import into a new table. The field names will derive from the headings in the csv.

Once the data has been imported into a table, you can use

insert into existingTable (field1, field2, field3)
select field1, field2, field3
from table-i-just-imported-the-csv-into

to swop any fields around, exclude fields, etc.
This is a more common method than importing directly into a live table .. if you select the wrong import options, you could add corrupted data into a live table. Much better to import into a 'staging table' where the data can be sanitized before copying to the proper table.