Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On Mon, May 07, 2012 at 01:34:13PM -0400, peter korinis wrote: > So, if I use gawk to change my comma separated file to | (pipe) delimiter . > will it work? If you use gawk, you can easily change your comma separated file to the series of correct SQL INSERT statements (besides ensuring

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 8 May 2012, at 12:34am, "peter korinis" wrote: > I did NOT specify a file name when I started sqlite3 - so I guess all is > lost. I'll have to redo it. > > So then the first thing is to specify the db (file) name - what syntax? I > tried typing a file name (test.db)

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

I did NOT specify a file name when I started sqlite3 - so I guess all is lost. I'll have to redo it. So then the first thing is to specify the db (file) name - what syntax? I tried typing a file name (test.db) but error. pk ___ sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

The file name does not necessarily have anything to do with the table name. On May 7, 2012 2:25 PM, "peter korinis" wrote: > Simon > > I searched the entire disk for the table name and no matches. > > > > pk > > > > ___ >

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 10:25pm, peter korinis wrote: > I searched the entire disk for the table name and no matches. SQLite keeps its tables in databases. One database is one file on disk. The files are not in any magic place: each time you create a database you have to

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Simon I searched the entire disk for the table name and no matches. pk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Sent: Monday, May 7, 2012 5:01 PM Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file I can't find a table I just created and imported data. With .databases I can see 'main'  but with no location on disk and with . tables I see the table. Instructions says they are save . but

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 10:01pm, peter korinis wrote: > I can't find a table I just created and imported data. > > With .databases I can see 'main' but with no location on disk and with . > tables I see the table. > > Instructions says they are save . but where. I can't find

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

I can't find a table I just created and imported data. With .databases I can see 'main' but with no location on disk and with . tables I see the table. Instructions says they are save . but where. I can't find them with windows search? pk ___

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Thanks for advice http://www.pantz.org/software/sqlite/sqlite_commands_and_general_usage.html had the explanation/example I needed to get the import done successfully. Using ".separator ," was what I was missing. peter ___ sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Next iteration of my csvimport utility. I think it handles Simon's test cases adequately (or it gives an error). Now allows for multiline fields Correctly(?) handles fields with escaped quotes. Checks the entire file to ensure all records have the correct field count before processing.

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

org Sent: Monday, May 7, 2012 1:34 PM Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file On 7 May 2012, at 4:41pm, peter korinis wrote: > My input file is a comma-delimited text file > When I run .import I get the following "Error: FILE line 1: expected 46 > colu

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 6:34pm, peter korinis wrote: > > > "The default separator is a pipe symbol ("|")." > > Simon. > > > > So, if I use gawk to change my comma separated file to | (pipe) delimiter . > will it work? I think so. Or

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 4:41pm, peter korinis wrote: > My input file is a comma-delimited text file > When I run .import I get the following "Error: FILE line 1: expected 46 > columns of data but found 1" > It seems .import is not recognizing comma delimiter.

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 6:27pm, peter korinis wrote: > Without knowing syntax, I'm forced to ask these dumb questions or give up > (not) . since no good documentation - sorry. Is very good documentation. The program itself says SQLite version 3.7.12 2012-03-31 02:46:20 Enter

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

No I had not used ".mode" - the wording described ".mode" as "set output mode where." . the word "output" made me think this was for .output statement. I just tried " .mode csv ". what do your * mean? Do I put in the file and/or table name or nothing more? I tried several different ways but

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 7 May 2012, at 4:41pm, peter korinis wrote: > My input file is a comma-delimited text file > When I run .import I get the following "Error: FILE line 1: expected 46 > columns of data but found 1" > It seems .import is not recognizing comma delimiter.

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

t; From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Friday, May 04, 2012 11:16 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file > > > On 4 May 2012,

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file On 4 May 2012, at 4:02pm, peter korinis <kori...@earthlink.net> wrote: > Sqlitespy looks good ... I will try it. > website says download contains sqlite itself, which I already have - > will there be a

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

%s\n",sql,sqlite3_errmsg(db)); >exit(1); > } >} > } > rc=sqlite3_finalize(stmt); > if (rc) { >fprintf(stderr,"Finalize failed: %s\n",sqlite3_errmsg(db)); >exit(1); > } > printf("%d inserts, committing...\n",

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Hi Simon, My test cases weren't intended to supply data, they're crash tests. I have faith that Mike's software correctly interprets syntactically correct cases. I was interested in making sure it didn't crash, hang or spew on syntactic errors. You're right. JcD

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 6 May 2012, at 3:20pm, Jean-Christophe Deschamps wrote: > Also fields starting with a " delimiter should comply with the rules. My test cases weren't intended to supply data, they're crash tests. I have faith that Mike's software correctly interprets syntactically

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Not sure I buy your test cases. I don't either, but for diverging reasons. Fields containg double quotes must be in double quotes. So only one of your examples is valid first,""second"",third I'll have to fix that one. No, it's invalid. Apply to " the same rules that apply to ' for

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 6 May 2012, at 3:03pm, "Black, Michael (IS)" wrote: > Not sure I buy your test cases. > > Fields containg double quotes must be in double quotes. So only one of your > examples is valid > > first,""second"",third > > I'll have to fix that one. Sure. The rest

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

m: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Sunday, May 06, 2012 8:35 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file On 6 May 2012, at 1:5

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 6 May 2012, at 1:59pm, "Black, Michael (IS)" wrote: > I modified my csvimport to allow for quoted fields. Still automaticallhy > detects the # of columns. > If quoted fields also contain internal quotes they must be escaped by > doubling them. > e.g. > col1,"this

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

iled on '%s': %s\n",argv[2],sqlite3_errmsg(db)); exit(1); } fclose(fp); return 0; } Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-us

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On Fri, May 04, 2012 at 11:23:42AM -0400, peter korinis wrote: > > After I get the data loaded and inspect for nulls in prospective index > attributes, can I add indices? Yes, of course. Moreover, it would be much faster to add indices at once at the end rather than create them beforehand and

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 2012-05-02 14:06, peter korinis wrote: > Thank you all. > Look like I'm stuck with the CLI though I have contacted Nucleon software > support ... tried CLI yesterday but need more practice. > Is there a good reference book you would recommend for SQLite? Absolutely. The one by Mike Owens was a

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 2012-05-01 20:41, Baruch Burstein wrote: > It is already wrapped in a transaction. > I seem to remember seeing somewhere that the .import command doesn't > understand escaping, e.g. > > "one","two,three" > > will get imported as > > "one" | "two | three" > > (the quotes are part of

[sqlite] is SQLite the right tool to analyze a 44GB file

I'm using R (R Studio) with large SQLite databases and it's a dream come true. Here's a simple example of a ToxCast chemical table inside a Substance database. # Load the following libraries into R # DBIR Database Interface # RSQLiteSQLite

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

____ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Friday, May 04, 2012 10:23 AM To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] is SQLite the right tool to analyz

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 4 May 2012, at 4:23pm, peter korinis wrote: > I have scaled down the attributes of interest to 46 columns (discarding the > other 550). No columns are calculated. No updates to this file ... one user > ... only query, sort, etc. type transactions. > So I want to load

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

