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

[Keith Medcalf]

On Tuesday, 4 February, 2020 05:19, Robert M. Münch 
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Keith Medcalf]

On Tuesday, 4 February, 2020 17:23, J. King  wrote:

>Not everyone has access to carrays and intarrays, either, such as PHP
>users like myself.

Then you should probably be creating a temporary table and using that/

begin immediate;
create temporary table inlist(x primary key(x)) without rowid;
insert into temp.inlist values (?); -- for each value you want in your IN list, 
one after each
commit;
select ... from ... where x IN (select x from temp.inlist);
drop table temp.inlist;


In Python one would do something like:

bloodybiglist = [...]
cursor.execute('begin immediate;')
cursor.execute('create temporary table inlist(x primary key(x)) without rowid;')
cursor.executemany('insert into temp.inlist values (?);', list(tuple((x,)) for 
x in bloodybiglist))
cursor.execute('commit;')
cursor.execute('select ... from ... where x in (select x from inlist);')
cursor.execute('drop table temp.inlist;')

In other interface wrappers you would still have to bind the parameters one at 
a time.  Cannot you prepare a statement in PHP (eg, the insert statement) and 
loop though binding the parameters and executing the prepared statement one 
binding parameter by each?

eg:

stmt = prepare('insert into temp.inlist values (?);')
for item in bloodybiglist:
  stmt.bind(stmt, 1, item)
  stmt.execute()
  
Which is what executemany does behind the curtain ...

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Robison]

On Tue, Feb 4, 2020, 5:23 PM J. King  wrote

> Not everyone has access to carrays and intarrays, either, such as PHP
> users like myself.
>

But everyone has access to temp tables, and I think the idea of creating a
temp table, inserting 1000 items in a loop, and using that temp table in
the query, is a lot more clear than formatting a query as a string with
?1000 or more bound parameters. It is more idiomatic SQL, I would think.


>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[J. King]

On February 4, 2020 7:10:52 p.m. EST, Scott Perry  wrote:
>On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
>> 
>> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
>> 
>>> WHERE x IN (?1,?2,?3,?4...,?1000 )
>> 
>> People really do this ?  Sheesh.
>
>It's a pretty common pattern. Sure, queries that are likely to use very
>large IN groups ought to be using carray or intarray but not everyone
>knows they exist :)

Not everyone has access to carrays and intarrays, either, such as PHP users 
like myself. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Perry]

On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
> 
> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
> 
>> WHERE x IN (?1,?2,?3,?4...,?1000 )
> 
> People really do this ?  Sheesh.

It's a pretty common pattern. Sure, queries that are likely to use very large 
IN groups ought to be using carray or intarray but not everyone knows they 
exist :)

Scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Keith Medcalf]

On Tuesday, 4 February, 2020 12:14, Deon Brewis  wrote:

>WHERE x IN (?1,?2,?3,?4...,?1000 )

That would be a really silly construct to use.  Why are you bothering to name 
all the parameters?  Anonymous parameters are merely an array of pointers to 
values.  When you give the parameters names then a linkage between the "name" 
and the "position" needs to be kept, as well as a hash table so that the "name" 
can be looked up.  When you refer to duplicate anonymous parameters you have to 
use a name for the one of them that is not the next anonymous parameter in 
line, but sheesh, naming them all?  Why?

>And the IN clause is filled by a list or array that's held inside the
>calling application memory rather than in SQLITE.

The VDBE program still needs an array of pointers for all used parameter range 
(from 1 to the highest parameter used).  That means that if you use something 
like:

select ?, ?100;

then the VDBE program will allocate an array to hold 100 parameter pointers 
(assuming that number of parameters were allowed).  And bind parameters are 
only stored in application memory if they are TEXT or BLOB type and you make 
the bind call providing a de-allocator (ie, not SQLITE_TRANSIENT) and even then 
if and only if no conversions need to be performed (for example from you 
external encoding to the internal database encoding).

>The alternate to this is to create a virtual table wrapper over the
>internal datasets of the app. Which is of course better, but harder. (We
>need an STL for SQLite. SqliteTL?).

see the carray extension ...

>PS: Doesn't SQLITE internally order an IN list and do a join across it?
>It seems to perform better than I would expect from a flat array.

Sort of.  When you do an IN (?,?,?,?) or IN (value, value, value ...) the 
values or parameters are loaded one after each into a without rowid table 
(effectively, that looks like "create temporary table temptable (variable 
primary key variable) without rowid") and then the table.variable IN 
(...list...) is treated as a "table JOIN temptable ON table.variable == 
temptable.variable".  NULLS in the IN list are silently discarded of course 
since they cannot be used with IN (which is defined as == not IS for each in 
turn).

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unsafe use of virtual table

