Re: [sqlite] Writing and reading a csv using sqlite3
Thanks, Jay, you're absolutely right. I've encountered a few csv incompatibilities myself, even within the same tool (one case used \" whereas the other used ""). Well, except for your last point, I only wanted sqlite3.exe to script some unit tests, so it really is why I downloaded SQLite. I can cope though. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing and reading a csv using sqlite3
> So the command-line tool cannot correctly read the CSV files it output > itself ? Okay, that's messed up. Something should be done. On the other hand, should it read HTML output back in again? At present, the .import only supports separators, no other processing is done. It wouldn't be too hard to respect the MODE_Csv setting on import, but the others might be a little more tricky. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug: round problem?
Simon Slavin: > Nice try. The rounding rule is that a .5 rounds to the nearest even number. No it isn't: sqlite> select round(40226+0.5); 40227.0 sqlite> select round(40227+0.5); 40228.0 sqlite> select round(40228+0.5); 40229.0 sqlite> select round(40229+0.5); 40230.0 Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing and reading a csv using sqlite3
Simon Slavin: > The command-line tool is correct as far as it goes in producing CSV > files: if you use quotes around text fields, it is correct to double quotes > inside those fields. Take a look at the CSVs you're trying to import. > Are the text fields delimited by quotes ? If quotes appear in the field, > are they doubled ? In this case, the csv file that I'm trying to import was created by SQLite. And yes, fields containing quotes or commas are quoted with the internal quotes doubled up. It's SQLite3's import that does not correctly handle the doubled-up quotes that it correctly wrote out. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round problem?
Shane Harelson: > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds > 0.5 and then truncates.Because of floating point precision, some numbers > can not be represented exactly... causing the odd rounding you saw in your > examples. I've just had a look "under the covers", and indeed it seems that SQLite has its own printf implementation. Makes sense, as I think the C standard itself is also implementation-defined in this case, and this might be the kind of thing that SQLite coders want control over. It was hasty to conclude that round-to-even is the rule, as one more example shows: sqlite> select round(40226.5); 40227.0 I think the 40223.5 case is just an example of binary/decimal floating point incompatibility. As Kernighan and Plauger put it: "Floating-point numbers are a lot like sandpiles: Every time you move one you lose a little sand and pick up a little dirt." I'm a little surprised that it's going wrong with a number ending in 0.5 though, I'd have thought that that would be expressible perfectly in binary without loss. I don't know enough about it though. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] round problem?
Igor: > http://en.wikipedia.org/wiki/Rounding#Round_half_to_even So, are you saying round-half-to-even is the SQLite behaviour? I would have expected it to have used the "normal" mathematical convention of round-half-away-from-zero. The reason this is "normal" mathematical behaviour is that any decimal result is likely to be a truncation of the real result, e.g. "pi = 3.14195". The SQL standard leaves it up to the implementation: sec 4.4.1: An approximation obtained by rounding of a numerical value N for an T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. So I guess "round half to even" is a reasonable interpretation, but I'd like to see this, and all implementation-defined behaviour, documented. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stupid noob question - can't find table
> If you give SQLite a command to open a database file that > doesn't exist, it will just create a new one. It doesn't give an > error message. So use a search function on your disk for > files with that name. Try this in your program: "select * from sqlite_master;" That will tell you if you are looking at the right database. Phil Hibbs. Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing and reading a csv using sqlite3
Paul Corke: > Not doing the ".mode csv" before the ".import" makes things better > but doesn't fix it. That breaks when I have more than one column though, "line 1: expected 3 columns of data but found 1". > Do you need to read the csv data back in to a sqlite database? If > not, then the file you've got should be ok. Well, what I need to do is import csv files from other sources, this was just a test to see if there were any gotchas. So I do need to fix the importing. > If you do then I think > you will need to either alter shell.c or create your own program > to read a csv file and insert it. I don't have a C compiler here at work, I'll look into installing one at home. I really should, it's been a long time since I did any C. Thanks for the replies. Is this something that should be fed back into the master source for shell.c? Should I post to the devs list? Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Writing and reading a csv using sqlite3
If I write out data in csv format using SQLite3 shell, it doubles up
all the quote characters. Any ideas how can I stop it doing this?
C:\sqlite>sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test ( f1 varchar(100) );
sqlite> insert into test values('test 1');
sqlite> insert into test values('test "2"');
sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from test;
sqlite> .quit
C:\sqlite>type test.csv
"test 1"
"test ""2"""
C:\sqlite>sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode csv
sqlite> delete from test;
sqlite> .import test.csv test
sqlite> select * from test;
"""test 1"""
"""test """"2"""""""
sqlite>
Phil Hibbs.
--
Don't you just hate self-referential sigs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: no such table on .import
> because of the semicolon following the table name in your .import - > command. Remove it. Yes, I was just about to reply saying I'd spotted that. Funny how after half an hour of scratching your head, you post to a mailing list and then spot it immediately. Thanks and apologies. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: no such table on .import
I'm doing this in SQLite: sqlite> .separator tabs sqlite> create table head ...> ( id varchar(10) ...> , tplnr varchar(20) ...> , plnal varchar(2) ...> , ktext varchar(40) ...> , arbpl varchar(10) ...> , werks varchar(4) ...> , verwe varchar(1) ...> , vagrp varchar(3) ...> , statu varchar(1) ...> , sttag varchar(8) ...> , strat varchar(6) ...> , batchno varchar(3) ...> ); sqlite> .import C:\HEAD.txt head; Error: no such table: head; sqlite> .schema CREATE TABLE head ( id varchar(10) , tplnr varchar(20) , plnal varchar(2) , ktext varchar(40) , arbpl varchar(10) , werks varchar(4) , verwe varchar(1) , vagrp varchar(3) , statu varchar(1) , sttag varchar(8) , strat varchar(6) , batchno varchar(3) ); Any idea why I'm getting "no such table"? Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cloning a database to memory
> http://www.sqlite.org/backup.html Awesome, that means an application could use SQLite both for its "save file" storage mechanism, and for manipulating its data in memory while it is running, dumping it back out to disk when it's finished. Thanks. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cloning a database to memory
Is there an easy way of opening a SQLite database and cloning it to an in-memory database? Cloning a database in a file is easy, you just copy the file. It would be nice if I could just copy a file into memory just as easily. Phil Hibbs. -- Don't you just hate self-referential sigs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
