[sqlite] datetime result help

[R.Smith]

On 2015-07-31 03:37 PM, jose isaias cabrera wrote:
>
> How much I owe you? :-)  The holidays was the next question. ;P 
> Thanks. This will help me understand CTE much deeper/better. Thanks.
>

The help is a pleasure, but those CTEs were hacked together to solve the 
date-time conundrum you've posted - Please don't use them as educational 
material!

I'm not aware of any great CTE tutorials for SQLite on the web, though 
there must be some, and other readers may offer links to them.

We've added a few tutorial-like demos to the script editor for the 
upcoming public release of SQLitespeed 2 (still  in final BETA, release 
expected late August) for doing fun stuff like drawing Sine/Cosine 
graphs, Mandelbrot sets and reading/expanding CSV data in a column - but 
I think a real basic tutorial will help a lot of people more.

I'll post some of those tutorials here if anyone is interested, and I 
hope someone could add some nice web tutorials/resources because CTEs 
are just brilliant in making life easier and everyone could / should 
benefit from it.  If interested in checking these out in the beta - it 
can be accessed from here: www.sqlc.rifin.co.za/ but I advise hanging on 
for the public release.


Two great informational pieces can be found here (although this is SQL 
SERVER based CTE discussions, I think the articles are accessible to all):

http://facility9.com/2008/12/a-quick-introduction-to-common-table-expressions/

https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

The former contains a host of reasons why CTEs are better to use and 
both give examples of practical non-recursive CTEs.


Cheers,
Ryan

[sqlite] CSV excel import

[R.Smith]

On 2015-08-01 05:42 PM, Jean-Christophe Deschamps wrote:
>
> At 16:36 01/08/2015, Igor wrote:
> 
>> There are many real problems with CSV - no need to make up imaginary 
>> ones.
> `---
> Indeed and I'm surprised noone mentionned this from the very start of 
> this thread: Nulls. There is no real provision in the RFC to represent 
> Null, or distinguish Null from an empty string.

Nobody mentions it because it is as irrelevant as bemoaning the fact 
that CSV cannot store lawn-chairs or Java objects. It wasn't intended to 
do so.  Neither, for that matter, does it store Integers or Reals as you 
go on to mention - It is completely typeless (moreso than SQLite). It 
stores only one single thing: Strings. It has only one single guide: How 
to correctly add /the string/ to a row and column and how to read it 
back. How you interpret those strung-together characters is up to the 
composer/decomposer (as Simon mentioned) - the CSV standard has no 
feelings about it.

For extra fun - How must a value that are both in and not in quotes be 
interpreted? i.e if I have this csv line, what values must the parser 
end up with?:

1, "2", "3" 4, 5 "6", 7


> I've long established my own convention to transfer data between the 
> language I use most (AutoIt), CSV files and SQLite and I'm very happy 
> with that.

i.e. You've made your own file specification using the CSV standard as a 
kick-off point.
Nice job though!

[sqlite] CSV excel import

[Jean-Christophe Deschamps]

At 16:36 01/08/2015, Igor wrote:

>There are many real problems with CSV - no need to make up imaginary ones.
`---

Indeed and I'm surprised noone mentionned this from the very start of 
this thread: Nulls. There is no real provision in the RFC to represent 
Null, or distinguish Null from an empty string.

I've long established my own convention to transfer data between the 
language I use most (AutoIt), CSV files and SQLite and I'm very happy 
with that.

Let's look at which datatypes we have to deal with, at least for SQLite:
   o) string
   o) integer
   o) real
   o) binary
   o) Null   <-- that's very important and most often forgoten!

Within my humble convention, a CSV field is either:
   o) a string delimited by a parametrable delimiter (double quote by 
default) where embedded delimiters are escaped by doubling
   o) an integer, optionally signed
   o) a real (e.g. 3.1415926 or -5.6e-12)
   o) a binary in 0x0123456789abcdef form
   o) an empty field or a field containing Null (w/o delimiter) 
represents Null

Fields are separated by separators (comma by default).
Rows are terminated by any Unicode line termination (defined by PCRE 
BSR_ANY).
An optional header may be present with column names as a series of 
string types.

That deals with SQLite datatypes. I've additionnally accept datatypes 
from/to AutoIt to/from SQLite (some of them don't make much sense 
storing in a DB, but anyway):
   o) a field containing True or False (w/o delimiter) represents 1 or 
0 as a boolean
   o) a field containing "<-~Default~->" represents the keyword Default
   o) function references are encoded as strings in the format 
