.separator and .mode commands interactions

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

.separator and .mode commands interactions

Luc Charansonney
Hello,

I apologize if the question has already been asked before, but I need some clarification on the use of .mode and .separator relatively to the .import command.

Say I have a structured tab-separated text file "mydata.txt" I want to import into a database using the .import FILE TABLE command. If I refer to the documentation obtained by typing ".help" in the SQLite shell, it states :
.separator COL ?ROW?   Change the column separator and optionally the row separator for both the output mode and .import

But this is in apparent contradiction with the Command Line Shell help :
"Note that it is important to set the "mode" to "csv" before running the ".import" command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format. "
https://sqlite.org/cli.html

So, if I use .separator, as hinted by the built-in .help command, I get the following
sqlite> .separator tabs
sqlite> .import mydata.txt mytable
I get the following error :
Error: multi-character column separators not allowed for import

So I fall back on my feet by using .mode instead of .separator:
sqlite> .mode tabs
sqlite> .import mydata.txt mytable

But I'd like to understand why .separator does not behave the way I expect it to? Is it because there is confusion between the default ".mode" (which is comma-separated) onto which I impose a tab through ".separator"?

Thanks for your explanations, and for this great piece of software.

Luc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .separator and .mode commands interactions

Clemens Ladisch
Luc Charansonney wrote:
> sqlite> .separator tabs

  sqlite> select 1, 2;
  1tabs2

> sqlite> .import mydata.txt mytable
> Error: multi-character column separators not allowed for import

You should have used ".separator \t".

> So I fall back on my feet by using .mode instead of .separator:
> sqlite> .mode tabs
> sqlite> .import mydata.txt mytable

The .mode command expects a name, but .separator the actual characters.

The modes have more differences than the separators, but that's mostly
for output.  As far as .import is concerned, the only mode with special
behaviour is "ascii" (which does not use CSV quoting rules).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users