[sqlite] Strange behaviour of sqlite3_stmt_busy

[Clemens Ladisch]

gwenn wrote:
> sqlite3_stmt_busy returns true after sqlite3_step returns DONE.

The documentation says:
| The sqlite3_stmt_busy(S) interface returns true (non-zero) if the
| prepared statement S has been stepped at least once using
| sqlite3_step(S) but has not run to completion and/or has not been
| reset using sqlite3_reset(S).

The statement has not been reset, and that "and/or" can be read as "or".


Regards,
Clemens

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Howard Kapustein]

>There cannot be a fully portable way, because path specifications are not 
>portable
Which begs the question, why isn't there an xGetTempFilename VFS function in 
sqlite3_vfs?

Wouldn't the simplify things? Have the VFS handle it apropos, with the default 
VFS' in SQLite (or an older VFS lacking it) use the current hunt sequences for 
compat

- Howard

This email may contain confidential and privileged information.? Any 
unauthorized use is prohibited.? If you are not the intended recipient, please 
contact the? sender by reply email and destroy all copies of the original 
message.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Tuesday, July 28, 2015 4:31 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] pragma temp_store_directory is deprecated, what is the 
alternative?


On 28 Jul 2015, at 9:55am, Paolo Bolzoni  
wrote:

> From the C interface, the way to decide the directory is setting the 
> value of sqlite3_temp_directory char pointer. As explained here:
> https://www.sqlite.org/c3ref/temp_directory.html
> This is also the first place sqlite3 checks.

The documentation is clear:

"Applications are strongly discouraged from using this global variable. It is 
required to set a temporary folder on Windows Runtime (WinRT). But for all 
other platforms, it is highly recommended that applications neither read nor 
write this variable. This global variable is a relic that exists for backwards 
compatibility of legacy applications and should be avoided in new projects."

Also, sqlite3_temp_directory is accessed by the VFS level, by choice of the 
VFS.  You might someday switch to another VFS which ignores it.

> From anywhere else sqlite3 checks the value of those env variables:
> SQLITE_TMPDIR, TMPDIR, TMP, TEMP, USERPROFILE.

All uses of SQLite are 'the C interface'.  That's all SQLite is: C code.  The C 
code checks the above places, depending on which VFS you're using, which 
usually comes down to which OS you're using.

The C code checks whatever environment variable the OS expects a user to use to 
set a default location for their temporary files.  (It also checks a lot of 
other places, but it will definitely check whatever the documentation for the 
OS says is the correct environment variable.)  Generally speaking you should 
let the computer's user or administrator set this variable, since they know 
which drives they have attached to the computer, and which one they want 
temporary files on. You're just a programmer of one application, running on a 
computer you've never seen.

The exceptions are for embedded controllers, where the programmer is the 
administrator, and that on all platforms SQLite can create huge temporary files 
and some systems don't have that much space available for temporary files.

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

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :

>
> On 30 Jul 2015, at 9:57pm, Sylvain Pointeau  > wrote:
>
> > no it does not work double clicking on the csv to open it in excel, I am
> > 100% sure (I just tried again), you have to go through the data->import
> and
> > set up the columns as text for it to work
>
> Yes, as I wrote, this bad behaviour (which you could reasonably call a
> bug) is documented.  That's how Microsoft wrote Excel and that's the way
> they want it to work, and that's how it will continue to work.
>
> Simon.
>
>
There is a workaround for the leading 0, but you cannot have multi-line
when doing this. It is one or the other, too bad! I though generating xml
for excel instead of csv, but I didn't have time to try yet.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 10:52 PM, Simon Slavin  wrote:

>
> On 30 Jul 2015, at 9:50pm, Sylvain Pointeau 
> wrote:
>
> > leading
> > 0 are removed when opening a csv file by double clicking on it to open it
> > in excel.
>
> This is documented behaviour in Excel, which assumes that all cells
> contain numbers, and therefore that leading zeros can be removed.  If you
> don't want them removed you have to quote the value, e.g. "0123".
>
> Simon.


no it does not work double clicking on the csv to open it in excel, I am
100% sure (I just tried again), you have to go through the data->import and
set up the columns as text for it to work

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 9:00 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/30/2015 10:58 AM, Sylvain Pointeau wrote:
> > is it possible? in a lot of cases, I cannot use sqlite (executable)
> > because of the lack of a good CSV import. It would really great if
> > this could be addressed.
>
> Use the APSW shell:
>
>   http://rogerbinns.github.io/apsw/shell.html
>
> Behind the scenes it uses the Python CSV libraries which support a
> number of dialects including excel (default) and excel-tab.
>
> The APSW shell also supports an .autoimport command.  It automatically
> works out dialects, separators and data types.  Here is the extended help:
>
>  8< 
> sqlite> .help autoimport
>
> .autoimport FILENAME ?TABLE?  Imports filename creating a table
>   and automatically working out
>   separators and data types
>   (alternative to .import command)
>
> 
>
> Care is taken to ensure that columns looking like numbers are
> only treated as numbers if they do not have unnecessary leading
> zeroes or plus signs.  This is to avoid treating phone numbers
> and similar number like strings as integers.


I know your shell, unfortunately it is more difficult to install Python and
APSW than just bare sqlite3. Additionally it works well (see my previous
email, I was wrong, the CSV import works just fine)

however your auto import reminds me just what I faced few days ago, leading
0 are removed when opening a csv file by double clicking on it to open it
in excel.
I had to do import -> data then to go through the wizard until I setup all
columns as text, to keep the leading 0. unfortunately it led to another
issue, cell with multiple lines where badly imported. what I did, I removed
the line feed on some columns before generating the csv. not easy to have
all we need :-/

[sqlite] FTS5 Porter extra arguments not passed through

[Ralf Junker]

As per the documentation, extra arguments to the Porter stemmer are 
handed on to the underlying tokenizer:

   http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=544-546

Example syntax a few lines below:

   http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=556

However, the code does not follow the documentation and specifies 0 
instead of the argument parameters:

   http://www.sqlite.org/src/artifact/30f97a8c74683797?ln=540

Ralf

[sqlite] CSV excel import

[R.Smith]

Ha.. ignore my previous reply then. Glad it works for you!

On 2015-07-30 10:40 PM, Sylvain Pointeau wrote:
>>
>> ?I am replying to your original message rather that later ones because I'm
>> curious about the CSV file which is giving you a problem. Using the sqlite3
>> command on Linux Fedora 22 (64 bit), I get the following (transcript):
>>
>> $sqlite3
>> SQLite version 3.8.10.2 2015-05-20 18:17:19
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create table test(name1 text, name2 text, name3 text);
>> sqlite> .mode csv
>> sqlite> .import ./test.csv test
>> sqlite> .mode lines
>> sqlite> select * from test;
>> name1 = name1
>> name2 = name2
>> name3 = name3
>>
>> name1 = line1a
>> name2 = line1b
>> name3 = line1c
>>
>> name1 = line2"a
>> name2 = line2b
>> name3 = 'line2b'
>> sqlite> .quit
>> joarmc at mckown5 2015-07-30T14:43:21 ~/junk
>> $cat test.csv
>> name1,name2,name3
>> "line1a",line1b,line1c
>> "line2""a",line2b,'line2b'
>> joarmc at mckown5 2015-07-30T14:43:25 ~/junk
>
> Well ... I am glad that you are right.
>
> When I saw your message, I wondered why it didn't work for me.
>
> I have taken back my samples:
>
> $ cat test.csv
> A,B,C
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> select * from T;
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> I think this is where my mistake comes from, and when I did browse on
> internet, I saw some replies showing how to do an update to remove the
> quotes...
>
> now I should have done:
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> .mode list
> sqlite> select * from T;
> T,TI,TIT
> A,B,C,D
> 1st line
> 2nd line,E,F
> 0003,002,01
>
> so I was obviously wrong, sqlite import the csv very well, and I am glad to
> have been wrong!
>
> apologies for my mistake.
>
> Best regards,
> Sylvain
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CSV excel import

