Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"

share|improve this question
    
Show us what you're trying to do... What does the CSV data look like? What is your table structure? –  matthewh Aug 3 '12 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 Aug 3 '12 at 22:55

1 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.

share|improve this answer
    
am trying this now thanks a bunch –  Sitati Sep 6 '12 at 20:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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