[sqlite] CSV excel import

[R.Smith]

On 2015-08-01 09:28 PM, Igor Tandetnik wrote:
> 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.

Indeed so. The RFC calls for values to be "enclosed" by double quotes, 
or not. It does not specify this kind of dual value, which isn't valid 
csv (if you expect a specific result from another csv parser, that is.)

There is however no call to ignore it, and so made a great meta-data 
opportunity for me in the past. This kind of CSV was completely valid 
and would get parsed while simply ignoring the parts after the quotes:

V1, V2,  V3,   V4
1 , "John" STR,  "" NULL,  4
2 , "" NULL, "42" INT, 4
3 , "James" STR, "Smith" STR,  7

You get the idea... I could parse it with one tool and retrieve the 
"metadata", and it would  still work in Excel and other csv parsers who 
simply ignored any bits after the closing quote and before the comma.

Lately though, Excel has changed its ways and actually will input the 
entire bit between commas as if a string. So this trick of mine has 
become useless, but just an interesting digression since csv's flaws 
been brought up.

[sqlite] CSV excel import

[Jean-Christophe Deschamps]

At 18:38 01/08/2015, you wrote:

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

Exactly. All I mean is that with only very few additional strict rules 
it can be changed into a basic type compliant vehicle able to reliably 
transfer data between many applications, including SQLite. A 
spreadsheet internally differentiates between a string, a number and an 
empty cell, for every cell, so the text output format should clearly do 
the same, unambiguously IMHO.

>   Neither, for that matter, does it store Integers or Reals as you go 
> on to mention - It is completely typeless (moreso than SQLite).

You know very well that SQLite is far from being typeless. What you put 
in you get out thru the kaleidoscope of affinities. Its storage classes 
are perfectly defined and obey precise rules.

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

True, but the issue with most variants of CSV files floating around is 
just that: it's the reader to decide if 12345.4890E-13 is a float or a 
string (for instance the reference of some item by a given supplier.) 
By forcing string delimiters, you gain at least SQLite type resilience 
in/out. If you follow the RFC by the letter and only use string 
delimiters when a string actually contains the delimiter, you loose the 
capacity to unambiguously determine basic types.

By adopting a set of simple rules, you can reliably import/export data 
blindly into/from SQL (and outside as well) without loosing basic type 
information even at the individual field level. Of course here I mean 
all SQLite datatypes, which is what we're talking about in this list.

>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
  ^ ??? (fixed font required)
An error there: this isn't valid CSV under all variants I know of. At 
the limit, the grammar can be enhanced (or bastardized?) to allow for 4 
being considered a comment, but I never had any incentive to handle 
this kind of cosmetic variation. Even in that case, then "6" should 
also being seen as a comment but this is going far beyond what I (and 
most people) need and routinely use.


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

Exactly: I don't pretend to have invented warm water, just decided for 
an easy to implement variant which fixes most of the issues with CSV 
being initially type-blind.

Additionally, I enforce UTF8 no BOM encoding and allow unsignificant 
Unicode horizontal whitespaces before and after field separators as 
well as at begin and end of line.

Obviously if one needs to use a format offering more complete 
semantics, then JSON or XML are there for use, albeit at significantly 
higher cost.

[sqlite] CSV excel import

[Simon Slavin]

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

> At 18:38 01/08/2015, you wrote:
> 
>> 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.
> 
> Exactly. All I mean is that with only very few additional strict rules it can 
> be changed into a basic type compliant vehicle able to reliably transfer data 
> between many applications, including SQLite.

Why make any changes at all ?  Just decide that all values are in JSON format 
and you don't have to violate any standards.



Simon.

[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] 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 <sc...@scottdoctor.com>
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 <aperi2...@gmail.com>
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] 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] 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] CSV excel import

[Bernardo Sulzbach]

> =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] CSV excel import

[Bart Smissaert]

Never considered that option but I think it will be a lot slower.
Currently I work with these 2:
https://sqliteforexcel.codeplex.com/
http://www.vbrichclient.com/#/en/About/

RBS

On Fri, Jul 31, 2015 at 1:47 AM, Simon Slavin  wrote:

>
> On 31 Jul 2015, at 1:32am, Bart Smissaert 
> wrote:
>
> > Moving data from Excel directly to SQLite seems the best option to me as
> > all the values are clearly separated.
> > No need for XML. An Excel sheet range can be directly converted to a
> > variant array and from there it is simple and
> > fast to move the data to SQLite. I have written an Excel add-in that does
> > this.
>
> Had you considered using Excel to write SQL commands ?  You can use Excel
> functions like
>
> =CONCATENATE("INSERT INTO myTable VALUES (",a1,","a2");")
>
> which will give you a column of SQL commands.  You can then select and
> copy this column of commands and paste them into the SQL shell tool.
>
> (Above is simplified and you may need to get more complicated to paste
> your values.)
>
> 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

[Don V Nielsen]

I use the Ruby scripting language and its abundance of libraries to read
excel files then write the output to sqlite.  Those libraries, whether they
r/w csv or excel files are robust, sometime robust beyond belief.  They are
likely more robust than what applications developers write into their
apps.  The libraries are maintained, and can be enhanced by the open source
community.  Lots of good stuff out there.

Sqlite is a db is awesome.  Let's leave the sqlite guys do what the sqlite
guys do best.


On Thu, Jul 30, 2015 at 2:47 PM, John McKown 
wrote:

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

[sqlite] CSV excel import

[Simon Slavin]

On 31 Jul 2015, at 1:32am, Bart Smissaert  wrote:

> Moving data from Excel directly to SQLite seems the best option to me as
> all the values are clearly separated.
> No need for XML. An Excel sheet range can be directly converted to a
> variant array and from there it is simple and
> fast to move the data to SQLite. I have written an Excel add-in that does
> this.

Had you considered using Excel to write SQL commands ?  You can use Excel 
functions like

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

which will give you a column of SQL commands.  You can then select and copy 
this column of commands and paste them into the SQL shell tool.

(Above is simplified and you may need to get more complicated to paste your 
values.)

Simon.

[sqlite] CSV excel import

[Bart Smissaert]

Moving data from Excel directly to SQLite seems the best option to me as
all the values are clearly separated.
No need for XML. An Excel sheet range can be directly converted to a
variant array and from there it is simple and
fast to move the data to SQLite. I have written an Excel add-in that does
this.

RBS

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

> 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-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 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] 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] 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] 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] 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] 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] 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] 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] 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
 >
 >