Load csv file into mysql database

Question

I'm trying to load a big CSV file into mysql but couldn't find out why it fails.

My CSV file looks like this:

_id,"event","unit","created","r1","r2","r3","r4","space_id","owner_id","name","display_name","users__email"
565ce313819709476d7eaf0e,"create",3066,"2015-12-01T00:00:19.604Z","563f592dd6f47ae719be8b38","3","13","7","55ecdd4ea970e6665f7e3911","55e6e3f0a856461404a60fc1","household","household","foo.bar@ace.com"
565ce350819709476d7eaf0f,"complete",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"
565ce350819709476d7eaf0f,"delete",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"
565ce350819709476d7eaf0f,"update",3067,"2015-12-01T00:01:19.988Z","21","","","","55e6df3ba856461404a5fdc9","55e6e3f0a856461404a60fc1","Ace","Base","foo.bar@ace.com"

And my code to load the file into mysql is this one:

CREATE DATABASE IF NOT EXISTS analys;

USE analys;

CREATE TABLE IF NOT EXISTS event_log (
  _id CHAR(24) NOT NULL,
  event_log VARCHAR(255),
  unit CHAR(4),
  created VARCHAR(255),
  r1 VARCHAR(255),
  r2 VARCHAR(255),
  r3 VARCHAR(255),
  r4 VARCHAR(255),
  space_id VARCHAR(255),
  owner_id VARCHAR(255),
  name VARCHAR(255),
  display_name VARCHAR(255),
  users__email VARCHAR(255),
  PRIMARY KEY (_id)
)

LOAD DATA INFILE 'audits.export.csv' 
INTO TABLE event_log
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n\r'
IGNORE 1 ROWS;

Everything is fine, including the query but I get NULL in every column (only one row).

Here is the Action Output:

22:31:21    LOAD DATA INFILE 'audits.export.csv'  INTO TABLE event_log FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n\r' IGNORE 1 ROWS   0 row(s) affected Records: 0  Deleted: 0  Skipped: 0  Warnings: 0   0.156 sec

I tried to tweak the table and the load query but it doesn't work

I'm on Windows 7, using Mysql 5.6 and Workbench. I heard about GUI solution or Excel connectors here (Excel Connector) but I prefer to do it programmaticaly as I need to reuse the code.

Any help? I couldn't solve the problem with similar posts on Stackoverflow.

It can be data issue and it can be formatting issue ....take one of the rows and try to insert into mysql using insert into blah values ("Ace","Base","foo.bar@ace.com") .... and take it from there as a part of debugging process. use "mysql workbench" or something — z atef, Jan 12 '16 at 15:54
Check your file is not one line .... hence , you are saying LINES TERMINATED BY '\n\r' .... you can do that like this : cat -vet filename.csv — z atef, Jan 12 '16 at 15:58

score 0 · Answer 1 · answered Jan 12 '16 at 16:22

0

This doens't seem a valid newline:

LINES TERMINATED BY '\n\r'

change to:

LINES TERMINATED BY '\r\n'

Or only one of them (could be single \n or \r depending on the system, or the software that created the csv file).

answered Jan 12 '16 at 16:22

Danny_ds

11,201
1
24
46

Same. I tweaked the code, especially this part as the encoding could have been done on Unix or Windows but it doesn't change anything. Good query but 0 rows affected – Synleb Jan 12 '16 at 16:26
@Synleb - Strange. And the filename is correct too I assume? i.e. not needing a slash/backslash after audits (or a full path)? – Danny_ds Jan 12 '16 at 16:34
@Synleb - I don't know if this is the case in your real data, but te primary key field contains duplicate values. – Danny_ds Jan 12 '16 at 16:42
Path is correct and I tried with the bavkslash. It doesn't change anything. For the duplicate it's not the case in real data. I copy pasted the 2nd line. – Synleb Jan 12 '16 at 16:43
With only the four lines I provided (without duplicates on _id) I get another error: **Error Code: 1261. Row 1 doesn't contain data for all columns** – Synleb Jan 12 '16 at 16:47
@Synleb - Are you sure there are no hidden characters in your data? (maybe manually type in a short one to test?) - (Long shot: maybe change _id to another name). – Danny_ds Jan 12 '16 at 17:19
I iterated by removing fields in order to come to the simplest form of my audit log. If found out that the problem is the double quote `"`. If I keep my audit log as-is, it doesn't log anything. If I do a find and replace to remove all the double quotes, and it start loading. BUT: as I have some comas in strings, by removing the double quotes, I get another error **Error Code: 1262. Row 405 was truncated; it contained more data than there were input columns** – Synleb Jan 12 '16 at 23:52

score 0 · Answer 2 · answered Jan 12 '16 at 23:53

Because some might come here wanting to know how to do this with MySQL Workbench:

Save CSV data to a file, for example foo.csv
Open MySQL Workbench
Choose/Open a MySQL Connection
Right-click on a schema in the Object Browser (left Navigator)
Choose "Table Data Import Wizard"
Select the CSV file, which is foo.csv in our example
Follow the wizard; many configuration options are available, including the separator
When finished, the CSV data will be in a new or existing table (your choice)

For additional information, see the documentation titled Table Data Export and Import Wizard.

I just tested this in the example data provided in the question, and it worked.

Thanks @Philip. But I'd rather like a code based solution than a GUI one as I want to implement the loading in a script. I know the problem is the double quotes but I don't know why as double quotes are one of my parameters in the load query — Synleb, Jan 13 '16 at 07:53

Load csv file into mysql database

2 Answers2