How to import Timestamp from a CSV file

Question

I am trying to Import a CSV file with a column called timestamp to MySQl to a column with the structure timestamp but all i get are NULLS in the columns is there a way that i can import the timestamp as the structure to my new table.

Transaction Timestamp,Batch Ref No,Transaction Type,Group Transaction No,Receipt No,Transaction No,Transaction Amount,Actual Cost,CErr,Resource Units,Resource Units Error,Operator Name,Account Number,Debt Reference No,Meter Serial No,Token,Comment,Station,Database Name,Resource,Tax Item Name,Fixed Charge Item Category,Debt Category,Utility Name,Vend Reason
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050154,2761.54,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Fixed Cost Transaction,EVG400021324,,EVG400050155,3841,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,,Basic Support Charge,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050156,845.02,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,Basic Support Charge,,Tanesco,

that is a sample of the CSV file that am trying to import everything else imports well except the timestamp, my structure is so that the timestamp structure is set to "timestamp"

Answer 1

You get NULL values probably because MySQL cannot determine automatically that first column in you CSV is a timestamp.
You could try to change table structure so first column becomes a VARCHAR, then import data, then transform dates using mysql date functions (change date to mysql standard format 'Y-M-D H:i:s'), then convert back this column to timestamp.

Alternatively, you could use LOAD DATA INFILE to insert CSV data directly, but for first column use SET directive in LOAD DATA INFILE to change first column data to MySQL standard format. Example:

LOAD DATA INFILE youfile.csv 
  INTO TABLE yourtable
  FIELDS TERMINATED BY ","
  LINES TERMINATED BY "\n"
  (@var1, col_name_2, ...) SET col_name_1 = STR_TO_DATE(@var1,'%m/%d/%Y %k:%i');

This converts date to MySQL format while importing data.