[Keith Medcalf]

This is part of the trusted schema.  

Virtual Tables and Functions can be labeled as DIRECT_ONLY, INNOCUOUS, or 
unlabeled.  

INNOCUOUS virtual tables and functions can be used anywhere they are allowed 
including in the schema and views and indexes and so forth (provided that they 
would otherwise be permitted in that location).  DIRECT_ONLY virtual tables and 
functions can only be used from top-level SQL, ever.

Unlabeled virtual tables and views depend on whether or not the schema is 
trusted.  If the schema containing those things (in views, the schema 
definitions, etc) is untrusted, then those unlabeled virtual tables and 
functions are treated as DIRECT_ONLY.  If the schema is trusted, then there are 
no restrictions on the use of unlabeled virtual tables and functions.  The TEMP 
schema is always trusted since it must have always been created by the 
application/user and cannot have been a "crafted part" of the database.

The dbstat virtual table is DIRECT_ONLY meaning that since 3.30.0 it can only 
be used in top-level (directly issued) SQL and not in a view, even a view 
created in the temp database.

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

>-Original Message-
>From: sqlite-users  On
>Behalf Of tom-sql...@pettymail.com
>Sent: Tuesday, 4 February, 2020 14:35
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] unsafe use of virtual table
>
>Hi,
>
>I have noticed a change between 3.30 and 3.31.1 and searched for more
>info on "unsafe use of virtual table" on sqlite.org but could not find
>anything relevant.
>
>In 3.30:
>
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create view somestats as select name, pgoffset from dbstat;
>sqlite> select * from somestats;
>sqlite_master|0
>
>
>But in 3.31.1:
>
>SQLite version 3.31.1 2020-01-27 19:55:54
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create view somestats as select name, pgoffset from dbstat;
>sqlite> select * from somestats;
>Error: unsafe use of virtual table "dbstat"
>
>Could someone point me to where I can find more info on unsafe use of
>virtual tables?
>
>Thanks
>Tom
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unsafe use of virtual table

[tom-sqlite]

Hi,

I have noticed a change between 3.30 and 3.31.1 and searched for more
info on "unsafe use of virtual table" on sqlite.org but could not find
anything relevant.

In 3.30:

SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view somestats as select name, pgoffset from dbstat;
sqlite> select * from somestats;
sqlite_master|0


But in 3.31.1:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view somestats as select name, pgoffset from dbstat;
sqlite> select * from somestats;
Error: unsafe use of virtual table "dbstat"

Could someone point me to where I can find more info on unsafe use of
virtual tables?

Thanks
Tom

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:

> WHERE x IN (?1,?2,?3,?4...,?1000 )

People really do this ?  Sheesh.

> The alternate to this is to create a virtual table wrapper over the internal 
> datasets of the app. Which is of course better, but harder. (We need an STL 
> for SQLite. SqliteTL?).

One alternative is to create and populate a temporary table, then use an INNER 
JOIN or an EXCEPT.  You can keep the temporary table around until the app quits.

Another alternative is to construct the command as a string.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Deon Brewis]

WHERE x IN (?1,?2,?3,?4...,?1000 )

And the IN clause is filled by a list or array that's held inside the calling 
application memory rather than in SQLITE.

The alternate to this is to create a virtual table wrapper over the internal 
datasets of the app. Which is of course better, but harder. (We need an STL for 
SQLite. SqliteTL?).

PS: Doesn't SQLITE internally order an IN list and do a join across it? It 
seems to perform better than I would expect from a flat array.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Simon Slavin]

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Alex Bronstein]

Prepared statements are good practice and recommended in places such as
https://www.php.net/manual/en/pdo.prepared-statements.php. There are use
cases for using them with many items in an IN() clause, or when inserting
many rows in a single INSERT statement. In such cases, you can easily end
up with more than 999 parameters.

While sqlite can be compiled with a larger SQLITE_MAX_VARIABLE_NUMBER flag,
there are situations where the application developer doesn't have control
over how the system libraries are compiled. For example, a given PHP
application could run on either a stock Debian/Ubuntu installation, a stock
Fedora/RHEL/CentOS installation, or other systems. Debian compiles sqlite
with SQLITE_MAX_VARIABLE_NUMBER=25
 (issue
) whereas
Fedora doesn't
set the flag
 (issue
), so gets sqlite's
default.

