Re: [sqlite] Tedious CSV import question

Emyr Thomas wrote: > On Oct 6, 12:50 pm, Pavel Ivanov wrote: >> I think you need to change your last compilation command line to this: >> >> gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib >> -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o > > That

Re: [sqlite] Tedious CSV import question

On Tue, Oct 06, 2009 at 07:50:52AM -0400, Pavel Ivanov scratched on the wall: > I think you need to change your last compilation command line to this: > > gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib > -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o Neither

Re: [sqlite] Tedious CSV import question

On Oct 6, 12:50 pm, Pavel Ivanov wrote: > I think you need to change your last compilation command line to this: > > gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib > -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o That didn't quite do it, but after

Re: [sqlite] Tedious CSV import question

I think you need to change your last compilation command line to this: gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o Pavel On Tue, Oct 6, 2009 at 6:16 AM, Emyr Thomas wrote: > On Oct 6, 10:52 

Re: [sqlite] Tedious CSV import question

On Oct 6, 11:16 am, Emyr Thomas wrote: > Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/ > local/include and -L/opt/local/lib to the make file. It gets further, > but now I get the following error: Of course I meant -I/opt/local/include, not

Re: [sqlite] Tedious CSV import question

On Oct 6, 10:52 am, Dan Kennedy wrote: > You have the most recent version installed. But perhaps the pre- > processor > is finding an older sqlite3.h. Try adding -I/usr/local/include to the > gcc command. Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/

Re: [sqlite] Tedious CSV import question

On Oct 6, 2009, at 4:40 PM, Emyr Thomas wrote: > On Oct 5, 8:09 pm, Nuno Lucas wrote: >> Emyr Thomas wrote: >>> Could you please provide some info as to how you would build this as >>> an extension module on Linux? >> >> "make" ? >> >> It would be easier if you stated why

Re: [sqlite] Tedious CSV import question

On Oct 5, 8:09 pm, Nuno Lucas wrote: > Emyr Thomas wrote: > > Could you please provide some info as to how you would build this as > > an extension module on Linux? > > "make" ? > > It would be easier if you stated why that doesn't work for you. Apologies, my question was very

Re: [sqlite] Tedious CSV import question

Roger Binns wrote: > Nuno Lucas wrote: >> (in fact, the code doesn't compile using the bundled >> 3.4.2 version on my Ubuntu 8.04 machine). > > Are you sure it is the virtual table api that is the problem and not some > other ones. I haven't changed my virtual table code in years - the last >

Re: [sqlite] Tedious CSV import question

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nuno Lucas wrote: > All I can say is that the virtual table API is still considered > experimental (or was last time i checked), so it's not stable across > sqlite versions Virtual tables were added mid-2006 (SQLite 3.3.7). 3.3.8 had an

Re: [sqlite] Tedious CSV import question

Emyr Thomas wrote: > On Sep 27, 6:35 pm, Nuno Lucas wrote: >> Some time ago I had to do something similar and decided to write a small >> virtual table implementation to treatCSVfiles as just another table. >> This works for my uses which is to import Excel and OpenOffice

Re: [sqlite] Tedious CSV import question

On Sep 27, 6:35 pm, Nuno Lucas wrote: > Some time ago I had to do something similar and decided to write a small > virtual table implementation to treatCSVfiles as just another table. > This works for my uses which is to import Excel and OpenOffice files. > > I cleaned up the

Re: [sqlite] Tedious CSV import question

On Mon, Sep 28, 2009 at 12:13 PM, Petite Abeille wrote: > > On Sep 27, 2009, at 7:35 PM, Nuno Lucas wrote: > >> Some time ago I had to do something similar and decided to write a >> small >> virtual table implementation to treat CSV files as just another table. > > Very

Re: [sqlite] Tedious CSV import question

On Sep 27, 2009, at 7:35 PM, Nuno Lucas wrote: > Some time ago I had to do something similar and decided to write a > small > virtual table implementation to treat CSV files as just another table. Very nice :) Similar to Oracle's "external table": "External Tables"

