File ext/misc/csv.c mentions support for separators other than ",". But I haven't found how to set the separator in the CLI. I tried .separator "\t" before .load csv, but the CSV extension seemingly ignores it when it creates a table with CREATE VIRTUAL TABLE.

Full replication (that doesn't work):

File test_tsv.sql:

.separator "\t"
.load csv
CREATE VIRTUAL TABLE temp.t1 USING csv(filename="sample.tsv");
.mode columns
.header on
select * from t1;

Tested with:

echo -e "col_text\tcol_int\nMary\t1\nJohn\t2" > sample.tsv
sqlite3 '' < test_tsv.sql

Output:

c0                     
----------------       
col_text        col_int
Mary    1              
John    2              

Another option is to use vsv.c, written by Keith Medcalf. You can find it linked here. I have used it to do more-or-less what you attempted.

I tried both options. Here's a summary.

I compiled vsv.c with the source of SQLite 3.32.0:

curl --remote-name 'http://www.dessus.com/files/vsv.c'
gcc -g -I. -fPIC -dynamiclib vsv.c -o vsv.dylib

got 4 warnings on compilation, still tested it with:

SELECT load_extension("vsv");
CREATE VIRTUAL TABLE temp.vsv USING vsv(filename="./sample.tsv");
SELECT * FROM vsv;

It returned an error on CREATE VIRTUAL TABLE:

Assertion failed: (sizeof(azPValue)==sizeof(azParam)), function vsvtabConnect, file vsv.c, line 946.

Meanwhile .mode tabs worked as expected:

.mode tabs
.import sample.tsv t1
.header on
SELECT * FROM t1;

Moving on with .mode tabs.

Thank you very much for the hints, Larry.

In vsv.c at line 938 change the 6 to a 5.

After changing that number, I'm getting an error during compiling (MacOS):

Undefined symbols for architecture x86_64:
  "_strnicmp", referenced from:
      _vsvtabConnect in vsv-35f1d0.o
      _vsv_parse_sep_char in vsv-35f1d0.o
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)

A quick google search reveals that on Mac OS the case insensitive compare functions have different names.

strnicmp => strncasecmp  
stricmp  => strcasecmp

Thank you, Keith. I'll try to fix it.

I'm not sure if/when I replied to this post... BUT IT ROCKS!!!

This implementation is only slightly different than the CSV version supplied with the SQLITE3 source and frankly I use them both. What's awesome about this is that I've been able to load the extensions into my golang projects too. In fact my use-case loads a zipcode table with lat/long and I use another extension to compute the estimated, as the crow flies, distance between two zipcodes.

But one thing is missing from the mention. If you use chroot, like I did in both linux, freebsd and OpenBSD... you have to remember to copy the dependent libs from the root OS to the chroot in the same/similar path tree.

(hope that makes sense)

I compiled my vsv.c and moved it to the lib/ folder of my chroot dir.

I ran this to locate the DEPS

$ ldd lib/vsv.so 
lib/vsv.so:
        Start            End              Type  Open Ref GrpRef Name
        00000f929a569000 00000f929a572000 dlib  1    0   0      /home/rbucker/src/bppdash/lib/vsv.so
        00000f9323bc1000 00000f9323bf1000 rlib  0    1   0      /usr/lib/libm.so.10.1

I had to create 'usr/lib' and then copy '/usr/lib/libm.so.10.1' to 'usr/lib'.

then I was able to chroot and then load the extension.