1

I have the following file(this is just 1 column of a file I am trying to import. I figure if I get this 1 column right, I can then do the other columns):

$ cat head_test_1col.csv
"Rec Open Date"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"
"2015-10-06"

now i can create my table in mysql as follows:

CREATE TABLE IF NOT EXISTS test1234 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`Rec Open Date` Date NOT NULL COMMENT 'Rec Open Date',
PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;

Then I try and add the rows with either of the following, 1 or 2, but it is not populating correctly.

1

LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234 COLUMNS TERMINATED BY ',';

2

LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234

I get the following(just all zeros):

mysql> LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234;
Query OK, 10 rows affected, 20 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 20

mysql>
mysql>
mysql> select * from test1234;
+----+---------------+
| id | Rec Open Date |
+----+---------------+
| 64 | 0000-00-00    |
| 65 | 0000-00-00    |
| 66 | 0000-00-00    |
| 67 | 0000-00-00    |
| 68 | 0000-00-00    |
| 69 | 0000-00-00    |
| 70 | 0000-00-00    |
| 71 | 0000-00-00    |
| 72 | 0000-00-00    |
| 73 | 0000-00-00    |
+----+---------------+
10 rows in set (0.00 sec)

mysql>

Can anyone advise how I can get the data to populate correctly?

HattrickNZ
  • 4,373
  • 15
  • 54
  • 98
  • Does [this answer about loading CSV data](http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile) help? You need to specify more options here for it to work properly. – tadman Apr 14 '16 at 03:05
  • You need `ENCLOSED by '"'` to tell it to strip off the quotes. – Barmar Apr 14 '16 at 03:13

1 Answers1

0

Try this:

LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234 COLUMNS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (`Rec Open Date`);

Explaination:

ENCLOSED BY '"'             -- data is enclosed by "
LINES TERMINATED BY '\n'    -- define line termination
IGNORE 1 LINES              -- first line is column name, don't load as data
(`Rec Open Date`);          -- Only load for 2nd column, and 1st column is auto increased.

Output:

mysql> LOAD DATA LOCAL INFILE '/home/jinggang.sjg/data.csv' INTO TABLE test1234 COLUMNS TERMINATED BY ','
    -> ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (`Rec Open Date`);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test1234;
+----+---------------+
| id | Rec Open Date |
+----+---------------+
| 64 | 2015-10-06    |
| 65 | 2015-10-06    |
| 66 | 2015-10-06    |
| 67 | 2015-10-06    |
| 68 | 2015-10-06    |
| 69 | 2015-10-06    |
| 70 | 2015-10-06    |
| 71 | 2015-10-06    |
| 72 | 2015-10-06    |
+----+---------------+
9 rows in set (0.00 sec)
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • This might also need `SET id = NULL` at the end. See [this similar post](http://stackoverflow.com/questions/6017032/how-to-load-data-infile-in-mysql-with-first-col-being-auto-increment) – Ezequiel Tolnay Apr 14 '16 at 03:31
  • Thanks. Seems reasonable to add that `SET`. Actually it works already and I think the not specified `id` will give a `NULL` or `0` by default. – Dylan Su Apr 14 '16 at 03:37
  • tks that seems to work. what is the lesson here. do i have to be really explicit, because I need to specify all them pameters e.g. `(\`Rec Open Date\`);`? I thought I coul be less explicit. and I have tried but i get errors/warnings unless i specify all the options. – HattrickNZ Apr 14 '16 at 03:42
  • Since first column will not be loaded, I think you need to specify all other column nams explicitly. – Dylan Su Apr 14 '16 at 03:49