Re: [sqlite] Tedious CSV import question

@Nuno: hey that is really nice. Thank you! On 9/27/09, Nuno Lucas wrote: > C. Mundi wrote: >> I'm hoping someone will (please) tell me I missed something in the sqlite >> docs. Otherwise, I guess I'll be using python's csv module to turn my CSV >> file into SQL insert

Re: [sqlite] Tedious CSV import question

C. Mundi wrote: > I'm hoping someone will (please) tell me I missed something in the sqlite > docs. Otherwise, I guess I'll be using python's csv module to turn my CSV > file into SQL insert statements. This is likely to be an infequent task, > but it has to be done perfectly. So if someone

Re: [sqlite] Tedious CSV import question

I recently had to import an Excel based database into an SQL database. To complicate the matters data was all over the place in each file (3 separate tables in a single CSV file), and some numbers had commas (e.g. 8,253.45). I used the Perl CSV module to read in each file (1500+) line at a

Re: [sqlite] Tedious CSV import question

Hi, Please send me a copy if you would. Been looking at doing this exact same thing! -Joe On Thu, Sep 24, 2009 at 7:42 PM, C. Mundi wrote: > On Thu, Sep 24, 2009 at 12:37 PM, C. Mundi wrote: > >> >> Hi.  I have scanned the list archives and Googled.  I

Re: [sqlite] Tedious CSV import question

On Sep 25, 2009, at 9:03 PM, Wilson, Ronald wrote: > Yeah. The clearest thing in the RFC is the ABNF grammar. However, > even > that leaves out common cases like white space outside of quoted > fields, > which most people would expect to be trimmed. Also, I think most > people > would

Re: [sqlite] Tedious CSV import question

On 26/09/2009, at 3:33 AM, Wilson, Ronald wrote: > the RFC says that everything between the commas is supposed to be > part of the field, including white space. Normally I trim the white > space unless it's quoted. You can certainly offer the option to trim whitespace or change case or

Re: [sqlite] Tedious CSV import question

On 26/09/2009 5:38 AM, Petite Abeille wrote: > On Sep 25, 2009, at 9:21 PM, C. Mundi wrote: > >> Your post neatly articulates virtually every facet of this issue. >> Thank you. I wish we could get everyone to stop using csv. I hate to >> look at xml but I often wish everyone would use it

Re: [sqlite] Tedious CSV import question

Cool. Where do I send my application (for citizenship)? :) Carlos On 9/25/09, Petite Abeille wrote: > > On Sep 25, 2009, at 9:21 PM, C. Mundi wrote: > >> Your post neatly articulates virtually every facet of this issue. >> Thank you. I wish we could get everyone to

Re: [sqlite] Tedious CSV import question

On 26/09/2009 5:03 AM, Wilson, Ronald wrote: > > > Yeah. The clearest thing in the RFC is the ABNF grammar. However, even > that leaves out common cases like white space outside of quoted fields, > which most people would expect to be trimmed. Also, I think most people > would expect

Re: [sqlite] Tedious CSV import question

C. Mundi wrote: > On 9/25/09, Jay A. Kreibich wrote: > >> ... >> >> CSV is a great quick and dirty format to move data. But it isn't >> "simple" and it isn't nearly as universal as many assume. It works >> great if you're just moving simple numbers and strings that

Re: [sqlite] Tedious CSV import question

