mysql + importing a file with spaces in the column headers + how to handle

Question

I have a csv file that I would like to import into mysql, and I am slowly getting there.

I am trying to use the LOAD DATA INFILE but first I have to create the table and this is where my issues is.

The file.csv I am trying to import looks like this(but it only has 1 column for this example "Rec Open Date"):

"Rec Open Date", <other columns e.g. "Data Volume (Bytes)">
"2015-10-06", <other values>

Now I would like the column header in the table to be the same as the csv file, but I can't get this to work using ' or ", shown below in EG1 and EG2. The way I can get this to work is to change replace the spaces with underscores in the table header i.e. "Rec Open Date" goes to "Rec_Open_Date". But this would involve changing the column header names in the csv file i.e. replace the spaces with underscores in the table header. This seems to be the best option for me, but can anyone advise other wise? Ideally I would like the csv file column headers to be the same as the datatable headers and EG3 seems to be the way to achieve this.

EG1:

mysql> 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 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Rec Open Date' Date NOT NULL COMMENT 'Rec Open Date',
  PRIMARY KEY (id)
) ENGI' at line 3
mysql>

EG2

mysql> ^MCREATE 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 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Rec Open Date" Date NOT NULL COMMENT 'Rec Open Date',
  PRIMARY KEY (id)
) ENGI' at line 3
mysql>

EG3:

mysql> 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 ;
Query OK, 0 rows affected (0.00 sec)

mysql>

NOTE following EG3, creating the table I would then do the following, but the column headers in the file would have to be the same as the ones on the datatable, as I understand it.

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

spencer7593 · Accepted Answer · 2016-04-14T00:51:48.307

The MySQL LOAD DATA statement loads values from the file by position, not by name.

MySQL doesn't have any requirement about the "names" of the fields (from the header line in the .csv file) "matching" any column names. (It's possible that some other component of your system has that type of requirement, but MySQL LOAD DATA doesn't.)

It's valid for a .csv file to not have a header line. When there is a header line, we typically "skip over" it using IGNORE 1 LINES.

As an aside, I prefer to use the keyword FIELDS in the LOAD DATA statement in place of COLUMNS.

In MySQL, identifiers (column names, table names) can be escaped using backtick characters. To use an identifier that includes invalid characters, like a space, the identifier must be escaped.

CREATE TABLE ... 
... 
`Rec Open Date` DATE NOT NULL COMMENT 'Rec Open Date',
^             ^

On my keyboard, the backtick is the key at the upper left ` ~ just to the left of the 1 ! key.

ANSI_QUOTES

If sql_mode variable includes ANSI_QUOTES, then you can also use double quotes to escape identifiers. e.g.

SHOW VARIABLES LIKE 'sql_mode' ;

SET sql_mode = 'ANSI_QUOTES,...' ;

SHOW VARIABLES LIKE 'sql_mode'

Variable_name  Value        
-------------  -----------
sql_mode       ANSI_QUOTES

Then:

CREATE TABLE ... 
... 
"Rec Open Date" DATE NOT NULL COMMENT 'Rec Open Date',
^             ^

If sql_mode does not include ANSI_QUOTES (either explicitly or included in some other setting), then the double quotes around identifers doesn't work. And using single quotes around identifiers shouldn't ever work... single quotes enclose string literals.

The takeaway: use backtick characters to escape identifiers (column names, table names, etc.) and use single quotes around string literals. Avoid using double quotes around string literals, to make your SQL code work even when ANSI_QUOTES is set.

tks, so I can jsut use back ticks to create my table ```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 ;``` — HattrickNZ, Apr 14 '16 at 02:51
how do i then load the file `LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234 COLUMNS TERMINATED BY ',';` or this `LOAD DATA LOCAL INFILE 'head_test_1col.csv' INTO TABLE test1234` does not work, i am getting all zeros in my table — HattrickNZ, Apr 14 '16 at 02:53
Getting "all zeros" sounds like the first field isn't a valid numeric value. If the field values are enclosed in double quotes. LOAD DATA can remove the double quotes if you include " OPTIONALLY ENCLOSED BY '"' " in the LOAD DATA statement. MySQL is expecting the date field to be in YYYY-MM-DD format. If the field is in a different format, you can load the field into a user-defined variable, e.g.`@udv2` and use an expression in the SET clause to convert the value into a DATE e.g. `\`Rec Open Date\` = STR_TO_DATE( @udv2, '%m/%d/%Y')` — spencer7593, Apr 14 '16 at 03:42

mysql + importing a file with spaces in the column headers + how to handle

1 Answers1

Linked