CODEWITHSUNDEEP
sqlitedatetimecsvimport
Kalina
Kalina

Reputation: 5594

Why does the date column of my .csv file not copy correctly into SQLite?

I have a .csv file that has a column filled with dates and times in the format "2011-07-29 12:00:00". When I open it in notepad or wordpad this is how I see it. However when I open it in excel, it shows up as "7/29/2011 12:00:00". I imported this .csv into a SQLite database using

CREATE TABLE myTable (_id PRIMARY INTEGER KEY, dateTime TEXT, blah BLAH...);
.separator ","
.import myCsv.csv myTable

And now, not only is my database formatted with the slashes, but also the seconds are gone. I.e., it looks like "7/29/20011 12:00". Why does this happen and how do I fix this? I need it in the "2011-07-29 12:00:00" format!

Upvotes: 3

Views: 6010

Answers (3)

saravanan G
saravanan G

Reputation: 1

change the format to YYYY-MM-DD HH:MM:SS in excel- column formatting and save as CSV and then import. it should work

Upvotes: 0

hmuelner
hmuelner

Reputation: 8231

See Datatypes In SQLite Version 3 section 1.2

Upvotes: 0

Michał Šrajer
Michał Šrajer

Reputation: 31182

Read the Date and Time Datatype documentation for sqlite3.

You can solve your problem in 2 steps:

  1. import data from CSV as they are (as you do) to temporary table
  2. convert data from temporary table to destination table using select and sqlite date time functions

Upvotes: 3

Related Questions