Re: csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

On Tuesday, 4 February, 2020 22:31, Keith Medcalf <[email protected]> wrote:

The vsv.c (source) file line counting is now verified and I have added a skip= parameter.  Adding skip was far easier than variable separators ...

Same location, file updated:  http://www.dessus.com/files/vsv.c
The complete collection:      http://www.dessus.com/files/sqlite3extensions.zip

Using tbartilde.csv containing:
a|b|c|d~1|2|3|4~2|"3|5"|4|5~3|4|5|6~4|5|6|7

SQLite version 3.32.0 2020-02-05 05:21:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~');
sqlite> .mode col
sqlite> .head on
sqlite> select * from t;
a           b           c           d
----------  ----------  ----------  ----------
1           2           3           4
2           3|5         4           5
3           4           5           6
4           5           6           7

sqlite> drop table t;
sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=3);
sqlite> select * from t;
a           b           c           d
----------  ----------  ----------  ----------
4           5           6           7

sqlite> drop table t;
sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=4);
sqlite> select * from t;
sqlite> drop table t;
sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=5);
Error: premature end of file during skip
sqlite>



>On Tuesday, 4 February, 2020 05:19, Robert M. Münch <[email protected]> wrote:
>
>>On 3 Dec 2019, at 16:10, Jannick wrote:
>
>>> would it be possible to add to the csv extension the following
>>> parameter options (with syntax along the lines of):
>
>>> - sep=';': field separator character (different from default ',')
>>> - skip=N: skip the first N lines
>
>>> Both options would be very helpful for information of fixed format
>>> downloaded from information providers.
>
>>Hi, those would be very useful enhancements.
>
>I did some of the changes and you can get the resulting extension called
>VSV (Variably Separated Values) from:
>
>http://www.dessus.com/files/vsv.c
>
>I added the facility to specify the field and record separator
>characters.  I did not add skip, though I may look at adding that too,
>but one can simply use the OFFSET in SQL to ignore some rows at the
>beginning of the file.  I made some other changes also which makes this
>non-compliant with the RFC.
>
>Data between the "field separator" markers can consist of any arbitrary
>string of bytes that DOES NOT include the field or record separator
>bytes.
>Data between the double-quotes can consist of any arbitrary string of
>bytes except that double-quotes must be escaped by doubling them.
>
>The added parameters are fsep=SEPERATOR and rsep=SEPERATOR for the field
>and record seperators respectively.
>
>SEPERATOR is a single quoted string that may be in the following formats:
>
>'x'    where x is any arbitrary byte and will be used as the separator
>character.
>'\x'   for standard escape codes (tab = \t, vtab = \v, Formfeed = \f,
>Newline = \n).
>'\xhh' where hh is the hexidecimal code for the byte to use.
>
>defaults if not specified are fsep=',' and rsep='\n'
>
>so to read the following file:
>
>a|b|c|d~1|2|3|4~2|"3|5"|4|5~3|4|5|6~4|5|6|7
>
>you can use the following commands:
>
>SQLite version 3.32.0 2020-02-05 02:43:27
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create virtual table tbartilde using
>vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~');
>sqlite> .mode col
>sqlite> .head on
>sqlite> select * from tbartilde;
>a           b           c           d
>----------  ----------  ----------  ----------
>1           2           3           4
>2           3|5         4           5
>3           4           5           6
>4           5           6           7
>
>Might still have some line counting errors and haven't figured out how to
>implement skip yet ...

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. 



_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users