2

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"

2
  • Show us what you're trying to do... What does the CSV data look like? What is your table structure?
    – Matt Healy
    Commented Aug 3, 2012 at 11:06
  • How do you import the file - by commandline, phpmyadmin, custom php-script? I myself tested it with phpmyadmin and it worked.
    – 32bitfloat
    Commented Aug 3, 2012 at 22:55

1 Answer 1

4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.