[R.Smith]

On 2015-07-30 09:05 PM, Sylvain Pointeau wrote:
> Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :
>
>> 
>>
>> The CSV importing part of the SQLite shell tool implements this very well.
>>
>> Simon.
>>
>
> No it does not implement the rule 5 correctly:
>
>   Each field may or may not be enclosed in double quotes (however
> some programs, such as Microsoft Excel, do not use double quotes
> at all).  If fields are not enclosed with double quotes, then
> double quotes may not appear inside the fields.
>
>
> Why sqlite keeps the quoted string in the database? Except of this, it
> would work well I would say

This is not correct, SQLite follows the standard correctly. Could you 
provide us with an example CSV file that fails to import correctly for you?

[sqlite] CSV excel import

[Sylvain Pointeau]

>
>
> ?I am replying to your original message rather that later ones because I'm
> curious about the CSV file which is giving you a problem. Using the sqlite3
> command on Linux Fedora 22 (64 bit), I get the following (transcript):
>
> $sqlite3
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test(name1 text, name2 text, name3 text);
> sqlite> .mode csv
> sqlite> .import ./test.csv test
> sqlite> .mode lines
> sqlite> select * from test;
> name1 = name1
> name2 = name2
> name3 = name3
>
> name1 = line1a
> name2 = line1b
> name3 = line1c
>
> name1 = line2"a
> name2 = line2b
> name3 = 'line2b'
> sqlite> .quit
> joarmc at mckown5 2015-07-30T14:43:21 ~/junk
> $cat test.csv
> name1,name2,name3
> "line1a",line1b,line1c
> "line2""a",line2b,'line2b'
> joarmc at mckown5 2015-07-30T14:43:25 ~/junk


Well ... I am glad that you are right.

When I saw your message, I wondered why it didn't work for me.

I have taken back my samples:

$ cat test.csv
A,B,C
T,TI,TIT
"A,B",C,D
"1st line
2nd line",E,F

sqlite> .mode csv
sqlite> .import test.csv T
sqlite> select * from T;
T,TI,TIT
"A,B",C,D
"1st line
2nd line",E,F

I think this is where my mistake comes from, and when I did browse on
internet, I saw some replies showing how to do an update to remove the
quotes...

now I should have done:

sqlite> .mode csv
sqlite> .import test.csv T
sqlite> .mode list
sqlite> select * from T;
T,TI,TIT
A,B,C,D
1st line
2nd line,E,F
0003,002,01

so I was obviously wrong, sqlite import the csv very well, and I am glad to
have been wrong!

apologies for my mistake.

Best regards,
Sylvain

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.98.0 have begun...

[Joe Mistachkin]

If you have any issues with the current code, please report them via this
mailing
list (and/or by creating a ticket on "https://system.data.sqlite.org/";)
prior to
Monday, August 3rd.

Thanks.

--
Joe Mistachkin

[sqlite] CSV excel import

[Simon Slavin]

On 30 Jul 2015, at 9:57pm, Sylvain Pointeau  
wrote:

> no it does not work double clicking on the csv to open it in excel, I am
> 100% sure (I just tried again), you have to go through the data->import and
> set up the columns as text for it to work

Yes, as I wrote, this bad behaviour (which you could reasonably call a bug) is 
documented.  That's how Microsoft wrote Excel and that's the way they want it 
to work, and that's how it will continue to work.

Simon.

[sqlite] CSV excel import

[Simon Slavin]

On 30 Jul 2015, at 9:50pm, Sylvain Pointeau  
wrote:

> leading
> 0 are removed when opening a csv file by double clicking on it to open it
> in excel.

This is documented behaviour in Excel, which assumes that all cells contain 
numbers, and therefore that leading zeros can be removed.  If you don't want 
them removed you have to quote the value, e.g. "0123".

Simon.

[sqlite] ATTACH Problem

[Joe Mistachkin]

Chris Parsonson wrote:
>
> 
> The first thing I need to do with the second database is ATTACH it
> to the first one. This is what I haven't been able to do successfully. 
> 
> 

Have you tried using a SQLiteParameter for the file name for the database
to attach?

--
Joe Mistachkin

[sqlite] Strange behaviour of sqlite3_stmt_busy

[gwenn]

Hello,
sqlite3_stmt_busy returns true after sqlite3_step returns DONE.

Here is the code:
#include 
#include 
#include "sqlite3.h"

int main(int argc, char **argv) {
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2("", &db, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n",
sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_exec(db, "BEGIN EXCLUSIVE", NULL, NULL, NULL);
if (SQLITE_OK != rc) {
fprintf(stderr, "Error: tx start: %s\n", sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_prepare_v2(db, "ROLLBACK", -1, &stmt, NULL);
if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_stmt_busy(stmt);
printf("%s busy before step? %d\n", sqlite3_sql(stmt), rc);
rc = sqlite3_step(stmt);
if (SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}

rc = sqlite3_stmt_busy(stmt);
printf("%s busy after step? %d\n", sqlite3_sql(stmt), rc);

rc = sqlite3_reset(stmt);
if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}

rc = sqlite3_stmt_busy(stmt);
printf("%s busy after reset? %d\n", sqlite3_sql(stmt), rc);

sqlite3_finalize(stmt);
sqlite3_close(db);
}

And the output:
ROLLBACK busy before step? 0
ROLLBACK busy after step? 1
ROLLBACK busy after reset? 0

SQLite version 3.8.10.2
Darwin Kernel Version 14.4.0

Why does sqlite3_stmt_busy return true even on stmt completion (DONE) ?

Regards.

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, John McKown  a
?crit :

> On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau <
> sylvain.pointeau at gmail.com > wrote:
>
> > On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  > wrote:
> >
> > > Actually there exists an open source tool that convert Excel data into
> > > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library
> (
> > > http://www.gdal.org/).  You do need a version of GDAL built with the
> > > SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> > > SpatiaLite into the mix, you can make your spreadsheets show up a
> virtual
> > > tables using the VirtualOGR module.
> > > Peter
> >
> >
> > really cool, but I would like to have a solution directly in the sqlite3
> > executable, so it would be available on my mac and on my windows at work
> as
> > well as everywhere where sqlite can be installed.
> >
> >
> ?I see and understand your desire. But I, personally, don't like the idea.
> I really don't want Dr. Hipp and the other developers to be using up their
> time trying to put in something that is MS specific. And then trying to
> keep it up to date with future, incompatible version of the Excel file
> format. I don't use Excel.?
>
> ?I normally use LibreOffice. And sometimes even Gnumeric.
>
> ?What might be of some, more generic, help would be if the SQLite
> executable could do an IMPORT operation from an ODBC source. This could
> address your problem because Excel, at least on Windows, supports being
> used as an ODBC target. I don't know about the Mac. The plus of this would
> be that would open up a standard interface to SQLite which could use many
> other sources such as Oracle, PostgreSQL, MariaDB (MySQL), and anything
> else which implements an ODBC source interface.?
>
>
My answer saying that I would like to have it in sqlite implied a correct
csv import. In a previous email, I stated that it would be unreasonable to
ask sqlite to have an xml parser etc.
I just need a import of csv that is working well, not more.

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :

>
> On 30 Jul 2015, at 7:48pm, Sylvain Pointeau  > wrote:
>
> > really cool, but I would like to have a solution directly in the sqlite3
> > executable
>
> If you're talking about the SQLite shell tool then Excel import will never
> be integrated into it.  There's no way to know when Microsoft is going to
> change or add to their file specification for Excel, and it would oblige
> the SQLite development team to update the shell tool on an schedule only
> Microsoft could predict.
>
> By the way, an earlier premise of this thread is incorrect.  CSV is
> perfectly standard and perfectly documented:
>
> 
>
> The CSV importing part of the SQLite shell tool implements this very well.
>
> Simon.
>


No it does not implement the rule 5 correctly:

 Each field may or may not be enclosed in double quotes (however
   some programs, such as Microsoft Excel, do not use double quotes
   at all).  If fields are not enclosed with double quotes, then
   double quotes may not appear inside the fields.


Why sqlite keeps the quoted string in the database? Except of this, it
would work well I would say

[sqlite] CSV excel import

[Sylvain Pointeau]

Le jeudi 30 juillet 2015, Scott Doctor  a ?crit :

>
> A trick that works great most of the time with ODS is when exporting to
> CSV select the option to quote all fields. One problem with CSV is that
> many exports quote strings but not numbers. If everything is quoted then it
> is much simpler to process. But would need at least several options on the
> import:
>
> 1)  what is the separator token (i.e. is it a comma, or a period, or a
> semicolon,...)
>
> 2)  what is the decimal token (i.e. is it a period, comma, other,...)
>
> 3)  Should quoted strings keep the quotes or strip the quote characters
> during processing
>
> 4)  What is the escape sequence for embedding a quote character within a
> quoted string
>
> 5)  using single or double quote character as the quote token.
>
> As you can see the number of permutations grows very fast to accommodate
> the wide variety of ways common programs handle CSV exports.
>
> On 7/30/2015 11:28 AM, Bernardo Sulzbach wrote:
>
> > I can remember two times when my life would have been easier if I
> > could throw big .ods into sqlite3 dbs.
>

