auto column insertion during data importing in mysql

Question

I have five column in .csv file and i have to import it in MySQL table.
But i also required date on which i import file in database.

So i mean six column in table with today date.

I have tried with use time stamp as datatype and CURRENT_TIMESTAMP in default.

But it occurs query:- Invalid column count in CSV input on line 1.

Please guide me.

Screen Shot:-

Csv File:-

Even if it is a timestamp, you need to add an empty value in your insert. The columns of the table and the number of column in your insert query should match. — Krishnakumar, Apr 06 '17 at 09:14
actually i not use mysql query but i simply import data using `import` menu in Wampserver. — Smit Saraiya, Apr 06 '17 at 09:17
Maybe you should show us the schema for the table. And the code used to do the import — RiggsFolly, Apr 06 '17 at 09:17
There is no Import menu in WAMPServer!! Do you mean you are using the IMPORT menu in `phpMyAdmin` which is equivalent to a table restore — RiggsFolly, Apr 06 '17 at 09:18
post the table structure and csv columns..that ll help us to understand your problem more — lalithkumar, Apr 06 '17 at 09:21
[`ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP`](https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html) — RiggsFolly, Apr 06 '17 at 09:22
@SmitSaraiya Try this stackoverflow solution [click here](http://stackoverflow.com/a/43223126/1676023) — Gaurav, Apr 06 '17 at 10:14

Abdus Sattar Bhuiyan · Accepted Answer · 2017-04-06T10:25:02.857

3

This means the number of column in your csv and table is not same. If you want to set default value of any column you have to keep this value empty but you have to mention this column. For example: if you want to insert 3 columns having a default value for 1 column named 'created' your csv file should look like this:

Please remove first row which describes your table structure. I mean do not use the following instead of the above:

And finally run the following sql if current timestamp is not set:

UPDATE `table` SET edate = current_timestamp;

Here put your table name it 'table' phrase.

edited Apr 06 '17 at 10:25

answered Apr 06 '17 at 09:29

Abdus Sattar Bhuiyan

3,016
4
38
72

It show error `#1292 - Incorrect datetime value: '' for column 'edate' at row 1` – Smit Saraiya Apr 06 '17 at 09:31
please share your sample csv data. – Abdus Sattar Bhuiyan Apr 06 '17 at 09:33

score 0 · Answer 2 · answered Apr 06 '17 at 10:13

0

You can use DEFAULT constraints to set the timestamp:

ALTER TABLE
 MODIFY edate datetime DEFAULT CURRENT_TIMESTAMP

ALTER TABLE
 MODIFY edate datetime DEFAULT ON UPDATE CURRENT_TIMESTAMP

answered Apr 06 '17 at 10:13

lalithkumar

3,480
4
24
40

i think this will help you – lalithkumar Apr 06 '17 at 10:13

auto column insertion during data importing in mysql

2 Answers2