or helpful info. peter -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valentin Davydov Sent: Friday, May 04, 2012 9:43 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 4 May 2012, at 4:02pm, peter korinis wrote: > Sqlitespy looks good ... I will try it. > website says download contains sqlite itself, which I already have - will > there be a problem using ...spy with existing sqlite? SQLite is not a single library which has to live

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Oliver Peters Sent: Thursday, May 03, 2012 7:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file Am 03.05.2012 19:59, schrieb peter korinis: > I have R but really haven't used it much. I know

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On Tue, May 01, 2012 at 04:06:01PM -0400, peter korinis wrote: > I'm new to SQLite . not a programmer . not a DBA . just an end-user with no > dev support for a pilot project (single user, no updates, just queries). > > > > I want to analyze the data contained in a 44GB csv file with 44M rows

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Am 03.05.2012 19:59, schrieb peter korinis: I have R but really haven't used it much. I know it's a great stats package and great for data reduction ... but I want to perform queries against my 44GB of data, filtering records by a variety of attributes, comparing those subsets in a variety of ad

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 5/3/2012 11:59 AM, peter korinis wrote: is R a good query tool? It's a programming language. It can do anything within your power to persuade the interpreter. One of the fundamental data types in R is the data frame, which is roughly equivalent to a SQLite table. This is an R

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

2012 9:36 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file > > On 5/1/2012 2:06 PM, peter korinis wrote: > > Is SQLite the wrong tool for this project? > > Probably. > > SQLite is a data st

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

] On Behalf Of Warren Young Sent: Thursday, May 03, 2012 9:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file On 5/1/2012 2:06 PM, peter korinis wrote: > Is SQLite the wrong tool for this project? Probably. SQLite is a data stor

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Am Do 03 Mai 2012 15:35:46 CEST schrieb Warren Young: On 5/1/2012 2:06 PM, peter korinis wrote: Is SQLite the wrong tool for this project? Probably. SQLite is a data storage tool. With enough SQL cleverness, you can turn it into a data *reduction* tool. But a data analysis tool? No, not

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

On 5/1/2012 2:06 PM, peter korinis wrote: Is SQLite the wrong tool for this project? Probably. SQLite is a data storage tool. With enough SQL cleverness, you can turn it into a data *reduction* tool. But a data analysis tool? No, not without marrying it to a real programming language.

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

gt;From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on >behalf of peter korinis [kori...@earthlink.net] >Sent: Wednesday, May 02, 2012 9:06 AM >To: 'General Discussion of SQLite Database' >Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file >

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

To: 'General Discussion of SQLite Database' Subject: EXT :Re: [sqlite] is SQLite the right tool to analyze a 44GB file Thank you all. Look like I'm stuck with the CLI though I have contacted Nucleon software support ... tried CLI yesterday but need more practice. Is there a good reference book you

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Tuesday, May 01, 2012 4:22 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file You need to try and do an import from the shell. GUIs seem to have way too many limits

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

Am 01.05.2012 22:06, schrieb peter korinis: I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all<15

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[sqlite-users-boun...@sqlite.org] > on behalf of peter korinis [kori...@earthlink.net] > Sent: Tuesday, May 01, 2012 3:06 PM > To: sqlite-users@sqlite.org > Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file > > I'm new to SQLite . not a programmer . not a DBA

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

...@earthlink.net] Sent: Tuesday, May 01, 2012 3:06 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] is SQLite the right tool to analyze a 44GB file I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

If none of your fields contain a comma, you can just use the sqlite3 terminal to load a csv file. On Tue, May 1, 2012 at 11:06 PM, peter korinis wrote: > I'm new to SQLite . not a programmer . not a DBA . just an end-user with no > dev support for a pilot project (single

[sqlite] is SQLite the right tool to analyze a 44GB file

I'm new to SQLite . not a programmer . not a DBA . just an end-user with no dev support for a pilot project (single user, no updates, just queries). I want to analyze the data contained in a 44GB csv file with 44M rows x 600 columns (fields all <15 char). Seems like a DBMS will allow me to