Search code examples
mysqlcsvload-data-infile

Import into Mysql Via .CSV File

Currently have : LOAD DATA LOCAL INFILE '/Users/RkG/Desktop/Microblogs.csv' INTO TABLE blogs

import

This is an example of the data im trying to import. img2

img3

The last image is a result of the query I ran and the second image is a screenshot of the table structure, why isn't it importing the 'created on', 'location', and 'Text' ???

By trying the method given below I'm giving this output: How do I fix this date format?? img

PLAINTEXT:

3,5/18/2011 13:26,42.22717 93.33772,this convention filled with technology could be better don't plan on leaving anytime soon
3,5/10/2011 9:22,42.18881 93.35642,4 orang top scorer skrg  3 di antara nya pake adidas F50 adizero  Lightest fastest deadliest.
3,5/14/2011 9:22,42.22479 93.35922,Nike 'gana' el Mundial al patrocinador oficial Adidas // y eso que sus figuras fueron un fracaso rotundo...
3,5/6/2011 9:22,42.2469 93.32527,Positiiff    mau nabung beli kaos adidas aslii muller .. * basii bgt baru nabung skrg ya  hahaha
Solution

  • Supposing that you change created_at from date to datetime this would give you an idea. Adapt date modifiers to your needs (i don't know date and month order in your string).

    load data local infile '/Users/RkG/Desktop/Microblogs.csv'
into table blogs 
fields terminated by ','
lines terminated by '\n'
(id,@created_at,location,text)
set created_at = str_to_date(@created_at,'%c/%d/%Y %k:%i')

    If that last line does not work for you see:

    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date