Importing CSV into MySQL with White Space

Question

Anyone have any success importing a csv file into MySQL?

Here's an example csv file:

Full name,Source - Name
John,Youtube
Jon,FB
Jacob,Twitter

Here's the code:

CREATE TABLE Person
(ID          INT        AUTO_INCREMENT          primary key,
fullname     CHAR       not null,
sourcenam1   CHAR,
);

LOAD DATA LOCAL INFILE '/Users/...DDB.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(Full name, Source - Name)

But I get an error that says my syntax is wrong. Specifically, it is the following:

ERROR 1064 (42000): You have an error in your SQL syntax

I would like to not have to delete the column names to solve this problem.

Possible duplicate of [mysql + importing a file with spaces in the column headers + how to handle](https://stackoverflow.com/questions/36611416/mysql-importing-a-file-with-spaces-in-the-column-headers-how-to-handle) — GSerg, Nov 09 '17 at 17:51

Barmar · Accepted Answer · 2017-11-09T19:28:39.993

0

The names in the LOAD DATA column list should be the column names in the table, not the headings in the CSV file.

You also need to use the IGNORE option to skip over the heading line.

LOAD DATA LOCAL INFILE '/Users/...DDB.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(fullname, sourcenam1)

edited Nov 09 '17 at 19:28

answered Nov 09 '17 at 17:43

Barmar

741,623
53
500
612

Hmmm.... When I did that, I got the following error: ERROR 1054 (42S22): Unknown column 'Full name' in 'field list' NOTE: The updated code looks like this LOAD DATA LOCAL INFILE '/Users/...DDB.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`Full name`, `Source - Name`) set fullname = `Full name`, sourcenam1 = `Source - Name`; – billyl320 Nov 09 '17 at 18:02
What is the actual name of the column in the table? – Barmar Nov 09 '17 at 18:05
Of the CSV? Full name and Source - Name. In t1 its fullname and sourcenam1. – billyl320 Nov 09 '17 at 18:07
The names in the CSV are not used for anything by MySQL. – Barmar Nov 09 '17 at 18:08
Do I have to use the set option to indicate where fullname and sourcename1 should go? What would that solution look like? – billyl320 Nov 09 '17 at 18:12
No, you don't. This means that the first field of the file should go in the `fullname` column of the table, the second field should go in the `sourcenam1` column. You only need `SET` if you're doing something other than just putting a CSV field directly into a table column. – Barmar Nov 09 '17 at 18:13
Hmmm.... When I tried to view the imported data, all I see if that it is the empty set i.e. mysql> SELECT * FROM Person; Empty set (0.00 sec) – billyl320 Nov 09 '17 at 18:18
Note: I got this when I ran your suggestion Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 – billyl320 Nov 09 '17 at 18:19
I've included an example csv file – billyl320 Nov 09 '17 at 18:25

Importing CSV into MySQL with White Space

1 Answers1