According to some of the answers on
https://stackoverflow.com/questions/6581573/what-are-the-max-number-of-allowable-parameters-per-database-provider-type,
PostgreSQL and some other databases support a 16 bit parameter count (64K
parameters). Given current memory availability, can sqlite's default be
raised to something similar to that?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug report (INSTR() ignores NOCASE on columns)

[Jose Isaias Cabrera]

Stephan Senzel, on Sunday, February 2, 2020 08:12 AM, wrote...
>
> INSTR() ignores NOCASE on columns
>
> ---
>
> example:
>
> SELECT * FROM table WHERE INSTR(column, ' castle ') > 0
>
> returns datasets with 'castle' only, without 'Castle', even if the
> column is set to NOCASE

True statement with v3.31.0:
12:25:41.10>sqlite3
SQLite version 3.31.0 2020-01-22 18:38:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0(a string collate nocase);
sqlite> insert into t0 values ('In my castle I have...');
sqlite> insert into t0 values ('In my castle I have had...');
sqlite> insert into t0 values ('In my castle I''ve never had...');
sqlite> insert into t0 values ('In my Castle I have...');
sqlite> select a from t0 where INSTR(a,' castle') > 0;
In my castle I have...
In my castle I have had...
In my castle I've never had...
sqlite>


> LIKE doesn't have this problem, works well
>
> SELECT * FROM table WHERE column LIKE '% castle %'
>
> returns 'castle' and 'Castle' when column is set to NOCASE

Also true with v3.31.0:
sqlite> select a from t0 where a LIKE '% castle%';
In my castle I have...
In my castle I have had...
In my castle I've never had...
In my Castle I have...
sqlite>

Just making sure... :-)

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Doug]

> On Tue, Feb 4, 2020 at 5:38 PM Doug  wrote:
> > > You're twisting my point here. I obviously want the reverse,
> > > I want the database types to "drive" the binding done. 1-to-1.
> > > Because even if binding a different type would work, via
> SQLite's
> > > own implicit conversion, I don't want that, because it's
> hiding a
> > > bug in the code most likely instead. --DD
> 
> > Is the code inadvertently putting quotes (') around in integer
> value [...]?
> 
> I'm talking about "real" binding here:
> https://www.sqlite.org/c3ref/bind_blob.html
> In C/C++, you could mess up your col indexes when binding, or bind
> incorrectly for some other reason, and "strong static typing" is more
> likely to find those, via SQL failures, than SQLite's default
> flexible-typing, that accepts any value in any typed column,
> unless you have these explicit CHECK+typeof constraints. --DD

So you are talking about a bug in your code where you inadvertently called:
  sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
instead of
  sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
and you want SQLite to tell you about it.

I have a hard time seeing how you could make that kind of coding error, given 
the different parameters and types in the calls.

Doug



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Dominique Devienne]

On Tue, Feb 4, 2020 at 5:38 PM Doug  wrote:
> > You're twisting my point here. I obviously want the reverse,
> > I want the database types to "drive" the binding done. 1-to-1.
> > Because even if binding a different type would work, via SQLite's
> > own implicit conversion, I don't want that, because it's hiding a
> > bug in the code most likely instead. --DD

> Is the code inadvertently putting quotes (') around in integer value [...]?

I'm talking about "real" binding here:
https://www.sqlite.org/c3ref/bind_blob.html
In C/C++, you could mess up your col indexes when binding, or bind incorrectly
for some other reason, and "strong static typing" is more likely to
find those, via
SQL failures, than SQLite's default flexible-typing, that accepts any
value in any typed column,
unless you have these explicit CHECK+typeof constraints. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Doug]

> You're twisting my point here. I obviously want the reverse,
> I want the database types to "drive" the binding done. 1-to-1.
> Because even if binding a different type would work, via SQLite's
> own implicit conversion, I don't want that, because it's hiding a
> bug in the code most likely instead. --DD

WRT the code that the bug is in: I'm assuming that your code is creating text 
SQL statements which it passes to some process, right? The "binding" you 
mention is confusing me. You can't be using query.addBindValue() because the 
type is coerced to match the column type.

So, if you are generating text SQL statements: Is the code inadvertently 
putting quotes (') around in integer value or is the user entering a string and 
your code is taking that input and slapping it into a SQL INSERT statement?

Please explain your possible code "bug".

Doug

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Robert Hairgrove]

On 04.02.20 15:42, Simon Slavin wrote:

On 4 Feb 2020, at 12:18pm, Robert M. Münch  wrote:


- sep=';': field separator character (different from default ',')

If you provide this facility, please don't add it to anything called 'csv' 
since the 'c' stands for 'comma'.

For those playing along at home, csv files using semi-colon are a result of a 
bug in Excel.  Windows has a setting for a 'list separator'.  The two most 
usual values are ',' and ';'.  The CSV export filter in Excel takes its 
separator from this field rather than always using a comma, because it was 
written by someone who wasn't aware of, didn't understand, or was intentionally 
trying to disrupt the standard.  Decades after being told about the bug, 
Microsoft hasn't fixed it.

There are a couple of other errors in Excel's CSV filters including how strings 
are quoted and how a blank value differs from a zero-length string.  The best 
way I've seen to handle this was to add a new filter to your software, similar 
to 'csv', called something like 'exceltext' which did things the Excel way.


Believe it or not, there is no binding standard for the CSV format. The 
closest anyone has come was RFC 4180.

However:

According to RFC 4180, section 2:
  "While there are various specifications and implementations for the
   CSV format (for ex. [4], [5], [6] and [7]), there is no formal
   specification in existence, which allows for a wide variety of
   interpretations of CSV files."

https://tools.ietf.org/html/rfc4180#section-2

In section 3, under "Interoperability considerations":
  "Due to lack of a single specification, there are considerable
   differences among implementations.  Implementors should "be
   conservative in what you do, be liberal in what you accept from
   others" (RFC 793 [8]) when processing CSV files."

https://tools.ietf.org/html/rfc4180#section-3

That being said, the problem with trying to enforce the comma as the 
sole delimiter character is due to the fact that over half of the 
non-English speaking world (or perhaps even more) uses the comma as the 
decimal separator. The "work-around" for that, of course, would be to 
enclose all fields in double quote characters. But, as we know, the 
800-pound gorilla in the room doesn't necessarily do that...


I agree that this would be a very good option to have. In the meantime, 
check out libcsv on GitHub:

https://github.com/rgamble/libcsv

It adheres as closely to what standards there are, and you can choose 
your own delimiter and quote character if you like. Of course, you have 
to do some programming to use it, but it's really easy to use. And it is 
very fast since it does just one thing, but does it very well.


HTH,
Bob Hairgrove

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 4 Feb 2020, at 12:18pm, Robert M. Münch  wrote:

> - sep=';': field separator character (different from default ',')

If you provide this facility, please don't add it to anything called 'csv' 
since the 'c' stands for 'comma'.

For those playing along at home, csv files using semi-colon are a result of a 
bug in Excel.  Windows has a setting for a 'list separator'.  The two most 
usual values are ',' and ';'.  The CSV export filter in Excel takes its 
separator from this field rather than always using a comma, because it was 
written by someone who wasn't aware of, didn't understand, or was intentionally 
trying to disrupt the standard.  Decades after being told about the bug, 
Microsoft hasn't fixed it.

There are a couple of other errors in Excel's CSV filters including how strings 
are quoted and how a blank value differs from a zero-length string.  The best 
way I've seen to handle this was to add a new filter to your software, similar 
to 'csv', called something like 'exceltext' which did things the Excel way.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Kees Nuyt]

On Tue, 04 Feb 2020 13:18:30 +0100, you 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 don't see the need, the feature is available in another form

$ cat test.csv

"a";"b"
"c";"d"

$ sqlite3 test.db \
"DROP TABLE IF EXISTS tbl1" \
".mode csv" \
".separator ;" \
".import test.csv tbl1" \
". mode column" \
".headers on" \
"SELECT * FROM tbl1"

a   b
--  --
c   d


-- 
Regards,
Kees Nuyt

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Robert M. Münch]

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.

-- 

Robert M. Münch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check constrain execution timing change? (Now a bug)

[Dominique Devienne]

On Mon, Feb 3, 2020 at 6:42 PM James K. Lowden  wrote:
> Do you want to force applications to "pre-convert" values the DBMS can
> convert implicitly?

Yes, that's exactly what I want James.
I want the enforce the bind-value type to be an exact match for the
column value type.

I could before, via CHECK+typeof(), so that's still breaking BC,
even though I accept that now that I was explained things.

> Do you want binding choices in the application to
> drive the datatype in the database, or do you want the database to
> enforce types?

You're twisting my point here. I obviously want the reverse,
I want the database types to "drive" the binding done. 1-to-1.
Because even if binding a different type would work, via SQLite's
own implicit conversion, I don't want that, because it's hiding a bug
in the code most likely instead. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users