"<-(myFunc)->"
   o) pointers are encoded as strings in the format "<-*0123456789"*->"
   o) Windows handles are encoded in the form "<-&0123456789&->"
   o) a C-style structure is encoded by "<-{0123456789ABCDEF}->"
and the following more exotic ones (their actuel content is not stored):
   o) a COM object is denoted by the format "<- at objectName@->"
   o) an array is denoted by the format "<-[3][4]...[2]->"
   o) all other unrepresentable variants are denoted by "<-?variantType?->"

I agree that the convention of using strings like "<- ... ->" is 
questionable and is merely a hint that some variable(s) should have 
been handled otherwise, but if one focuses only on SQLite (and Excel or 
such) datatypes, the format is definitely unambiguous and easily parsed 
by a single regexp and encoded with minimum effort.
The benefit of representing datatypes unambiguously in round-trip 
operations should be obvious.

Granted that doesn't solve the direct import from Excel CSV or some 
other data source using its own "standard", but I prefer having to code 
the few lines of AutoIt needed to grab data directly from Excel or 
other COM objects and format the data according to my own convention 
than rely on variable formats found around.

Again, the issue of not providing a clear way to represent SQL Null is 
a big hole in the RFC (I understand it was not the purpose at the time 
it was issued and the fact that it was too late to enforce something 
better). Also the optional string delimiter makes it essentially 
impossible to distinguish the integer value 1234 from the string 
"0001234", another issue that needs addressing. 

[sqlite] CSV excel import

[Simon Slavin]

On 1 Aug 2015, at 4:42pm, Jean-Christophe Deschamps  wrote:

> Indeed and I'm surprised noone mentionned this from the very start of this 
> thread: Nulls. There is no real provision in the RFC to represent Null, or 
> distinguish Null from an empty string.

Just to deambiguate that, the first is true, not the second.  There's no way to 
specify NULL in a CSV file, just like there's no way to specify a number.  An 
empty string is a value of zero characters, not NULL.

Values in CSV files are not typed.  A string of characters is a value.  It's up 
to the reading program to know whether it wants a string or a number or 
possibly a NULL, and what to look for to make sure that the value can be 
interpreted like that.  If your reading program wants to interpret NULL as NULL 
and "NULL" as a string, that's not in contradiction of the CSV file format.

Simon.

[sqlite] CSV excel import

[Simon Slavin]

On 1 Aug 2015, at 3:25pm, Bernardo Sulzbach  
wrote:

>> P.S. As Simon noted, it seems to be defined here: 
>> 
> 
>> I was probably referring to the first sentence of chapter 2: "While there 
>> are various specifications and implementations for the CSV format"
> 
> OK for that document existing and all, but just a quotes:
> 
>  "Each field _may or may not_ be enclosed in double quotes"

Read the next sentence of section 2.5, which says

"If fields are not enclosed with double quotes, then
   double quotes may not appear inside the fields."

> So if I want to represent "" I can use either "" or "".

No.  In addition to the above statement in Section 2.5, Section 2.6 says

"Fields containing line breaks (CRLF), double quotes, and commas
   should be enclosed in double-quotes."

[sqlite] datetime result help

[jose isaias cabrera]

"R.Smith" wrote...
>
> On 2015-07-31 03:37 PM, jose isaias cabrera wrote:
>>
>> How much I owe you? :-)  The holidays was the next question. ;P Thanks. 
>> This will help me understand CTE much deeper/better. Thanks.
>>
>
> The help is a pleasure, but those CTEs were hacked together to solve the 
> date-time conundrum you've posted - Please don't use them as educational 
> material!
>
> I'm not aware of any great CTE tutorials for SQLite on the web, though 
> there must be some, and other readers may offer links to them.
>
> We've added a few tutorial-like demos to the script editor for the 
> upcoming public release of SQLitespeed 2 (still  in final BETA, release 
> expected late August) for doing fun stuff like drawing Sine/Cosine graphs, 
> Mandelbrot sets and reading/expanding CSV data in a column - but I think a 
> real basic tutorial will help a lot of people more.
>
> I'll post some of those tutorials here if anyone is interested, and I hope 
> someone could add some nice web tutorials/resources because CTEs are just 
> brilliant in making life easier and everyone could / should benefit from 
> it.  If interested in checking these out in the beta - it can be accessed 
> from here: www.sqlc.rifin.co.za/ but I advise hanging on for the public 
> release.
>
>
> Two great informational pieces can be found here (although this is SQL 
> SERVER based CTE discussions, I think the articles are accessible to all):
>
> http://facility9.com/2008/12/a-quick-introduction-to-common-table-expressions/
>
> https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
>
> The former contains a host of reasons why CTEs are better to use and both 
> give examples of practical non-recursive CTEs.