I would like something that works, actually I never could use the import
csv from sqlite, I had to build my own, like probably many others. Why is
it not possible to have a solution directly in sqlite?

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  wrote:

> Actually there exists an open source tool that convert Excel data into
> SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (
> http://www.gdal.org/).  You do need a version of GDAL built with the
> SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> SpatiaLite into the mix, you can make your spreadsheets show up a virtual
> tables using the VirtualOGR module.
> Peter


really cool, but I would like to have a solution directly in the sqlite3
executable, so it would be available on my mac and on my windows at work as
well as everywhere where sqlite can be installed.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:43 PM, Adam Devita  wrote:

> Instead of trying to conform to MS-Excel's csv format, wouldn't it be
> better to write an import from .xls (or .ods if that is an open
> standard) directly?
>
> That way each cell's value can be bound to a position holder in a
> query.  No more fussing with "In this country we use this symbol to
> denote decimals", "my data has special characters or line feeds inside
> a cell" etc.
>
>
The level of effort is just not the same, you have to deal with an XML
parser and all.
CSV works also well, I had actually no problem at all handling CSV with H2,
I am just asking the same for sqlite.

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:32 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> > My point is that I have seen so many emails regarding this incorrect csv
> import, that it would be so easy for us if it just simply works in the CLI
> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
>

JSON is not an option when we are working with business people. Excel is
their only  tool to review and modify data so we need to import and export
CSV. Honestly direct excel import/export would be even better but CSV is
fine too and largely simpler.

for instance, H2 worked wonderfully well for all excel import. why is it
unreasonable to ask for the same in sqlite?

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:17 PM, Luuk  wrote:

> On 30-7-2015 20:07, Richard Hipp wrote:
>
>> On 7/30/15, Sylvain Pointeau  wrote:
>>
>>> I understood from the mailing list, that CSV is not a defined format,
>>> then
>>> let's propose another format, well defined, the Excel one (which is in my
>>> experience a format to is good every time I had to exchange CSV files).
>>>
>>> Then why don't you propose an import of CSV from Excel (or similar)?
>>> csv(excel)
>>>
>>> is it possible? in a lot of cases, I cannot use sqlite (executable)
>>> because
>>> of the lack of a good CSV import. It would really great if this could be
>>> addressed.
>>>
>>>
>> An Excel-to-SQLite converter utility sounds like it would be a great
>> open-source project.  Why don't you start it up?
>>
>>
> +1
>
> Except for the fact that "CSV is not a defined format"..
>
> It's not a defined format because:
> 1) CSV is an acornym for 'Comma Separated Values'
>
> 2) There are countries in the world which use a comma ',' as a decimal
> separator
>
> 3) Excel (or Microsoft) decided to use the ';' as a separator in case the
> decimal separator is a ','
>
>
for instance, in H2, the CSV reader works wonderfully well.
we can define in H2, but also in sqlite the column separator.

Well as I said, CSV might not be a defined format, but it is probably not
the excuse to not import it correctly. For now in sqlite, quoted text stays
quoted after the import. How do you want us to work correctly with this
result? honestly.

Best regards,
Sylvain

[sqlite] CSV excel import

[Oliver Peters]

Sylvain Pointeau  
writes:

> 
> I understood from the mailing list, that 
CSV is not a defined format, then
> let's propose another format, well 
defined, the Excel one (which is in my
> experience a format to is good every time 
I had to exchange CSV files).
> 
> Then why don't you propose an import of 
CSV from Excel (or similar)?
> csv(excel)
> 
> is it possible? in a lot of cases, I 
cannot use sqlite (executable) because
> of the lack of a good CSV import. It would 
really great if this could be
> addressed.
> 
> Best regards,
> Sylvain
> 


Have you already tried the sqlitemanager 
addon for Firefox? Very easy to import csv 
files manually. 

Oliver 

[sqlite] CSV excel import

[Sylvain Pointeau]

On Thu, Jul 30, 2015 at 8:07 PM, Richard Hipp  wrote:

> > Then why don't you propose an import of CSV from Excel (or similar)?
> > csv(excel)



>

An Excel-to-SQLite converter utility sounds like it would be a great
> open-source project.  Why don't you start it up?
> --
> D. Richard Hipp
> drh at sqlite.org


I have made a lot of utilities for sqlite, and one of them is a csv2db for
sqlite.

it is not so practical because:
- it needs to be recompile for the platform (windows in my case and I
didn't have any compiler)
- the program is external to the sqlite3 CLI, therefore we need to mix sql
script and bash scripts.

but I can give you the source code if you want, it is however written in
c++.
actually it was available in gitorious but it closed. I have to find time
to make it again available on github.

My point is that I have seen so many emails regarding this incorrect csv
import, that it would be so easy for us if it just simply works in the CLI
and delivered in standard in the sqlite3 executable.

Best regards,
Sylvain

[sqlite] CSV excel import

[Luuk]

On 30-7-2015 20:07, Richard Hipp wrote:
> On 7/30/15, Sylvain Pointeau  wrote:
>> I understood from the mailing list, that CSV is not a defined format, then
>> let's propose another format, well defined, the Excel one (which is in my
>> experience a format to is good every time I had to exchange CSV files).
>>
>> Then why don't you propose an import of CSV from Excel (or similar)?
>> csv(excel)
>>
>> is it possible? in a lot of cases, I cannot use sqlite (executable) because
>> of the lack of a good CSV import. It would really great if this could be
>> addressed.
>>
>
> An Excel-to-SQLite converter utility sounds like it would be a great
> open-source project.  Why don't you start it up?
>

