2

I am a using MySQL locally to my Mac. I have a CSV file all fields enclosed in double quotes. Here is an extract of the first rows of my CSV file:

$ head -5 companies.csv
"COMPANY_ADDRESS1","COMPANY_ADDRESS2","COMPANY_CITY","COMPANY_COUNTRY","COMPANY_FAX","COMPANY_GERMANY","COMPANY_ID","COMPANY_INDIANAPOLIS","COMPANY_NAME","COMPANY_STATE","COMPANY_TELEPHONE","COMPANY_VAT","COMPANY_ZIP"
"980 Madison Avenue","6th Floor","New York","USA","","","1393","","Lucky Fives LLC","NY","212-74-2313","","10075"
"1209 Orange Street","","Wilmington","USA","","","1394","","Global Five Holdings","DE","-","","19801"
"2020 Ponce de Leon Boulevard","PH2","Coral Gables","USA","","1015110","1395","","Lion Aero Corporation","FL","305-755-5120","","33134"
"100 NE Adams Street","","Peoria","USA","","","1396","","Caterpillar Inc.","IL","+13096755975","","61630"

And here is my import command:

$ mysqlimport --local --ignore-lines=1 my_db companies.csv -u root --fields-enclosed-by='\"' --lines-terminated-by='\r\n' --fields-terminated-by=','

I am getting all records imported, nothing is skipped:

companies: Records: 3063  Deleted: 0  Skipped: 0  Warnings: 104

However, why I am getting all data imported in the first column and all other columns are populated with NULL?

The table was previously created with all columns necessary to support the data to be loaded.

L.D
  • 1,279
  • 3
  • 15
  • 31
  • MySQL doesn't need the double-quote escaped `\"` and it is already escaped for the shell because it's enclosed in single quotes `'\"'` -- so you are defining _fields-enclosed-by_ to be _backslash-quote_, **not** simply _quote_. Try with `--fields-enclosed-by='"'` – Stephen P Jun 20 '16 at 19:31
  • same problem when the backslash is not used, I can use ' " ' or ' \" ', same result, all date gets into the first column. – L.D Jun 20 '16 at 19:54
  • I ran the changed command in mostly the same way and it worked fine. This is the command I ran: `mysqlimport -u username -p --local --ignore-lines=1 myschema tablename.csv --fields-enclosed-by='"' --lines-terminated-by='\n' --fields-terminated-by=','` (... schema/table/user replaced from my _actual_ command) I terminated by just `\n` not `\r\n` because I created the csv file on my Mac (OS X is Unix) – Stephen P Jun 20 '16 at 20:27
  • Run almost the same and got half imported: $ mysqlimport -u root --local --ignore-lines=1 rr_ccdb companies.csv --fields-enclosed-by='"' --lines-terminated-by='\n' --fields-terminated-by=',' rr_ccdb.companies: Records: 1532 Deleted: 0 Skipped: 0 Warnings: 1613 – L.D Jun 20 '16 at 20:36

2 Answers2

2

I think mysqlimport is looking for a backslash double quote to enclose fields, and it's not finding any, so it's all one field.

I'm questioning the purpose of the backslash character before the double quote. If this is for Linux bash shell, I don't think the backslash is required to specify the double quote. As a demo:

# echo foo --fields-enclosed-by='\"'
foo --fields-enclosed-by=\"

# echo foo --fields-enclosed-by='"'
foo --fields-enclosed-by="

(This is just a guess, I could be wrong. I've been wrong plenty times before. It's possible that mysqlimport is discarding that backslash character that's being passed to it. The issue might be something else. But for a test, I'd give it whirl without the backslash, and see how big of a smokeball it makes without it.)

Demo environment:

# uname -o -r -s
Linux 3.13.0-83-generic GNU/Linux

# echo $SHELL
/bin/bash
spencer7593
  • 106,611
  • 15
  • 112
  • 140
1

Test schema:

-- drop table if exists aT;
create table aT
(   -- address thing
    COMPANY_ADDRESS1 varchar(100) not null,
    COMPANY_ADDRESS2 varchar(100) not null,
    COMPANY_CITY varchar(100) not null,
    COMPANY_COUNTRY varchar(100) not null,
    COMPANY_FAX varchar(100) not null,
    COMPANY_GERMANY varchar(100) not null,
    COMPANY_ID varchar(100) not null,
    COMPANY_INDIANAPOLIS varchar(100) not null,
    COMPANY_NAME varchar(100) not null,
    COMPANY_STATE varchar(100) not null,
    COMPANY_TELEPHONE varchar(100) not null,
    COMPANY_VAT varchar(100) not null,
    COMPANY_ZIP varchar(100) not null
);

Command run interactively or via a program:

LOAD DATA INFILE 'c:\\nate\\aT.csv' INTO TABLE aT
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;
select * from aT;

or Linux:

LOAD DATA INFILE '/home/nate/aT.csv' INTO TABLE aT
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;

No problem bringing the data in

select * from aT; -- view the import

Now for command line (create a .sql of one of the above):

truncate aT; -- run this first

mysql -u root -p so_gibberish2 < c:\nate\run_this1.sql

so_gibberish2 is the database name. Data comes in. Because the mysql client is not subject to bash or other command line escape sequences.

Drew
  • 24,851
  • 10
  • 43
  • 78
  • My file is from a Microsoft system as is coded WESTERN - ISO LATIN1. The load fails since my MySQL database is encode UTF-8. Getting the error ERROR 1300 (HY000): Invalid utf8 character string: '"N'. How the command LOAD DATA can be updated to count for the encoding difference? I was able to load only after converting the CSV file to UTF-8, but this is not an option for large files. – L.D Jun 21 '16 at 09:12
  • If I had a dropbox file from you for testing, (a medium small sized nasty one), I could test it. Also if I were to know what would be allowed to make it work, such as a staging table for an import or the exact create table statement you have on the receiving end. I would not want to spend time doing something that would be rejected if that info could have been communicated before I began. – Drew Jun 21 '16 at 10:19
  • Sorry, I discovered the encoding matter only after I attempted the import in Postgres, which was successful. MySQL proved to me many shortcomings. This is one of them. Getting all data in one column instead of providing an error and rejecting the load. Postgres has a parameter to be set for encoding during the load. For MySQL I had to save the file with UTF-8 encoding before having the load succeeding. In a nutshell, without Postgres I would not able to fix it for MySQL. Is not this matter something MySQL needs to address? – L.D Jun 21 '16 at 10:58
  • As mentioned, knowing your create table stmt would shed some light. That has not been provided. Perhaps this is of assistance: http://stackoverflow.com/q/4957900 – Drew Jun 21 '16 at 11:04
  • This is the create table statemet executed identically in MySQL and Postgres. Not sure how this could help. create table companies ( company_address1 varchar(255), company_address2 varchar(255), company_city varchar(255), company_country varchar(255), company_fax varchar(255), company_germany varchar(255), company_id varchar(255), company_indianapolis varchar(255), company_name varchar(255), company_state varchar(255), company_telephone varchar(255), company_vat varchar(255), company_zip varchar(255) ); – L.D Jun 21 '16 at 11:35
  • Did you test anything with the `LOAD DATA INFILE` in relation to the character set seen in the above reference, such as this [answer](http://stackoverflow.com/a/14397450) from JMHeap ? – Drew Jun 21 '16 at 11:39
  • 1
    Yes, I did, same outcome, no difference. I already moved to Postgres. No problems, no troubles. – L.D Jun 23 '16 at 00:21