On Mon, 11 May 2015 22:24:53 +0300
<tonyp at acm.org> wrote:
> Current .import behavior is that when the imported table does not
> exist, it will be created using the first row from the data file as
> column names for the new table.
Huh, somehow I'd forgotten that!
> Once the initial table is created by the first .import, every
> subsequent time .import will add the header row again and again as
> data.
I think your suggested solution is a bit arcane, though. A single
change to the header row, perhaps capitalization or adding an
underscore, would cause the header row to be treated as data. And the
whole create-table-from-headers feature, while admittedly sometimes
convenient, is also a lion trap. Most of the time, I'd rather be told
the tablename I typed doesn't exist than to automatically create a new
one with the wrong name or in the wrong database.
It would be nice to be able to control that feature instead of adding to
its automagic. In addition, it would be nice to control which lines of
the data file are imported.
My first suggestion would be to add a setting, perhaps
.import_mode create|require|skip_header|check_header
or
.autocreate on|off
The first option's values are:
create: .import *always* creates a table (else error)
require: .import *never* creates a table
skip_header: like require, and skip the first line of a file
check_header: like require, and verify that strings in the first
line of the file match the column names
That's pretty comprehensive, but also a little rigid. Sometimes
there's more to choose about the file than how to treat the first
line. That's more than a humble .import_mode setting can bear.
Deselecting the header line isn't so hard, after all. One way is to
replace
sqlite3 db ".import '${FILE}' tablename"
with
sed 1d ${FILE} | sqlite3 db ".import '/dev/stdin' tablename"
But what if it's not a one-liner? How to choose lines through
the .import syntax?
.import FILE TABLE
I would take a page out of Perl's syntax, and let FILE be a command if
the first character is '|',
sqlite3 db ".import '| sed 1d ${FILE}' tablename"
Drop the leading pipe character of the FILE string, hand the rest to
exec(2), and read its output from a pipe. That give you the full
capabilty of the command line to manipulate the file as it's imported.
--jkl
