I'm trying to import a csv file into a existing temporary table, however instead of that happening two empty tables are created in addition to a third table with the first row as the header, none of them being the table I need with the first line as a row not the column name.

$ cat import.sql
.mode csv
.shell ls > files
create virtual table temp.files using fts5(file);
.import files temp.files
.mode col
select * from files;

$ sqlite3 '' .read import.sql
Usage: .read FILE
$ # no output
$
$ sqlite3 -init import.sql 
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> select * from files;
sqlite> select * from temp.files;
sqlite> select * from "temp.files";
... -- output with first row as header

Probably easiest by showing you what's happening. First, if a temp table exists and a non-temp table with the same name doesn't exist, SQLite uses the temp one:

sqlite> create table temp.test1 (a); sqlite> insert into temp.test1 values (123); sqlite> select * from temp.test1; 123 sqlite> select * from test1; 123 sqlite>

Then we see that FTS5 creates a number of tables, not just the one you name:

sqlite> create virtual table temp.filesinfts5 using fts5(filename); sqlite> .tables temp.filesinfts5 temp.filesinfts5_content temp.filesinfts5_docsize temp.filesinfts5_config temp.filesinfts5_data temp.filesinfts5_idx sqlite>

Then I test importing the file list:

sqlite> .import files filesinfts5 sqlite> select * from filesinfts5; CPMap.png fhyogs1jjj661.png files myCPMap.png wording.txt sqlite>

You may have had .headers turned on:

sqlite> .headers on sqlite> select * from filesinfts5; filename CPMap.png fhyogs1jjj661.png files myCPMap.png wording.txt sqlite>

Hope that helps.

No that doesn't explain importing into a temporary table, the table doesn't need to be virtual and headers are off.

The docs say: when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content.. I've created a temporary table first so the problem is .import can't import into a temporary table, instead creating another "temp.files" table.

In the below example file1 and file2 should be in the temporary files table under column file.

$ sqlite3 
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers off
sqlite> .mode csv
sqlite> .shell echo file1 > import.csv
sqlite> .shell echo file2 >> import.csv
sqlite> create table temp.files(files);
sqlite> .import import.csv temp.files
sqlite> .mode line
sqlite> select * from temp.files; -- this should show file = file1 and file = file2
sqlite> select * from "temp.files"; -- this should not exist
file1 = file2

You make the assumption that the syntax of the .import command is

.import <filename> [<schema>.]<table>

this is not the case. The syntax is:

.import <filename> <table>

You mistakenly assume that the token temp.files means the table files in the schema temp. This is not the case nor is it the documentation of the .import command. It means the temp.files table in the schema main if no table by that name exists in any other attached schema.

If you (a) have no table named files in any schema other than the temp schema; and, (b) use the command .import import.csv files then you will achieve the result for which you appear to be searching.

The CLI is primarily intended as a debugging aid to permit common operations to be carried out in a standard way in order to permit debugging of reported errors in the SQLite3 database by providing a standard and correct common implementation of the interface between the user and the database engine. If the "problem" cannot be reproduced with the CLI that generally indicates that the problem exists in the end-user application and not the SQLite3 database engine.

As such, it IS NOT a full featured application designed for general programming use and does not support the ever expanding feature set that such an application would entail, although it may often be used to provide simple snippets of functionality that are within its capability.

In other words, sqlite3.exe is the equivalent of the original Sybase isql.exe program -- the sole purpose of which was to "send" SQL commands to the server and display the results therefrom. MS SQL Server (being a stolen copy of Sybase) followed the same path, although of late they have changed the name of this simple executable program interface several times and "farted" with its original functionality. There was even a brief era where it was "gooeyfied" so that people who like clickety-pokeys would not feel left out (and added no useless cruft beyond passing SQL to and displaying results from the database server).

There exists in the case of MS SQL Server a "User Interface Application" called Visual SQL Studio (or some such nonsense) that has turned into a huge agglomeration of crap that runs slower than molasses running uphill in the middle of a -40 degree Edmonton winters' day and takes six to sixty times the RAM and DISK resources of the actual server itself.

sqlite3.exe is the equivalent of the original isql.exe program and serves the same purpose. It is not a command-line equivalent of "Visual SQL Studio" or whatever that crap is called these days.

Thanks for the in depth explanation, I understand now.

This is awesome; been wanting this to avoid VACUUM, etc. Thank you!

+1 for inclusion into next release (I've applied against 3.36.0 with good results).

A similarly impactful change, inspired by your suggestion and just checked-in, permits the target table schema to be specified. This can be "temp" for temporary tables or can refer to an ATTACH'ed DB. (Or, it can be set to "main", needlessly.)

I might quibble about the term "regression", but can see some sense in the pre-3.38 behavior. And we do like to preserve long-standing "features" (however documented or not they may be) where reasonably possible. Hence, the older behavior is reverted to here. This should get to the 3.38.1 patch release slated to occur soon.

(Edited to add:) Also, thanks for noticing and reporting this so soon, before the newer behavior became more worthy of preservation.

Well I would consider it a regression bug then as the behaviour has changed, since my script from earlier in the year failed to yield the same database in the current release, which I only discovered by running it yesterday.
I can confirm your latest commit fixes the issue at hand. Thanks.