+1

Except for the fact that "CSV is not a defined format"..

It's not a defined format because:
1) CSV is an acornym for 'Comma Separated Values'

2) There are countries in the world which use a comma ',' as a decimal 
separator

3) Excel (or Microsoft) decided to use the ';' as a separator in case 
the decimal separator is a ','



Defined would be:
1) All numeric values have a decimal separator (if the are decimals )
2) All text values are enclosed in double quotes (to make sure a ',' 
containted in a text is not seen as a separator. And special treatment 
is provided for the double quoot in the text.
3) Some definition might be needed about line endings (CR, CRLF, LF)

[sqlite] CSV excel import

[Sylvain Pointeau]

I understood from the mailing list, that CSV is not a defined format, then
let's propose another format, well defined, the Excel one (which is in my
experience a format to is good every time I had to exchange CSV files).

Then why don't you propose an import of CSV from Excel (or similar)?
csv(excel)

is it possible? in a lot of cases, I cannot use sqlite (executable) because
of the lack of a good CSV import. It would really great if this could be
addressed.

Best regards,
Sylvain

[sqlite] shell: better handling of multi-line commands and command history

[bitsoc...@hush.com]

I often paste in multi-line sql statements from elsewhere directly into the 
sqlite shell. But when I want to run that command again later, the command 
history only allows me to select a single line from the command history. I 
suggest using the technique described here [1] to convert the newlines in the 
command before storing them in the history.

[1] 
http://stackoverflow.com/questions/161495/is-there-a-nice-way-of-handling-multi-line-input-with-gnu-readline

[sqlite] CSV excel import

[Simon Slavin]

On 30 Jul 2015, at 7:48pm, Sylvain Pointeau  
wrote:

> really cool, but I would like to have a solution directly in the sqlite3
> executable

If you're talking about the SQLite shell tool then Excel import will never be 
integrated into it.  There's no way to know when Microsoft is going to change 
or add to their file specification for Excel, and it would oblige the SQLite 
development team to update the shell tool on an schedule only Microsoft could 
predict.

By the way, an earlier premise of this thread is incorrect.  CSV is perfectly 
standard and perfectly documented:



The CSV importing part of the SQLite shell tool implements this very well.

Simon.

[sqlite] CSV excel import

[Jim Callahan]

Another option is to save the Excel file as tab separated values. Using
tabs as delimiters avoids some of the comma issues.
Jim
On Jul 30, 2015 6:07 PM, "R.Smith"  wrote:

>
>
> On 2015-07-30 11:12 PM, Sylvain Pointeau wrote:
>
>> Le jeudi 30 juillet 2015, Simon Slavin  a ?crit :
>>
>>  Yes, as I wrote, this bad behaviour (which you could reasonably call a
>>> bug) is documented.  That's how Microsoft wrote Excel and that's the way
>>> they want it to work, and that's how it will continue to work.
>>>
>>> Simon.
>>>
>>>  There is a workaround for the leading 0, but you cannot have multi-line
>> when doing this. It is one or the other, too bad! I though generating xml
>> for excel instead of csv, but I didn't have time to try yet.
>>
>
> Something I like to point out to clients everywhere:
>
> Excel is intended (much like Calc etc.) to be a financial spreadsheet
> system, NOT a data-manipulation tool. Its Row/Column/Cell backbone simply
> lends well to the latter and so people press it into service - but that
> wasn't the design goal.
>
> On that note, if you output things to Excel in CSV (or TSV formats) and
> you have columns suffering leading zero text, you can simply Prepend an
> Equals sign.
>
> i.e. if this is your CSV:
>
> ID, Name, Age
> "00017", John, 14
> "10044", Joan, 17
> "00038", James, 16
>
> Which will import wrong losing leading zeroes, then change it to this:
>
> ID, Name, Age
> ="00017", John, 14
> ="10044", Joan, 17
> ="00038", James, 16
>
> and Excel will behave perfectly well without any added weird characters or
> the like.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] CSV excel import

[Peter Aronson]

Actually there exists an open source tool that convert Excel data into SQLite 
tables -- the ogr2ogr command line tool of OSGeo's GDAL library 
(http://www.gdal.org/). ?You do need a version of GDAL built with the SQLite 
and XLSX and/or XLS drivers. ?Actually, if you don't mind adding SpatiaLite 
into the mix, you can make your spreadsheets show up a virtual tables using the 
VirtualOGR module.
Peter 


 On Thursday, July 30, 2015 11:37 AM, Sylvain Pointeau  wrote:



 On Thu, Jul 30, 2015 at 8:32 PM, Bernardo Sulzbach <
mafagafogigante at gmail.com> wrote:

> > My point is that I have seen so many emails regarding this incorrect csv
> import, that it would be so easy for us if it just simply works in the CLI
> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
>

JSON is not an option when we are working with business people. Excel is
their only? tool to review and modify data so we need to import and export
CSV. Honestly direct excel import/export would be even better but CSV is
fine too and largely simpler.

for instance, H2 worked wonderfully well for all excel import. why is it
unreasonable to ask for the same in sqlite?
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

[Jens Miltner]

Hi,

we encountered an issue where a query that performed well using SQLite 3.8.4.1 
(i.e. execution time way below 1 second) suddenly took several seconds to 
execute.
Unfortunately, I could not yet reduce the query and database to a sample that I 
could send along and the real query / database is not something I can disclose 
to the public.

Also, I saw the query performance differ a lot between two databases 
(containing similar amounts of records in the involved tables), so it might be 
related to the correlation between the data populating the tables being used...

I can send you the query and information on the tables involved off list as 
well as "explain query", "explain query plan", etc. results - just let me know 
what you'd need (although I probably cannot send the actual database involved).

I also tried with the July 14th SQLite 3.8.11 draft 
(sqlite-amalgamation-201507141718.zip) - but I have not yet tried with the 
3.8.11 release sources - and the performance of 3.8.11 was similar to 3.8.8 for 
that query.

Running "ANALYZE" and "vacuum" also did not improve the performance for those 
DBs that took the performance hit.


Sorry for the fuzzy "bug report", I really would have liked to provide you with 
a reproducible snippet, but so far I have been unable to pin down what makes 
things go slow on certain DBs...

Let me know how I can help figuring out what's going on.

Thanks,
-jens

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

[Simon Slavin]

On 30 Jul 2015, at 5:38pm, Jens Miltner  wrote:

> I can send you the query and information on the tables involved off list as 
> well as "explain query", "explain query plan", etc. results - just let me 
> know what you'd need (although I probably cannot send the actual database 
> involved).

Posting just the "SELECT" and the "EXPLAIN QUERY PLAN" for both versions of 
SQLite will be a good initial step in allowing us to figure out what's 
happening.

> Running "ANALYZE" and "vacuum" also did not improve the performance for those 
> DBs that took the performance hit.


That's interesting and will also help the diagnosis.

Simon.

[sqlite] datetime result help

[jose isaias cabrera]

