How to set the column separator for the CSV extension in the CLI?
(1) By anonymous on 2020-05-21 14:25:41
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]
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]
Another option is to use vsv.c, written by Keith Medcalf. You can find it linked [here](http://sqlite.1065341.n5.nabble.com/csv-extension-feature-requests-new-options-for-field-separator-different-from-default-and-skip-N-to-s-td109828.html). 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 ```
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.