On Thursday, 7 March, 2019 17:12, D Burgess <dburges...@gmail.com> wrote:
>The big downside of the csv extension, is that no matter what you do >your table ends up with every column with a type of text. >A trap if you are you are using union/except/intersect clauses. You are more or less but not entirely correct. If you materialize a csv virtual table for which you have specified appropriate column affinities, then those affinities are applied to the materialized copy (but not the rows in the virtual table). So not quite "nothing you can do". Granted, there is "nothing you can do" to make the virtual table not return text, but then again, csv files are text files ... SQLite version 3.28.0 2019-03-05 23:49:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read source/sqlite/schema.sql sqlite> create virtual table csv1 using csv(filename=test.csv, header=yes); sqlite> create table test1 as select * from csv1; sqlite> create virtual table csv2 using csv(filename=test.csv, header=yes, schema='create table csv2 (a integer, b float)'); sqlite> create table test2 as select * from csv2; sqlite> .head on sqlite> select * from syscolumns where ObjectType == 'table'; ObjectType|ObjectName|ColumnID|ColumnName|Type|Affinity|isNotNull|DefaultValue|isPrimaryKey table|csv1|0|a|TEXT|Text|0||0 table|csv1|1|b|TEXT|Text|0||0 table|csv2|0|a|integer|Integer|0||0 table|csv2|1|b|float|Real|0||0 table|test1|0|a|TEXT|Text|0||0 table|test1|1|b|TEXT|Text|0||0 table|test2|0|a|INT|Integer|0||0 table|test2|1|b|REAL|Real|0||0 sqlite> select a, typeof(a), b, typeof(b) from csv1; a|typeof(a)|b|typeof(b) 1|text|2|text 2|text|3|text sqlite> select a, typeof(a), b, typeof(b) from csv2; a|typeof(a)|b|typeof(b) 1|text|2|text 2|text|3|text sqlite> select a, typeof(a), b, typeof(b) from test1; a|typeof(a)|b|typeof(b) 1|text|2|text 2|text|3|text sqlite> select a, typeof(a), b, typeof(b) from test2; a|typeof(a)|b|typeof(b) 1|integer|2.0|real 2|integer|3.0|real sqlite> >On Fri, Mar 8, 2019 at 11:00 AM Keith Medcalf <kmedc...@dessus.com> >wrote: > >> >> On Thursday, 7 March, 2019 14:45, Eric Tsau <erict...@gmail.com> >asked: >> >> >Is it possible to add the option of importing data into a >temporary >> >table? >> >> >Currently you have to create a temporary table first before >importing >> >to it, or having to drop the table afterwards. >> >> >.import dump.csv temp.table >> >or >> >.import dump.csv attach.table >> >> Apparently not ;) It would appear that the .import shell command >does not >> know how to create tables in schema's other than "main" >> >> However, you can create a virtual table in the temp database using >the csv >> extension (since .import only works from the sqlite3 shell) ... >> >> create virtual table temp.tablename using >csv(filename=filename.csv, >> header=yes); >> >> The virtual table will be deleted when the connection is closed ... >since >> it is only a temporary thing. You could then create a duplicate >> materialized table if you wished (but since you have a table >attached to a >> csv file why would you want to do that?) >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says >> a lot about anticipated traffic volume. >> >> >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