"R.Smith" wrote...
>
> On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>>
>>
>> There will be entries longer than 1 day, so I will have to address than. 
>> Thanks for the help, but this is great, though.  If I need more help, I 
>> will come back, but I have enough, and I "I think" I can figure out those 
>> longer hours then one or more days.  Thanks.
>>
>> jos?
>
> Well, it seemed fun, and probably easier to calculate in code, but this 
> SQL (SQLite specific) will do what you need for any amount of hours (even 
> hundreds of days) and for any work-day-length and work-start-time in the 
> day:
>
>
> WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>
>   5.5, -- Set this to the hours you wish to add. Any positive value is 
> fine.
>   8.5, -- Set this to the hours where the work-day starts (8:30am = 
> 8.5, 9:15am = 9.25, etc.)
>   8.5, -- Set this to how many hours makes up a workday (8:30..17:00 = 
> 8.5 hours)
>
>   (CAST(strftime('%H','now','localtime') AS 
> REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5 here 
> Same as DaySTart
>
> ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
> MAX(HrsPerDay-HrsNow,0),
> MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>   FROM DTBase
>
> ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>  CAST((HrsRemain/HrsPerDay) AS INT),
> (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>   FROM DTFill,DTBase
>
> ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
>  DaysToAdd,
> (DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE 
> HrsNow+HrsToFillToday END))
>   FROM DTBase,DTFill,DTDays
>
> )
> SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||' 
> days', '+'||DayHrsToAdd||' hours') AS NewTime
>   FROM DTTime;

Ok, I have one more option for you, and since you think it's fun, I need to 
exclude Saturdays and Sundays.  I have a hack in the programming side of 
things, but I would like to do it right from SQLite.  Thougths?

jos? 

[sqlite] Query tooooo sloooooooooow

[Clemens Ladisch]

James Qian Wang wrote:
> 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
> 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)

There is no more efficient way to execute this query.

> both very slow

What file system? What disks? Any network?


Regards,
Clemens

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Scott Hess]

Passing NULL to xOpen()'s zName parameter opens a temp file.

-scott


On Thu, Jul 30, 2015 at 4:29 PM, Howard Kapustein <
Howard.Kapustein at microsoft.com> wrote:

> >There cannot be a fully portable way, because path specifications are not
> portable
> Which begs the question, why isn't there an xGetTempFilename VFS function
> in sqlite3_vfs?
>
> Wouldn't the simplify things? Have the VFS handle it apropos, with the
> default VFS' in SQLite (or an older VFS lacking it) use the current hunt
> sequences for compat
>
> - Howard
>
> This email may contain confidential and privileged information.  Any
> unauthorized use is prohibited.  If you are not the intended recipient,
> please contact the  sender by reply email and destroy all copies of the
> original message.
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, July 28, 2015 4:31 AM
> To: General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] pragma temp_store_directory is deprecated, what is
> the alternative?
>
>
> On 28 Jul 2015, at 9:55am, Paolo Bolzoni 
> wrote:
>
> > From the C interface, the way to decide the directory is setting the
> > value of sqlite3_temp_directory char pointer. As explained here:
> > https://www.sqlite.org/c3ref/temp_directory.html
> > This is also the first place sqlite3 checks.
>
> The documentation is clear:
>
> "Applications are strongly discouraged from using this global variable. It
> is required to set a temporary folder on Windows Runtime (WinRT). But for
> all other platforms, it is highly recommended that applications neither
> read nor write this variable. This global variable is a relic that exists
> for backwards compatibility of legacy applications and should be avoided in
> new projects."
>
> Also, sqlite3_temp_directory is accessed by the VFS level, by choice of
> the VFS.  You might someday switch to another VFS which ignores it.
>
> > From anywhere else sqlite3 checks the value of those env variables:
> > SQLITE_TMPDIR, TMPDIR, TMP, TEMP, USERPROFILE.
>
> All uses of SQLite are 'the C interface'.  That's all SQLite is: C code.
> The C code checks the above places, depending on which VFS you're using,
> which usually comes down to which OS you're using.
>
> The C code checks whatever environment variable the OS expects a user to
> use to set a default location for their temporary files.  (It also checks a
> lot of other places, but it will definitely check whatever the
> documentation for the OS says is the correct environment variable.)
> Generally speaking you should let the computer's user or administrator set
> this variable, since they know which drives they have attached to the
> computer, and which one they want temporary files on. You're just a
> programmer of one application, running on a computer you've never seen.
>
> The exceptions are for embedded controllers, where the programmer is the
> administrator, and that on all platforms SQLite can create huge temporary
> files and some systems don't have that much space available for temporary
> files.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Read strings as they were

[hawk]

Here is my problem. I have strings that "looks" like a numbers. But they
aren't (at least for me).
I am aware that SQLite handles types on it's own and makes conversion to
INTEGER or REAL if it is possible (sqlite.org/datatype3.html). The problem
is that I got ".0" at every number I put into SQLite. Is it possible to
read values exactly as they were?

Example:

CREATE TABLE IF NOT EXISTS 'test' (`testcol` INTEGER);
INSERT INTO 'test' (`testcol`) VALUES ('');
SELECT * from 'test';

and I get
.0

"" is a name (not a number) for me. ".0" is also a valid "name" for
me therefore I can't truncate .0 by myself - prefer to read exact value
from SQLite.

Any help apreciated

Regards,
Krzysztof

[sqlite] Read strings as they were

[Simon Slavin]

On 30 Jul 2015, at 3:04pm, hawk  wrote:

> CREATE TABLE IF NOT EXISTS 'test' (`testcol` INTEGER);
> INSERT INTO 'test' (`testcol`) VALUES ('');
> SELECT * from 'test';
> 
> and I get
> .0

Igor pointed out one problem: you explicitly defined the column as INTEGER, 
therefore SQL is handling values as numbers.  Try again but this time use TEXT 
instead of INTEGER.

The other problem is in your programming.  I assume you are retrieving the 
value returned from the SELECT and storing it in a floating point variable or 
handling it as a floating point number.  If you use the SQL command-line shell, 
which doesn't make assumptions about values, everything works correctly.



Simon.

[sqlite] Query tooooo sloooooooooow

[James Qian Wang]

I agree.  Thank you all.



On Thu, Jul 30, 2015 at 3:44 PM, Clemens Ladisch  wrote:

> James Qian Wang wrote:
> > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
> > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)
>
> There is no more efficient way to execute this query.
>
> > both very slow
>
> What file system? What disks? Any network?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
James Qian Wang
Mobile: 44 7986 099 233

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 3:40 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

>
> Well ... I am glad that you are right.
>
> When I saw your message, I wondered why it didn't work for me.
>
> I have taken back my samples:
>
> $ cat test.csv
> A,B,C
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> select * from T;
> T,TI,TIT
> "A,B",C,D
> "1st line
> 2nd line",E,F
>
> I think this is where my mistake comes from, and when I did browse on
> internet, I saw some replies showing how to do an update to remove the
> quotes...
>
> now I should have done:
>
> sqlite> .mode csv
> sqlite> .import test.csv T
> sqlite> .mode list
> sqlite> select * from T;
> T,TI,TIT
> A,B,C,D
> 1st line
> 2nd line,E,F
> 0003,002,01
>
> so I was obviously wrong, sqlite import the csv very well, and I am glad to
> have been wrong!
>
> apologies for my mistake.
>

?Well, I made the identical mistake myself on my first try. But then I
noticed in the manual that the ".mode csv" also affected how the output
from a SELECT was displayed. That's when I tried the ".mode lines" just to
see what happened. The light then came on. ?



