Thank you very much Ryan :) I'll try it tomorrow. Kind regards,
Miguel On Fri, Jul 18, 2014 at 10:37 PM, RSmith <[email protected]> wrote: > > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
