On 2014/07/18 22:56, Rui Fernandes wrote:
I already know how to import a csv file, and save it in SQLite format.
But how can I define the time of variable in the fields since it assumes
all of them are TEXT?
I assume you mean "type" of variable (and not "time") - else see the reply from
Mr. Griggs.
If you mean type - this is not possible with importing a CSV from scratch via sqlite3.exe because we know nothing of the file being
imported before it is imported - at which point the schema already needs to exist.
It also depends how often you need to do this. Do you only want to import the data once, or at least, only need to specify the
schema once? Or is it different schemata every time over which you need some control of the variable types?
Either way, one possibility is to define the table manually before importing
the data. Let's assume you have data like this:
rTime, sName, iAge
2014-07-14 15:33:22,John Smith,30
2014-07-14 16:31:25,William Gates,43
If you simply import it to a new non-existing table, it will all have TEXT
affinity, as you already noticed.
but if you do first as an example table schema:
CREATE TABLE people_db (
rTime NUMERIC NOT NULL,
sName TEXT COLLATE NOCASE,
iAge INT DEFAULT 0
);
and then import the same data as above, you ill now have a correctly typed
structure.
Important, you can do this before importing, or after importing. To explain the "after importing" scenario, imagine you imported the
csv to a table named "import_temp", knowing it will have those columns all as text.
Now you can run simply the schema creation again, followed by an import from
the other DB rather than the csv, like this:
CREATE TABLE people_db (
rTime NUMERIC NOT NULL,
sName TEXT COLLATE NOCASE,
iAge INT DEFAULT 0
);
INSERT INTO people_db (rTime,sName,iAge) SELECT CAST(rTime AS NUMERIC), sName,
CAST(iAge AS INT) FROM import_temp;
DROP TABLE import_temp;
Maybe wrapping all in a Transaction.
sName is already Text, so no need to cast. I am not even sure if the casts are needed at all, having set the Schema correctly, but
it might highlight where the csv contain invalid types of data (i.e. which cannot be type-cast as needed).
This is rather easy to do in a script too, as long as you know before the time
what the data types should be.
Another method, if you have very complicated tables that you will only import once, is to maybe use an importer tool, almost all DB
admin type programs have some flavour of it available. We could suggest some if this is a viable route.
Hope it helps,
Ryan
(I have not tested any of the posted SQL above, but if it is faulty, should be
easy to fix)
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