>
> Best regards,
> Sylvain
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[Bernardo Sulzbach]

> My point is that I have seen so many emails regarding this incorrect csv 
> import, that it would be so easy for us if it just simply works in the CLI 
> and delivered in standard in the sqlite3 executable.

I don't think I understand what you mean by this. Also, most of the
problems seems to arise from the fact that CSV is just too weakly
specified. See how better defined JSON is and how it solves a lot of
problems (not suggesting JSON here).

[sqlite] CSV excel import

[Bernardo Sulzbach]

I can remember two times when my life would have been easier if I
could throw big .ods into sqlite3 dbs. So I would also like such a
project.

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 12:58 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> I understood from the mailing list, that CSV is not a defined format, then
> let's propose another format, well defined, the Excel one (which is in my
> experience a format to is good every time I had to exchange CSV files).
>
> Then why don't you propose an import of CSV from Excel (or similar)?
> csv(excel)
>
> is it possible? in a lot of cases, I cannot use sqlite (executable) because
> of the lack of a good CSV import. It would really great if this could be
> addressed.
>
> Best regards,
> Sylvain
>

?I am replying to your original message rather that later ones because I'm
curious about the CSV file which is giving you a problem. Using the sqlite3
command on Linux Fedora 22 (64 bit), I get the following (transcript):

$sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(name1 text, name2 text, name3 text);
sqlite> .mode csv
sqlite> .import ./test.csv test
sqlite> .mode lines
sqlite> select * from test;
name1 = name1
name2 = name2
name3 = name3

name1 = line1a
name2 = line1b
name3 = line1c

name1 = line2"a
name2 = line2b
name3 = 'line2b'
sqlite> .quit
joarmc at mckown5 2015-07-30T14:43:21 ~/junk
$cat test.csv
name1,name2,name3
"line1a",line1b,line1c
"line2""a",line2b,'line2b'
joarmc at mckown5 2015-07-30T14:43:25 ~/junk
?



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[Adam Devita]

Instead of trying to conform to MS-Excel's csv format, wouldn't it be
better to write an import from .xls (or .ods if that is an open
standard) directly?

That way each cell's value can be bound to a position holder in a
query.  No more fussing with "In this country we use this symbol to
denote decimals", "my data has special characters or line feeds inside
a cell" etc.

regards,
Adam


On Thu, Jul 30, 2015 at 2:32 PM, Bernardo Sulzbach
 wrote:
>> My point is that I have seen so many emails regarding this incorrect csv 
>> import, that it would be so easy for us if it just simply works in the CLI 
>> and delivered in standard in the sqlite3 executable.
>
> I don't think I understand what you mean by this. Also, most of the
> problems seems to arise from the fact that CSV is just too weakly
> specified. See how better defined JSON is and how it solves a lot of
> problems (not suggesting JSON here).
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 2:13 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> >
> >
> My answer saying that I would like to have it in sqlite implied a correct
> csv import. In a previous email, I stated that it would be unreasonable to
> ask sqlite to have an xml parser etc.
> I just need a import of csv that is working well, not more.


?My apologies for my misunderstanding. If the CSV import is broken (in that
it does not properly import data which is properly formatted according RCF
4180, https://www.ietf.org/rfc/rfc4180.txt), I would?

?consider that a "bug fix" request.?


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[John McKown]

On Thu, Jul 30, 2015 at 1:48 PM, Sylvain Pointeau <
sylvain.pointeau at gmail.com> wrote:

> On Thu, Jul 30, 2015 at 8:44 PM, Peter Aronson  wrote:
>
> > Actually there exists an open source tool that convert Excel data into
> > SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (
> > http://www.gdal.org/).  You do need a version of GDAL built with the
> > SQLite and XLSX and/or XLS drivers.  Actually, if you don't mind adding
> > SpatiaLite into the mix, you can make your spreadsheets show up a virtual
> > tables using the VirtualOGR module.
> > Peter
>
>
> really cool, but I would like to have a solution directly in the sqlite3
> executable, so it would be available on my mac and on my windows at work as
> well as everywhere where sqlite can be installed.
>
>
?I see and understand your desire. But I, personally, don't like the idea.
I really don't want Dr. Hipp and the other developers to be using up their
time trying to put in something that is MS specific. And then trying to
keep it up to date with future, incompatible version of the Excel file
format. I don't use Excel.?

?I normally use LibreOffice. And sometimes even Gnumeric.

?What might be of some, more generic, help would be if the SQLite
executable could do an IMPORT operation from an ODBC source. This could
address your problem because Excel, at least on Windows, supports being
used as an ODBC target. I don't know about the Mac. The plus of this would
be that would open up a standard interface to SQLite which could use many
other sources such as Oracle, PostgreSQL, MariaDB (MySQL), and anything
else which implements an ODBC source interface.?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] CSV excel import

[Richard Hipp]

On 7/30/15, Sylvain Pointeau  wrote:
> I understood from the mailing list, that CSV is not a defined format, then
> let's propose another format, well defined, the Excel one (which is in my
> experience a format to is good every time I had to exchange CSV files).
>
> Then why don't you propose an import of CSV from Excel (or similar)?
> csv(excel)
>
> is it possible? in a lot of cases, I cannot use sqlite (executable) because
> of the lack of a good CSV import. It would really great if this could be
> addressed.
>

An Excel-to-SQLite converter utility sounds like it would be a great
open-source project.  Why don't you start it up?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Query tooooo sloooooooooow

[Rousselot, Richard A]

How slow is too slow?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of James Qian 
Wang
Sent: Thursday, July 30, 2015 5:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query to sloow

my desktop (windows 7)
SQLite version 3.8.7.4 2014-12-09 01:34:36 and explain query plan showed:

0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)

my linux box needs upgrade.

regardless, both very slow


On Thu, Jul 30, 2015 at 11:03 AM, Clemens Ladisch 
wrote:

> James Qian Wang wrote:
> > select count(*) from contact c left join history h on
> > (h.elid=c.elid);
>
> Please show the output of EXPLAIN QUERY PLAN for this query.
>
> If it does not look like this:
>   0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
>   0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)
> you should get a newer SQLite version.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
James Qian Wang
Mobile: 44 7986 099 233
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.

[sqlite] CSV excel import

[jungle Boogie]

On 30 July 2015 at 13:52, Simon Slavin  wrote:
>> leading
>> 0 are removed when opening a csv file by double clicking on it to open it
>> in excel.
>
> This is documented behaviour in Excel, which assumes that all cells contain 
> numbers, and therefore that leading zeros can be removed.  If you don't want 
> them removed you have to quote the value, e.g. "0123".

If you want the double quotes present in your MS Excel file, then you
can use the "01234"; otherwise, you can prefix it with the single
quote: '01234.


-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Performance regression between SQLite 3.8.4.1 and 3.8.8

[Richard Hipp]

On 7/30/15, Jens Miltner  wrote:
> Hi,
>
> we encountered an issue where a query that performed well using SQLite
> 3.8.4.1 (i.e. execution time way below 1 second) suddenly took several
> seconds to execute.
> Unfortunately, I could not yet reduce the query and database to a sample
> that I could send along and the real query / database is not something I can
> disclose to the public.
>
> Also, I saw the query performance differ a lot between two databases
> (containing similar amounts of records in the involved tables), so it might
> be related to the correlation between the data populating the tables being
> used...
>
> I can send you the query and information on the tables involved off list

