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

Did you remember to specify the file name when you started sqlite3. If you don't give the file name, everything is done to the memory database and is discarded when you exit. David From: peter korinis To: sqlite-users@sqlite.org

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

>From the docs: .separator STRING Change separator used by output mode and .import replace string with a comma, without quotes, then do your import. It should work. David From: peter korinis To: sqlite-users@sqlite.org Sent:

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

Have you ran *.mode csv*? Jonas Malaco Filho 2012/5/7 peter korinis > Regarding SQLITE3.exe statement ".import FILE TABLE" > I created a table. > My input file is a comma-delimited text file > When I run .import I get the following "Error: FILE line 1: expected 46 >

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

Regarding SQLITE3.exe statement ".import FILE TABLE" I created a table. 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. I suspect this is a simple

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

Hi Michael, May I make a request on behalf of the C++ challenged crowd? Is it possible to fuse your contribution with the existing capabilities of the ".import" command so that it can be activated via syntax similar to - "RECORDS DELIMITED BY... FIELDS TERMINATED BY... OPTIONALLY ENCLOSED BY..."

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

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. I could add the CR/LF...but I've never seen that used in a CSV format. Then again, may as well use the standard.

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

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",col3 It's not fully RFC4180 compliant as it doesn't allow for CF/LF

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

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

Here's a utility to import a comma separated file (does not work for quoted strings or strings with commas). Figures out the # of columns automagically from the csv file. All wrapped in a singled transaction. Shows progress every 100,000 inserts. Usage: csvimport filename databasename

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

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 an empty 46 column table. (I intend to test load with

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

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? I was trying to use sqlite3 CLI cmd ".import testfile.txt test_tbl;" to load a 999x46 comma-delimited file into a previously

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

As said Oliver, I don't think the real issue is have to choose one software: or SQLite or R. I've used SQLite with data around 10GB under Windows 7 with 4GB of RAM and it worked perfectly. Yes, it size is less than yours but I learned that the use of every GUI was a problem. Since that, I always

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

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 hoc ways, perhaps summing/counting other

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

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

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

Does that mean using the CLI worked for you? If so, you may be able to access the database with the other programs AFTER you create it. Seems that creating a db from csv is challenging to some and not thoroughly tested for large data sources. Michael D. Black Senior Scientist Advanced

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 would recommend for SQLite? peter -Original Message- From: sqlite-users-boun...@sqlite.org

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

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 the data, and the second column was split into two by the comma)

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. http://sqlite.org/download.html Don't do any indexes up frontdo them afterwords if they'll help your queries. Indexes will slow down your import notably. I don't think you're anywhere near the

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