SQLite Forum

How to set the column separator for the CSV extension in the CLI?
Login

How to set the column separator for the CSV extension in the CLI?

(1) By anonymous on 2020-05-21 14:25:41 [link] [source]

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              

(2) By Larry Brasfield (LarryBrasfield) on 2020-05-21 17:14:38 in reply to 1 [link] [source]

As you say, that code mentions something about separators, saying "... extended with the option of having a separator other than ','.". But it does not, in fact as written, provide a way to specify any other separator than ','. So that is your primary problem.

You would do better to use the sqlite3 .import command after setting ".mode tabs".

(3) By Larry Brasfield (LarryBrasfield) on 2020-05-21 17:26:20 in reply to 1 [link] [source]

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.

(4) By anonymous on 2020-05-24 07:18:26 in reply to 3 [link] [source]

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.

(5) By Keith Medcalf (kmedcalf) on 2020-05-24 10:14:45 in reply to 4 [link] [source]

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

(6) By anonymous on 2020-05-26 08:43:48 in reply to 5 [source]

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)

(7) By Keith Medcalf (kmedcalf) on 2020-05-26 11:06:05 in reply to 6 [link] [source]

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

strnicmp => strncasecmp  
stricmp  => strcasecmp

(8) By anonymous on 2020-05-27 12:08:42 in reply to 7 [link] [source]

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

(9) By rbucker on 2021-03-27 01:53:38 in reply to 4 [link] [source]

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.