Can you send the query and the original database file directly to me, please?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Query tooooo sloooooooooow

[Clemens Ladisch]

James Qian Wang wrote:
> select count(*) from contact c left join history h on (h.elid=c.elid);

Please show the output of EXPLAIN QUERY PLAN for this query.

If it does not look like this:
  0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
  0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)
you should get a newer SQLite version.


Regards,
Clemens

[sqlite] CSV excel import

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/30/2015 10:58 AM, Sylvain Pointeau wrote:
> is it possible? in a lot of cases, I cannot use sqlite (executable)
> because of the lack of a good CSV import. It would really great if
> this could be addressed.

Use the APSW shell:

  http://rogerbinns.github.io/apsw/shell.html

Behind the scenes it uses the Python CSV libraries which support a
number of dialects including excel (default) and excel-tab.

The APSW shell also supports an .autoimport command.  It automatically
works out dialects, separators and data types.  Here is the extended help:

 8< 
sqlite> .help autoimport

.autoimport FILENAME ?TABLE?  Imports filename creating a table
  and automatically working out
  separators and data types
  (alternative to .import command)

The import command requires that you precisely pre-setup the
table and schema, and set the data separators (eg commas or
tabs).  In many cases this information can be automatically
deduced from the file contents which is what this command does.
There must be at least two columns and two rows.

If the table is not specified then the basename of the file will
be used.

Additionally the type of the contents of each column is also
deduced - for example if it is a number or date.  Empty values
are turned into nulls.  Dates are normalized into -MM-DD
format and DateTime are normalized into ISO8601 format to allow
easy sorting and searching.  4 digit years must be used to detect
dates.  US (swapped day and month) versus rest of the world is
also detected providing there is at least one value that resolves
the ambiguity.

Care is taken to ensure that columns looking like numbers are
only treated as numbers if they do not have unnecessary leading
zeroes or plus signs.  This is to avoid treating phone numbers
and similar number like strings as integers.

This command can take quite some time on large files as they are
effectively imported twice.  The first time is to determine the
format and the types for each column while the second pass
actually imports the data.
 8< 

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlW6dDEACgkQmOOfHg372QRG5ACgt/OpLOPVZ40YQ4B2EWBf7yk0
HCUAniZLLUkPs7ac7cvCLb7Bn3zyNguC
=YpCt
-END PGP SIGNATURE-

[sqlite] CSV excel import

[Scott Doctor]

A trick that works great most of the time with ODS is when 
exporting to CSV select the option to quote all fields. One 
problem with CSV is that many exports quote strings but not 
numbers. If everything is quoted then it is much simpler to 
process. But would need at least several options on the import:

1)  what is the separator token (i.e. is it a comma, or a 
period, or a semicolon,...)

2)  what is the decimal token (i.e. is it a period, comma, 
other,...)

3)  Should quoted strings keep the quotes or strip the quote 
characters during processing

4)  What is the escape sequence for embedding a quote character 
within a quoted string

5)  using single or double quote character as the quote token.

As you can see the number of permutations grows very fast to 
accommodate the wide variety of ways common programs handle CSV 
exports.

On 7/30/2015 11:28 AM, Bernardo Sulzbach wrote:

 > I can remember two times when my life would have been easier if I
 > could throw big .ods into sqlite3 dbs. So I would also like 
such a
 > project.
 > ___
 > sqlite-users mailing list
 > sqlite-users at mailinglists.sqlite.org
 > 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >
 >

[sqlite] Query tooooo sloooooooooow

[James Qian Wang]

my desktop (windows 7)
SQLite version 3.8.7.4 2014-12-09 01:34:36 and explain query plan showed:

0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)

my linux box needs upgrade.

regardless, both very slow


On Thu, Jul 30, 2015 at 11:03 AM, Clemens Ladisch 
wrote:

> James Qian Wang wrote:
> > select count(*) from contact c left join history h on (h.elid=c.elid);
>
> Please show the output of EXPLAIN QUERY PLAN for this query.
>
> If it does not look like this:
>   0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2
>   0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?)
> you should get a newer SQLite version.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
James Qian Wang
Mobile: 44 7986 099 233

[sqlite] Query tooooo sloooooooooow

[James Qian Wang]

Hi All,

Here is my query:
select count(*) from contact c left join history h on (h.elid=c.elid);

Here are the table structures:
sqlite> .schema history
CREATE TABLE history (name varchar(32), email varchar(128) unique, elid
int(12));
CREATE INDEX elid on history (elid)
;
sqlite> .schema contact
CREATE TABLE contact (name varchar(32), email varchar(128) unique , elid
int(12));
CREATE INDEX elid2 on contact (elid);

Table size:
contact 20m
history  10m

elid integer and each elid has about 20k records

Ideas please?

Thanks a lot in advance
-- 
James Qian Wang
Mobile: 44 7986 099 233

[sqlite] Read strings as they were

[Igor Tandetnik]

On 7/30/2015 10:04 AM, hawk wrote:
> Here is my problem. I have strings that "looks" like a numbers. But they
> aren't (at least for me).
> I am aware that SQLite handles types on it's own and makes conversion to
> INTEGER or REAL if it is possible (sqlite.org/datatype3.html). The problem
> is that I got ".0" at every number I put into SQLite. Is it possible to
> read values exactly as they were?
>
> Example:
>
> CREATE TABLE IF NOT EXISTS 'test' (`testcol` INTEGER);

If you want the column to store text exactly as it was inserted, then 
why do you declare it with INTEGER type? Make it TEXT.

> INSERT INTO 'test' (`testcol`) VALUES ('');
> SELECT * from 'test';
>
> and I get
> .0

Can't reproduce. I get . The problem is somewhere in the part of the 
setup you haven't shown.
-- 
Igor Tandetnik

[sqlite] datetime result help

[jose isaias cabrera]