Thanks. Ryan.  Wow, your tool is pretty amazing.  I did not know it existed. 
The site is also very nice.  Thank for the CTE links.  I am a newbie to SQL, 
and I started with SQLite for a project for work in 2006 and now it has 
become bigger than I wanted/thought, which is pushing me to learn more and 
more about this fancy world of SQL. :-)  Again, thanks for all the help.

jos?

[sqlite] CSV excel import

[Ben Newberg]

+1 for the use of R for this task. I use the below if the file is in XLSX
format:

library(sqldf)
library(openxlsx)
df <- read.xlsx("mytable.xlsx", sheet=1, startRow=1, colNames=TRUE)
db <- dbConnect(SQLite(), "mydatabase.db")
dbWriteTable(db, "mytable", df)

On Sat, Aug 1, 2015 at 8:13 AM, Gabor Grothendieck 
wrote:

> Here is how to do it in R.  Download, install and start R and then paste
> the following code into R.
>
> Uncomment the first line (the line starting with  a hash) if you don't
> already have sqldf installed.  This not only installs sqldf but also the
> RSQLite driver and SQLite itself.
>
> The code assumes that mytable.csv is the input file, DF is the table name
> to create and db  is the name of the SQLite database to use (or create).
> Change these as needed.   It will use the first line of the input file as
> the column names and will automatically determine the types of  columns by
> examining the first few data rows of the input.
>
> # install.packages("sqldf")
>
> library(sqldf)
> DF <- read.csv("mytable.csv")
> sqldf(c("attach db as new", "create table new.DF as select * from DF"))
>
>
> On Thu, Jul 30, 2015 at 1: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
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] CSV excel import

[Jean Chevalier]

Or use read.csv() followed by dbWriteTable from package RSQLite.

Or read.DIF() as the case may be (the DIF format for spreadsheets being simpler 
than XLS).


Today, Gabor Grothendieck wrote:
>
> Here is how to do it in R.  Download, install and start R and then
> paste the following code into R.
>
> Uncomment the first line (the line starting with  a hash) if you
> don't already have sqldf installed.  This not only installs sqldf
> but also the RSQLite driver and SQLite itself.
>
> The code assumes that mytable.csv is the input file, DF is the table
> name to create and db  is the name of the SQLite database to use
> (or create).  Change these as needed.   It will use the first line
> of the input file as the column names and will automatically
> determine the types of  columns by examining the first few
> data rows of the input.
>
> # install.packages("sqldf")
>
> library(sqldf)
> DF <- read.csv("mytable.csv")
> sqldf(c("attach db as new", "create table new.DF as select * from DF"))
>
>

[sqlite] CSV excel import

[Igor Tandetnik]

On 8/1/2015 12:38 PM, R.Smith wrote:
> if I have this csv line, what values must the parser end up with?:
>
> 1, "2", "3" 4, 5 "6", 7

This is not a valid line of CSV, at least not as specified in RFC 4180. 
Therefore, RFC 4180-conforming parsers may differ in their 
interpretation of this line. There is no particular set of values that 
the parser "must" end up with, assuming you use the word "must" with the 
meaning specified in RFC 2119.
-- 
Igor Tandetnik

[sqlite] CSV excel import

[Simon Slavin]

> On 1 Aug 2015, at 2:09am, Bernardo Sulzbach  
> wrote:
> 
>> =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");")
> 
> This is so neat. Supposing you are not migrating from a spreadsheet
> because it got too big (millions of rows will take a time for this to
> finish) and that your data respects a logical grouping, this is very
> handy.

Glad you like it.  I stumbled on it when I had a spreadsheet where lots of 
columns needed a little more processing before they were suitable for a .csv 
file.  There was one particularly annoying text field which contained 
line-ends, apostrophes and quotes and I couldn't get it to work right in the 
.csv export/import.  I eventually realised that instead of making a .csv file I 
could make a .sql file with the IMPORT commands in.  Make a column of 
CONCATENATE formulae as above, select and copy the column of results and paste 
them into a text file, and I was done.

I missed out two commas in the example I posted above.  It should be more like

=CONCATENATE("INSERT INTO myTable VALUES (",a1,",",a2,");")

Simon.

[sqlite] CSV excel import

[Edward Lau]

Scott is right in the myriads of combination in handling csv/text file.  Check 
out "Text File Reader for SQLite" at https://github.com/elau1004/TFR4SQLite/wiki


Hope it meet some or all the needs dealing with importing text file.  Since it 
is implemented as virtual table, one need to select the text and insert it into 
a table.  One can specify conversion so that you can define your own 
interpretation of "NULL".




