0

I have a trouble in importing my sample data to my table in MySQL.

MySQL table is like this:

CREATE TABLE `draft_client` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_name` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

In my csv I have a sample output like this:

38,"ADERL INC./UNITED, INC."
115,AETOS HOLDINGS INC.
64,"Another Lane Gateway (Philippines), Inc."

The first column is for client ID and the 2nd is for client_name. I imported the csv like this:

LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/mysite/client_new.csv'
INTO TABLE draft_client
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

And in my table I have an output like this:

enter image description here

I used HeidiSQL for this.

can you help me?

Jerielle
  • 7,144
  • 29
  • 98
  • 164
  • @Cine I already checked that. :-) – Jerielle Oct 03 '16 at 05:31
  • Maybe my wrong is in the string itself. When I checked some client name is enclosed in double quote (""). I'll try to update my csv file. :) – Jerielle Oct 03 '16 at 05:35
  • 1
    if you are enclosed by or optionally enclosed by. Yours don't match. Enclose your fields with double quotes. The dupe target points to the manual. `LOAD DATA INFILE` is somewhat forgiving, but you have to meet it half-way – Drew Oct 03 '16 at 05:35
  • @Drew, ok sir I'm already doing that. :) thanks for the suggestion – Jerielle Oct 03 '16 at 05:39
  • Your data has to match your specification in the load data infile command. It is not. Your schema is important too (not shown). If you don't have a header row, don't "IGNORE 1 ROWS" – Drew Oct 03 '16 at 05:40
  • @Drew, I already show my table. – Jerielle Oct 03 '16 at 05:42
  • @Drew, thanks I already imported my csv file. :) – Jerielle Oct 03 '16 at 05:48
  • txt file: http://i.imgur.com/Uh8qN79.jpg .... code: http://i.imgur.com/AV64sFP.jpg – Drew Oct 03 '16 at 05:56
  • 1
    Change the `ENCLOSED BY '"'` to `OPTIONALLY ENCLOSED BY '"'`, and remove the `IGNORE 1 ROWS` - that should do it. – Anse Oct 11 '16 at 14:15

0 Answers0