"R.Smith" wrote...
>
> On 2015-07-29 08:58 PM, jose isaias cabrera wrote:
>>
>>
>> There will be entries longer than 1 day, so I will have to address than. 
>> Thanks for the help, but this is great, though.  If I need more help, I 
>> will come back, but I have enough, and I "I think" I can figure out those 
>> longer hours then one or more days.  Thanks.
>>
>> jos?
>
> Well, it seemed fun, and probably easier to calculate in code, but this 
> SQL (SQLite specific) will do what you need for any amount of hours (even 
> hundreds of days) and for any work-day-length and work-start-time in the 
> day:
>
>
> WITH DTBase(HrsToAdd, DayStartsAt, HrsPerDay, HrsNow) AS (SELECT
>
>   5.5, -- Set this to the hours you wish to add. Any positive value is 
> fine.
>   8.5, -- Set this to the hours where the work-day starts (8:30am = 
> 8.5, 9:15am = 9.25, etc.)
>   8.5, -- Set this to how many hours makes up a workday (8:30..17:00 = 
> 8.5 hours)
>
>   (CAST(strftime('%H','now','localtime') AS 
> REAL)+(CAST(strftime('%M','now','localtime') AS REAL)/60)-8.5)-- 8.5 here 
> Same as DaySTart
>
> ), DTFill(HrsToFillToday,HrsRemain) AS (SELECT
> MAX(HrsPerDay-HrsNow,0),
> MAX(HrsToAdd-MAX(HrsPerDay-HrsNow,0),0)
>   FROM DTBase
>
> ), DTDays(DaysToAdd,DayHrsToAdd) AS (SELECT
>  CAST((HrsRemain/HrsPerDay) AS INT),
> (HrsRemain-(CAST((HrsRemain/HrsPerDay) AS INT)*HrsPerDay))
>   FROM DTFill,DTBase
>
> ), DTTime(DaysToAdd,DayHrsToAdd) AS (SELECT
>  DaysToAdd,
> (DaySTartsAt+(CASE WHEN HrsRemain>0 THEN DayHrsToAdd+24 ELSE 
> HrsNow+HrsToFillToday END))
>   FROM DTBase,DTFill,DTDays
>
> )
> SELECTdatetime( 'now', 'localtime', 'start of day', '+'||DaysToAdd||' 
> days', '+'||DayHrsToAdd||' hours') AS NewTime
>   FROM DTTime;

Well, since you did it for fun, I'll test it and let you know. ;-)  Thanks.

[sqlite] changed time zone

[Bruno Schwägli (CTModule AG)]

We use the fatastic ODBC implementation from Christian Werner 
(http://www.ch-werner.de/sqliteodbc/) with the IIS webserver.
So we don't have any control over envir variables.
I think SQLite should always use runtime information about the current time 
zone.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Igor 
Tandetnik
Gesendet: Donnerstag, 23. Juli 2015 14:41
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] changed time zone

On 7/23/2015 4:11 AM, Bruno Schw?gli (CTModule AG) wrote:
> We use SQLite in an environment (ships) where a time zone change can happen 
> during normal operation. We then set the time zone of the system (windows) 
> accordingly.
> Running SQLite instances do not respect this change. So Time('now') and  
> Time('now', 'localtime') still return the same values but in my opinion 
> Time('now', 'localtime') should respect the new time zone.

Have the host application listen to WM_SETTINGCHANGE message. When you detect 
time zone change, delete TZ environment variable (with _putenv or 
SetEnvironmentVariable ) and call _tzset
--
Igor Tandetnik

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

[sqlite] Thanks SQLite

[R.Smith]

On 2015-07-30 12:41 AM, Simon Slavin wrote:
> On 29 Jul 2015, at 11:32pm, Richard Hipp  wrote:
>
>> I'm looking for real-world (open-source) use cases for CTEs.
> I would like to see a demonstration of how non-recursive CTEs can be useful 
> in simplifying a SQL command.  Ideally in a plausible example rather than 
> something obviously made up with no real-world equivalent.  It seems that 
> everyone who mentions CTEs jumps straight to recursion, as if that's the only 
> thing they're useful for.

Well, it's hard to show a quick and simple example when the question 
specifically presupposes complexity, but if you forgive the verbosity, 
here is an example of non-recursive CTE which I don't think is even 
possible without CTE (or pre-set-up temporary tables) in a very 
real-World situation:

Basic manufacturing company has a data system comprising of stock items 
and stockkeeping units (SKU's) aka manufactured products. The method of 
turning stock into a manufactured product is commonly referred to as a Job.

Jobs have Bills of material (BOM) that basically is a stock item (sku) 
with a parent-child relationship to several other stock items which it 
"uses" or consumes to get made.

A stock item can belong to very many Bills of material, or can be used 
outside of any (such as surgical gloves that makes part of the 
consumables for a job, but isn't part of the manufactured product, 
etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's 
Engine is its own manufactured BOM unit but also a sub-unit of the Car's 
total BOM. (Recursive CTE's help us a lot in this regard for working out 
other things)

Now at any point, in your store there is an amount of stock that can be 
used, but the amount of a specific finished product that can be produced 
from it will vary widely according to BOM consumption figures. So let's 
say you have 10 of Stock X1,  20 of X2 and  5 of X3, but the product you 
want to make consumes 8 of each. You will need to buy 3 more X3 to be 
able to make even 1 of this product.

That sounds simple enough, but consider that nobody will sell you 3 more 
X3, those X3 items comes in minimum orders of 25 (we say MOQ for 
minimum-order-quantity). So if I buy a pack of X3, I can now make one 
product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of 
X3 - that is more stock than I started with!

Of course the MOQ for X1 and X2 are different, I might need to buy 30 of 
X1's and 10 of X2's.  (There is also a thing called Pan-Size / Pack-Size 
that dictates the minimum set to buy - you might need to buy a minimum 
of 30, but after that you still need to buy sets of 6, so you can buy 36 
or 42, but not 35 or 37, etc. I will ignore this bit for this example, 
but note how the complexity grows).

Then, every item has a cost. Some items are very expensive, and some are 
very cheap. I won't mind buying 50 extra of cheap item A as long as I 
use up expensive item B completely.

One common question is: How much do I need to buy of everything so that 
I can manufacture my products and have the least amount of wasted value 
left over?.

A final complication to mention is that, we only really care about 
balancing after a point - I mean it's all fine and dandy if we find that 
making 7235 jobs will use up the stock so that there is zero of 
everything left. We can't make that many jobs, we don't have the 
capacity and even if we did, nobody will buy them all. In fact, for this 
exercise, we don't really wish to make more than 20 jobs at any time.

By now I hopefully don't need to explain any more how this has become a 
really complex problem to solve. To answer the question, some CTEs can 
be called into service.

First, let's build a table with job counts in the range that we are 
comfortable with (so 0 to 20 in this case).

WITH JCount(c) AS (
   SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21
),


(Ignore the recursion here, it only builds a quick little table, it 
isn't recursing the main dataset in any way - just another way of saying 
SELECT 1 UNION ALL SELECT 2 UNION ALL.. etc etc.)

Next we will add a CTE that lists Job candidates for manufacture, 
followed by a CTE that calculates all our candidate BOMs and their stock 
needs.
I'll imagine this is a sweets factory and we're making little jelly 
things this time.
(I'm not showing the schema because it isn't important and you can 
intuit it)

WITH JCount(c) AS (
SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20
),  Candidates(BOMCode, UnitsPerBOM) AS (
SELECT StockCode, QtyPer FROM stock
WHERE Description LIKE '%jelly%'  AND StockCode IN (SELECT
ParentPart FROM BomStructure)

), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS (
   SELECT c, BOMCode, (c * UnitsPerBOM),  (QtyPer * c)
 FROM JCount, Candidates
 JOIN stock ON stock.StockCode IN (SELECT Component FROM
BomStructure WHERE ParentPart = BOMCode)
WHERE stock.QtyPer > 0

)

[sqlite] Thanks SQLite

[R.Smith]

I just wish to note something and display my gratitude (which I hope is 
shared by others) towards Richard, Dan and other SQLite devs for all the 
great functionality, but specifically CTE's.

The reason I need to say this is that I was one of the people who, when 
we first had requests for CTE's on this forum (if anyone fancies 
scrolling back), said: "C-T-E - Shmee-Tee-Eee - who would ever need that 
for real?" chimed along with others who had statements like "Anything 
you can do with CTE you can also do without it" and the like.

Today the inverse is true. What an absolute pleasure to use. Even if the 
CTE may not be strictly needed, almost every problem or complex query 
can be simplified using a CTE, and easily later factored back (removing 
the CTE) if optimization requires it. I find also explaining query 
concepts to others via a CTE much easier. I now use CTE's extensively in 
MSSQL (thanks to SQLite showing the way) and get annoyed at 
MariaDB/MySQL for not having it.

This also makes up amicably for SQLite's lack of script variables. Just 
make a CTE table full of presets to use later - problem solved.

A heartfelt Thank-you!
Ryan