-Original Message-
From: Scott Doctor 
To: General Discussion of SQLite Database 
Sent: Thu, Jul 30, 2015 11:53 am
Subject: Re: [sqlite] CSV excel import



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

[sqlite] CSV excel import

[Edward Lau]

Along the lines virtual table, check out "Text File Reader for SQLite" at 
https://github.com/elau1004/TFR4SQLite/wiki


Hope it meet some or all the needs dealing with importing text. file  Since it 
is virtual table, one need to select the text and insert it into a table.



-Original Message-
From: Andrea Peri 
To: General Discussion of SQLite Database 
Sent: Sat, Aug 1, 2015 2:47 am
Subject: Re: [sqlite] CSV excel import


Toimport a text into a sqlite:

this link could be
useful:

http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html

CREATE
VIRTUAL TABLE books USING VirtualText(books.txt, CP1252, 1,
COMMA, DOUBLEQUOTE,
',');

Regards,

A.


2015-08-01 11:43 GMT+02:00 Andrea Peri
:
> Hi,
> to import from excel and from csv I use the
spatialite extension for sqlite.
>
> With that extension, I import directly an
excel table, and to import the csv,
> using that extension is possibile tohave a
virtual table for text and csv.
>
https://www.gaia-gis.it/fossil/libspatialite/index
>
> In the same suite there
also the freexl extension.
> https://www.gaia-gis.it/fossil/freexl/index
>
>
Regards,
>
> A.
>
>
> 2015-07-30 19:58 GMT+02:00 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-users
mailing list
>> sqlite-users at mailinglists.sqlite.org
>>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
--
> -
> Andrea Peri
> . . . . . . . . .
> qwerty ?
>
-



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

[sqlite] FTS5 Porter extra arguments not passed through

[Dan Kennedy]

On 07/31/2015 03:48 AM, Ralf Junker wrote:
> 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

Thanks for reporting this, and for the other ones earlier. Now fixed here:

   http://www.sqlite.org/src/info/c3c672af97edf2ae

Dan.

[sqlite] CSV excel import

[Andrea Peri]

Toimport a text into a sqlite:

this link could be useful:

http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html

CREATE VIRTUAL TABLE books USING VirtualText(books.txt, CP1252, 1,
COMMA, DOUBLEQUOTE, ',');

Regards,

A.


2015-08-01 11:43 GMT+02:00 Andrea Peri :
> Hi,
> to import from excel and from csv I use the spatialite extension for sqlite.
>
> With that extension, I import directly an excel table, and to import the csv,
> using that extension is possibile tohave a virtual table for text and csv.
> https://www.gaia-gis.it/fossil/libspatialite/index
>
> In the same suite there also the freexl extension.
> https://www.gaia-gis.it/fossil/freexl/index
>
> Regards,
>
> A.
>
>
> 2015-07-30 19:58 GMT+02:00 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-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> -
> Andrea Peri
> . . . . . . . . .
> qwerty ?
> -



-- 
-
Andrea Peri
. . . . . . . . .
qwerty ?
-

[sqlite] CSV excel import

[Andrea Peri]

Hi,
to import from excel and from csv I use the spatialite extension for sqlite.

With that extension, I import directly an excel table, and to import the csv,
using that extension is possibile tohave a virtual table for text and csv.
https://www.gaia-gis.it/fossil/libspatialite/index

In the same suite there also the freexl extension.
https://www.gaia-gis.it/fossil/freexl/index

Regards,

A.


2015-07-30 19:58 GMT+02:00 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
-
Andrea Peri
. . . . . . . . .
qwerty ?
-

[sqlite] CSV excel import

[Bernardo Sulzbach]

> P.S. As Simon noted, it seems to be defined here: 
> 

> I was probably referring to the first sentence of chapter 2: "While there are 
> various specifications and implementations for the CSV format"

OK for that document existing and all, but just a quotes:

  "Each field _may or may not_ be enclosed in double quotes"

So if I want to represent "" I can use either "" or "".
Noting that if I cannot tell the parser if I am using quotes or not
"" becomes either "" or "". And if I need to use a crlf in
there, quotes are mandatory. So it may or may not be enclosed but must
be in some scenarios. Fantastic.

This kind of BS (at least to me) reduces the relevance of a "definition".
It also may or may not have headers, but that's a smaller issue.

[sqlite] CSV excel import

[Luuk]

On 30-7-2015 20:31, Sylvain Pointeau wrote:
> 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:
>>> 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"..
>>

>
> Well as I said, CSV might not be a defined format,

> but it is probably not the excuse to not import it correctly.

So, you are going to implment a 'correct' import, on a format that 
'might no be defined' 


