SQLite Forum

Additional Delimiter Support for the CSV Virtual Table Module
Login

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 [link] [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 [source]

Currently only supports Comma Delimited files

(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.

(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

(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"

HTH

-- 
Regards,
Kees Nuyt