Additional Delimiter Support for the CSV Virtual Table Module
(1.1) By Angela Lusweti (anamikoye) on 2021-01-08 10:08:02 edited from 1.0 [link] [source]
Request to allow the CSV Virtual Table module (https://www.sqlite.org/csv.html) to support additional delimiters, e.g. Tab, Colon, etc. over and above supporting Comma Separated Values Proposal: To make delimiter field a variable, in order to extend this, integrate existing solutions e.g. http://www.dessus.com/files/vsv.c to source
(2) By Simon Slavin (slavin) on 2021-01-08 08:33:19 in reply to 1.0 [source]
The 'C' in 'CSV' stands for 'comma'.
By all means, take the open source code for the CSV extension, modify it any way you want, and make the result available as open source to other SQLite users. Just make sure you don't call it 'CSV'. A common name for the sort of files you mentioned is 'Character Delimited Files' or 'CDF. If the SQLite developers like the result perhaps they'll imitate it themselves.
The work you do is going to be more complicated than just allowing any character to be a delimiter. For instance, your code will have to handle cases where the separator is used inside a value, which means you'll have to decide whether your code should match quotes or not. Also, you'll have to decide what your code does if the user specifies that the separator is the return character, or a quote character.
(3) By tom (younique) on 2021-01-08 09:39:02 in reply to 2 [link] [source]
Except for using doubles-quotes as a delimiter - shouldn't this problem already be solved? The delimiter inside a field always has to be quoted or escaped, no matter whether it's a comma or a tab.
(4) By Angela Lusweti (anamikoye) on 2021-01-08 09:47:41 in reply to 3 [link] [source]
Currently only supports Comma Delimited files
(5) By anonymous on 2021-01-08 09:50:29 in reply to 1.0 [link] [source]
Request to allow the CSV Virtual Table module (https://www.sqlite.org/csv.html) to support additional delimiters
If you are using the CSV extension, the approach detailed below may or may not work; I have been unable to compile the extension and therefore I am unable to test.
On the other hand, if you are using .import, (that is, the CLI) you can use any delimiter using the technique illustrated below (Iam using Windows).
Step 1: Edit your CSV file using any text editor and add the following as the first line in your file; here I am using | as the delimiter.
sep = |
Step 2: My file is SAMPLE.CSV; examine your file to ensure that the first line is inserted correctly.
sqlite> .shell type 'E:\CSV\sample.csv' sep=! FirstName|Surname|Factor ajay|askoolum|100.23 askoolum|ajay|23.10
Note the first line. - you can specify any arbitrary single character delimiter. The second line contains column names and is followed by two rows of data.
Step 3: Drop the target table (if it exists)
sqlite> drop table if exists tblMySample;
Step 4: Read the CSV which contains columns delimited by |
sqlite> .import --skip 1 E:/CSV/sample.csv tblMySample
Step 5: Examine your table
sqlite> select * from tblMySample; FirstName Surname Factor --------- -------- ------ ajay askoolum 100.23 askoolum ajay 23.10
For the sake of completeness, I repeat the steps above using ^ as the delimiter: here's my session:
sqlite> .shell type 'E:\CSV\sample.csv' sep=^ FirstName|Surname|Factor ajay|askoolum|100.23 askoolum|ajay|23.10 sqlite> drop table if exists tblMySample; sqlite> .import --skip 1 E:/CSV/sample.csv tblMySample sqlite> select * from tblMySample; FirstName Surname Factor --------- -------- ------ ajay askoolum 100.23 askoolum ajay 23.10
(6) By TripeHound on 2021-01-08 09:56:21 in reply to 3 [link] [source]
But you have to decide on, or specify, the way of quoting or escaping an embedded delimiter. And suppose the user wants to use the character normally used as the "escape" character as their delimiter... nothing that can't be ironed-out, but not as simple as "just" changing the delimiter character.
(7) By Angela Lusweti (anamikoye) on 2021-01-08 10:00:36 in reply to 5 [link] [source]
Thanks for this, but I'm not using the shell. Would make things a whole lot easier..
(8) By Keith Medcalf (kmedcalf) on 2021-01-08 10:02:01 in reply to 1.0 [link] [source]
You can try downloading VSV.C from http://www.dessus.com/files/vsv.c and see if that suits your needs. See the comments in the file for the parameters I added. It is free and public domain, so if you break it you own both halves ...
(9) By Angela Lusweti (anamikoye) on 2021-01-08 10:04:19 in reply to 8 [link] [source]
Using it, works great! I guess what I'm really asking for is official support
(10) By Kees Nuyt (knu) on 2021-01-08 15:50:52 in reply to 1.1 [link] [source]
Other separators are supported by the sqlite3 shell. Consider this script:
#!/bin/bash set -x # echo commands rm -f test.db* test.csv # clean start # create colon delimited input file cat >test.csv <<EOF 1:a:b 2:c:d 3:e:f EOF sqlite3 test.db \ "CREATE TABLE t1 ( \ id INTEGER PRIMARY KEY NOT NULL, \ a TEXT, \ b TEXT)" \ ".mode csv" \ ".separator ':'" \ ".import test.csv t1" \ ".mode box" \ "SELECT * FROM t1"
-- Regards, Kees Nuyt