P.S. As Simon noted, it seems to be defined here:


I was probably referring to the first sentence of chapter 2:
"While there are various specifications and implementations for the CSV 
format"

[sqlite] CSV excel import

[Igor Tandetnik]

On 8/1/2015 10:25 AM, Bernardo Sulzbach wrote:
>> P.S. As Simon noted, it seems to be defined here: 
>> 
>
>> I was probably referring to the first sentence of chapter 2: "While there 
>> are various specifications and implementations for the CSV format"
>
> OK for that document existing and all, but just a quotes:
>
>"Each field _may or may not_ be enclosed in double quotes"
>
> So if I want to represent "" I can use either "" or "".

False. You have conveniently neglected to mention the very next sentence 
in the document: "If fields are not enclosed with double quotes, then 
double quotes may not appear inside the fields."

Thus, "" is unambiguously an empty string, and "" is unambiguously a 
string consisting of two double quotes.

There are many real problems with CSV - no need to make up imaginary ones.
-- 
Igor Tandetnik

[sqlite] CSV excel import

[Gabor Grothendieck]

Here is how to do it in R.  Download, install and start R and then paste
the following code into R.

Uncomment the first line (the line starting with  a hash) if you don't
already have sqldf installed.  This not only installs sqldf but also the
RSQLite driver and SQLite itself.

The code assumes that mytable.csv is the input file, DF is the table name
to create and db  is the name of the SQLite database to use (or create).
Change these as needed.   It will use the first line of the input file as
the column names and will automatically determine the types of  columns by
examining the first few data rows of the input.

# install.packages("sqldf")

library(sqldf)
DF <- read.csv("mytable.csv")
sqldf(c("attach db as new", "create table new.DF as select * from DF"))


On Thu, Jul 30, 2015 at 1: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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

[sqlite] Can you enable Recursive Triggers via the connection string?

[Mark A. Donohoe]

I hope this is the right address to send my questions to.

I found this mailing list on the SQLite.org site under the 
?Support? section.

In short, can you enable Recursive Triggers via the connection string?

I?ve read in some places that you can specify PRAGMA statements on the 
connection string, but that doesn?t appear to be the case, at least not as far 
as Recursive Triggers go.

The problem is we have logic that depends on them, but we?re using frameworks 
which open and close the connections automatically so it?s not easy for us to 
manually execute the PRAGMA statement at that time.

Here?s my question on StackOverflow.com regarding it.

http://stackoverflow.com/questions/30457501/in-sqlite-can-you-set-the-recursive-triggers-pragma-via-the-connection-string

As you can see, I haven?t received a single comment in over a month, let alone 
an answer:

I?m starting to think our only course of action would be to manually recompile 
the DLL but that seems like it would be opening a giant can of worms for a 
simple feature.

Thanks in advance for your help.  I?m hoping this is a simple ?Yes, here?s how? 
or ?No, it can?t be done.?

Mark

[sqlite] CSV excel import

[Keith Medcalf]

Python with either xlrd or openpyxl.  Why use a multiplicity when one 
programming environment will do?

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Bernardo Sulzbach
> Sent: Friday, 31 July, 2015 21:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] CSV excel import
> 
> > =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");")
> 
> This is so neat. Supposing you are not migrating from a spreadsheet
> because it got too big (millions of rows will take a time for this to
> finish) and that your data respects a logical grouping, this is very
> handy.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

[Dan Kennedy]

On 07/31/2015 09:51 PM, Hinrichsen, John wrote:
> Update:
>
> I don't have a problem compiling under centos 7 (gcc 4.8.3), but with
> centos 6 (gcc 4.4.7) I do get this error.
>
> fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
> fts5Int.h:83: note: previous declaration of 'Fts5Global' was here
>
> Unfortunately, I still have to support centos 6.

Thanks for this. Now fixed here:

   http://www.sqlite.org/src/info/54a771fe2c2b3c1c

Regards,
Dan.




>
>
> On Mon, Jul 27, 2015 at 4:16 PM, Hinrichsen, John 
> wrote:
>
>> This was the error I got:
>>
>> fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
>>
>>
>> On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy 
>> wrote:
>>
>>> On 07/28/2015 02:55 AM, Hinrichsen, John wrote:
>>>
 Hi,

 I was not able to get the fts5 module to build from the versioned source
 tarball for this release (
 http://www.sqlite.org/2015/sqlite-src-3081100.zip
 ).

>>> Which step failed?
>>>
>>>
>>>
>>>
 I was able to 'make fts5.c' following the instructions that reference the
 "trunk" tarball.

 Regards,
 John Hinrichsen


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