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 validit

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) but error. It helps when

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 maili

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 > > > > ___ > sqlite-users mailing list > sql

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 specify a specific file (

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

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

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 them with windows > sea

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

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 m

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

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

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 > columns of data but found 1"

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 you could issue the comm

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 ".help" for instruction

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 stil

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

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

e.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, at 4:02pm, peter korinis

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

Lite Database Subject: 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

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

': > %s\n",sql,sqlite3_errmsg(db)); >exit(1); > } > rc = sqlite3_reset(stmt); > if (rc) { >fprintf(stderr,"Reset failed on '%s': %s\n",sql,sqlite3_errmsg(db)); >exit(1); > } >} > } > rc=sqlite

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 correct cases. I was in

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 l

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 aren't interpretation tests

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

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 is, a ""quoted"" field",co

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

3_exec(db,"COMMIT",NULL,NULL,NULL); if (rc) { fprintf(stderr,"COMMIT failed on '%s': %s\n",sql,sqlite3_errmsg(db)); exit(1); } rc=sqlite3_close(db); if (rc) { fprintf(stderr,"Close failed on '%s': %s\n",argv[2],sqlite3_errmsg(db));

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 the

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 t

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

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

k Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of peter korinis [kori...@earthlink.net] Sent: Friday, May

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 two 22GB csv files into

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

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

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 somewhere on your comput

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

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 x

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 equivale

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

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 storage tool. With

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

te.org] 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

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 witho

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

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

not sure yet ... but i'm working on it (between interruptions). thanks -Original Message- >From: "Black, Michael (IS)" >Sent: May 2, 2012 10:15 AM >To: General Discussion of SQLite Database >Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB f

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

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

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

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

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 ch

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

> > > > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > ____ > From: sqlite-users-boun...@sqlite.

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

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 . just an end-user with no dev support for a pilot pro

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 user, no updates, just qu

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