[Resolved] LOAD DATA INFILE & Timestamp Question

[4rxsid]

Hi all,

Im loading some data into a MySQL db v 3.23.55 on winNT box, from a .csv file using LOAD DATA INFILE.

Everything is working perfect, except for trying to get my last column in the table, which is a Timestamp(14) column, to display the timestamp for which the data was loaded. All Zero's (0) are being displayed.

According to the MySQL site

TIMESTAMP columns are set to the current date and time only if there is a NULL value for the column (that is, \N), or (for the first TIMESTAMP column only) if the TIMESTAMP column is omitted from the field list when a field list is specified.
[\quote]

So, I am loading the last cell value as a null or "". I've also tried "\n" which doesn't work either. The last cell value in the .csv should correspond to my last MySQL timestamp column type. I just can't seem to get anything but 0's to be displayed in mysql.

Any ideas?

Thanks.

[Drakla]

Have you actually saved out data for the timestamped columns when you created the file you're loading from? If not then you can specify the LOAD DATA command to load the data into specific fields in the table. If you skip the timestamp column I assume that it would put in the current date, e.g. if you table had columns

name, address, telephone, time

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (name, address, telephone);

I haven't checked this, but just in case it helps I've said it.

(5 mins later, I've just checked it and it does work - but don't know how this will help if you were loading that extra column)

[4rxsid]

ok...i've found the issue.
i forgot a date character for seconds (for the .cvs data in the cell corresponding to the timestamp column):

//had
date("YmdHi");

//revised
date("YmdHis");

now it works 🙂