在LOAD DATA INFILE期间，时间戳字段未正确更新

Question

I'm populating a MySQL table with test data using LOAD DATA INFILE and seeing behavior I don't understand. The timestamp column (with default=CURRENT_TIMESTAMP) is updating some records correctly and others, seemingly randomly, with 0000-00-00 00:00:00.

Questions Table Structure:
Column             Type     Null    Default             Comments
Q_ID               int(10)  No           
Clients_ID         int(10)  No           
Question           varchar(255) No  

. . other fields left out .

Submit_Timestamp   timestamp    No      CURRENT_TIMESTAMP 

After connecting to the db, here are the relevant lines of code:

// prepare the SQL query for the Questions table

$sql = "LOAD DATA INFILE 'c:/wamp/www/Q_Sample_01.CSV' INTO TABLE Questions FIELDS TERMINATED BY ','";

// submit the query and capture the result

$result = mysqli_query($conn, $sql) or die (mysqli_error($conn));

The program executes and the data is INSERTed into all fields properly with the exception of the Submit_Timestamp field, which sometimes gets correct results, and sometimes gets 0000-00-00 00:00:00.

The CSV file has a leading comma in all records to indicate the missing Q_ID data. The Q_ID field, which is Autoincrement, is working properly. There is no data for timestamp at the end of the CSV rows, I expect MySQL to enter the default value. I tried to put trailing comas after the last field listed in the CSV, thinking that might make it more clear there was data to add into the Submit_Timestamp field, but it made things worse, giving me 0000-00-00 00:00:00 for every record.

I also tried a suggestion to alter the table making Submit_Timestamp ON UPDATE CURRENT_TIMESTAMP, but it didn't help, and isn't the behavior I want for this field, so I removed it.

I just noticed a pattern, though it's not yet explanatory to me yet. The records that get all 0s for the timestamp have at least one field in them that has double quotes around it, to prevent the included comma from inappropriately indicating a new field. This is not always in the last field (adjacent to the Submit_Timestamp field). The CSV was created in Microsoft Excel.

Where am I going wrong?

Answer 1

Excel save to CSV is problematic with string data that contains commas.

You can easily fix this by changing the separator character to something not in the data. I used the '|' (above the backslash on my keyboard).

You'll also need to change your SQL command to incorporate this change to the separator character.

$sql = "LOAD DATA INFILE 'c:/wamp/www/Q_Sample_01.CSV' INTO TABLE Questions FIELDS TERMINATED BY '|'";

This worked perfectly.