[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

+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

[sqlite] CSV excel import

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.

[sqlite] CSV excel import

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.

[sqlite] CSV excel import

> 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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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 s

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

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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 >

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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 >

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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)?

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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 >

[sqlite] CSV excel import

> 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

[sqlite] CSV excel import

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

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[sqlite] CSV excel import

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

[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