On 26 Sep 2009, at 1:40am, BareFeet wrote: > As above, I think it is pretty simple. The complexity arrives when > people expect CSV to provide more than a simple array of strings. One of my free programs has to read CSV files ( http://www.hearsay.demon.co.uk/mac/MacGarminTools/csv2gpx.html

Re: [sqlite] Tedious CSV import question

On 26/09/2009, at 12:00 AM, Jay A. Kreibich wrote: > On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the > wall: > >> In reality, in the thousands of CSV files I've dealt with >> over the years, they all follow the same standard: >> >> 1. Rows are delimited by a new line (return

Re: [sqlite] Tedious CSV import question

- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Wilson, Ronald Sent: Friday, September 25, 2009 1:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Tedious CSV import question > > Still, the RFC does not > > address how to

Re: [sqlite] Tedious CSV import question

On Sep 25, 2009, at 9:21 PM, C. Mundi wrote: > Your post neatly articulates virtually every facet of this issue. > Thank you. I wish we could get everyone to stop using csv. I hate to > look at xml but I often wish everyone would use it instead of csv. In fact, in Switzerland, there is a

Re: [sqlite] Tedious CSV import question

Jay... Your post neatly articulates virtually every facet of this issue. Thank you. I wish we could get everyone to stop using csv. I hate to look at xml but I often wish everyone would use it instead of csv. I would hate to see any of the sqlite core devs waste time on csv. Carlos On

Re: [sqlite] Tedious CSV import question

> > I read the RFC last night > > Oh, my... > > Programming in Lua has a nice, concise example regarding CSV parsing > (near the end of the page): > > http://www.lua.org/pil/20.4.html > > Quote: > > To break a CSV into an array is more difficult, because we must avoid > mixing up the commas

Re: [sqlite] Tedious CSV import question

On Sep 25, 2009, at 7:33 PM, Wilson, Ronald wrote: > I read the RFC last night Oh, my... Programming in Lua has a nice, concise example regarding CSV parsing (near the end of the page): http://www.lua.org/pil/20.4.html Quote: To break a CSV into an array is more difficult, because we must

Re: [sqlite] Tedious CSV import question

> > Still, the RFC does not > > address how to handle rows like this: > > > > 1234,abc"123",abc > > 1235,""123,abc > > Both violate the format as defined. Generate an error message or > interpret them any way that suits you. > > > What are you supposed to do with those? It is not clear. Also,

Re: [sqlite] Tedious CSV import question

On 25 Sep 2009, at 6:33pm, Wilson, Ronald wrote: > Still, the RFC does not > address how to handle rows like this: > > 1234,abc"123",abc > 1235,""123,abc Both violate the format as defined. Generate an error message or interpret them any way that suits you. > What are you supposed to do

Re: [sqlite] Tedious CSV import question

> Reading the RFC only proves my point. SQLite v3 is older than that > doc, and it pretty much admits the cat was out of the bag a long time > ago. There are a ton of optional and might/may/could sections that > event the format they define has a lot of holes in it (i.e. headers, > or

Re: [sqlite] Tedious CSV import question

On 25 Sep 2009, at 3:00pm, Jay A. Kreibich wrote: > Personally, I'd rather have the SQLite team working on core database > features than trying to build a better CSV parser Yet strangely, the command-line tool is one of the best things about SQLite. If I appear to find a bug in a SQLite

Re: [sqlite] Tedious CSV import question

On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the wall: > In reality, in the thousands of CSV files I've dealt with > over the years, they all follow the same standard: > > 1. Rows are delimited by a new line (return and/or line feed). > 2. Columns are delimited by a comma.

Re: [sqlite] Tedious CSV import question

On Thu, Sep 24, 2009 at 9:21 PM, C. Mundi wrote: > On Thu, Sep 24, 2009 at 7:16 PM, wrote: > >> On Thu, 24 Sep 2009, Rich Shepard wrote: >> >> > On Thu, 24 Sep 2009, C. Mundi wrote: >> > >> >> I just bit the bullet and did it.  Python has an excellent csv

Re: [sqlite] Tedious CSV import question

C. Mundi wrote: > Hi. I have scanned the list archives and Googled. I may have missed > something, but what I found was not sufficiently general or robust for my > needs. > > Happily, sqlite has a very nice .output mode for CSV. It correctly > double-quotes fields when they contain a space or a

Re: [sqlite] Tedious CSV import question

On Thu, Sep 24, 2009 at 7:16 PM, wrote: > On Thu, 24 Sep 2009, Rich Shepard wrote: > > > On Thu, 24 Sep 2009, C. Mundi wrote: > > > >> I just bit the bullet and did it. Python has an excellent csv module, > >> capable of handling just about any dialect you're likely to

Re: [sqlite] Tedious CSV import question

On Thu, Sep 24, 2009 at 6:45 PM, Rich Shepard wrote: > On Thu, 24 Sep 2009, C. Mundi wrote: > > > I just bit the bullet and did it. Python has an excellent csv module, > > capable of handling just about any dialect you're likely to encounter. I > > am so grateful I did

Re: [sqlite] Tedious CSV import question

On Thu, 24 Sep 2009, Rich Shepard wrote: > On Thu, 24 Sep 2009, C. Mundi wrote: > >> I just bit the bullet and did it. Python has an excellent csv module, >> capable of handling just about any dialect you're likely to encounter. I >> am so grateful I did not have to write a parser for CSV. In

Re: [sqlite] Tedious CSV import question

On Thu, 24 Sep 2009, C. Mundi wrote: > I just bit the bullet and did it. Python has an excellent csv module, > capable of handling just about any dialect you're likely to encounter. I > am so grateful I did not have to write a parser for CSV. In just a few > lines I can read the csv right into

Re: [sqlite] Tedious CSV import question

On Thu, Sep 24, 2009 at 12:37 PM, C. Mundi wrote: > > Hi. I have scanned the list archives and Googled. I may have missed > something, but what I found was not sufficiently general or robust for my > needs. > > Happily, sqlite has a very nice .output mode for CSV. It

Re: [sqlite] Tedious CSV import question

On 25/09/2009, at 7:47 AM, Jay A. Kreibich wrote: > On Thu, Sep 24, 2009 at 09:12:20PM +0100, Simon Slavin scratched on > the wall: > >> The .import command does not handle quoted fields correctly, as has >> recently been stated on this list. > > I think it would be more fair to say that the

Re: [sqlite] Tedious CSV import question

On Sep 24, 2009, at 11:47 PM, Jay A. Kreibich wrote: > The bigger issue is that CSV isn't really a format, but more of a > loose idea. Right, that said, sticking to RFC 4180 is not such a bad bet: Common Format and MIME Type for Comma-Separated Values (CSV) Files

Re: [sqlite] Tedious CSV import question

On Thu, Sep 24, 2009 at 09:12:20PM +0100, Simon Slavin scratched on the wall: > The .import command does not handle quoted fields correctly, as has > recently been stated on this list. I think it would be more fair to say that the .import command has chosen not to support quoted fields.

Re: [sqlite] Tedious CSV import question

I had a lot of trouble with trying to import into SQL Manager from csv files. Per some earlier advice, I began inputting through the console instead. But I did figure out a way to cut down on the time. I've been making cookie-cutter iPhone apps (a series of rules of legal procedure

Re: [sqlite] Tedious CSV import question

: [sqlite] Tedious CSV import question On c.Mundi's CSV inquiry, One way I (interactively) address the need to import CSV data into sqlite databases is to use Mrinal Kant's SQL Manager add-in. This offers what I believe are more robust facilities for performing this type of bulk import. I'm not sure

Re: [sqlite] Tedious CSV import question

On c.Mundi's CSV inquiry, One way I (interactively) address the need to import CSV data into sqlite databases is to use Mrinal Kant's SQL Manager add-in. This offers what I believe are more robust facilities for performing this type of bulk import. I'm not sure if SQL Manager is an option for

Re: [sqlite] Tedious CSV import question

On 24 Sep 2009, at 8:37pm, C. Mundi wrote: > Happily, sqlite has a very nice .output mode for CSV. It correctly > double-quotes fields when they contain a space or a comma, and it > correctly > 'escapes' double-quote literals as "". Great! I need to go the > other way. > > I don't see an

Re: [sqlite] Tedious CSV import question

assuredcommunications(tm) > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of C. Mundi > Sent: Thursday, September 24, 2009 3:37 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Tedious C

[sqlite] Tedious CSV import question

Hi. I have scanned the list archives and Googled. I may have missed something, but what I found was not sufficiently general or robust for my needs. Happily, sqlite has a very nice .output mode for CSV. It correctly double-quotes fields when they contain a space or a comma, and it correctly