LOAD DATA LOCAL INFILE - only one row gets inserted

Question

I have recently crossed over from SQL and still having some trouble time to time. Could anyone help me please with this blocker. I am sure this is some silly mistake I am making but I can't pass it.

Then I have created table:

create table FFX_DCM_RawData (
    ID int,
    Campaign varchar (256),
    Site_DFA varchar (256),
    Placement varchar (256),
    Report_Date varchar (256),
    Creative varchar (256),
    Ad varchar (256),
    Impression integer,
    Clicks integer,
    Media_Cost decimal (5,2),
    MobileClickToCall_Buy_PostClick integer,
    MobileClickToCall_Buy_PostView integer,
    MobileClickToCall_Rent_PostClick integer,
    MobileClickToCall_Rent_PostView integer,
    MobileEmailEnquiry_Buy_PostClick integer,
    MobileEmailEnquiry_Buy_PostView integer,
    MobileEmailEnquiry_Rent_PostClick integer,
    MobileEmailEnquiry_Rent_PostView integer,
    DesktopClickToReveal_Buy_PostClick integer,
    DesktopClickToReveal_Buy_PostView integer,
    DesktopClickToReveal_Rent_PostClick integer,
    DesktopClickToReveal_Rent_PostView integer,
    DesktopEmailEnquiry_Buy_PostClick integer,
    DesktopEmailEnquiry_Buy_PostView integer,
    DesktopEmailEnquiry_Rent_PostClick integer,
    DesktopEmailEnquiry_Rent_PostView integer
);

and inserted via INFILE...

LOAD DATA LOCAL INFILE 'Users//Jakub//Desktop//DCM Data_All Channels.csv' INTO TABLE test.FFX_DCM_RawData
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Id, Campaign, Site_DFA, Placement, Report_Date,
Creative, Ad, Impression, Clicks, Media_Cost, MobileClickToCall_Buy_PostClick, MobileClickToCall_Buy_PostView,
MobileClickToCall_Rent_PostClick, MobileClickToCall_Rent_PostView, MobileEmailEnquiry_Buy_PostClick,
MobileEmailEnquiry_Buy_PostView, MobileEmailEnquiry_Rent_PostClick, MobileEmailEnquiry_Rent_PostView,
DesktopClickToReveal_Buy_PostClick, DesktopClickToReveal_Buy_PostView, DesktopClickToReveal_Rent_PostClick,
DesktopClickToReveal_Rent_PostView, DesktopEmailEnquiry_Buy_PostClick, DesktopEmailEnquiry_Rent_PostClick,
DesktopEmailEnquiry_Rent_PostView
);

but the result is always that only one row gets inserted. Anyone knows where I am going wrong?

The better choice is to use HeidiSQL http://www.heidisql.com/screenshots.php?which=import_textfile — Suganthan Madhavan Pillai, Sep 14 '14 at 11:25

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

I am not sure, but this is defaults in heidisql while csv upload

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ESCAPED BY '"'

Also if you using excel you can refer this answer as well

edited May 23 '17 at 10:24

Community

1
1

answered Sep 14 '14 at 11:30

Suganthan Madhavan Pillai

5,495
9
51
84

Thanks for your responds however even adding '\r\n' didn't solve my issue. Not sure what else I should try – Jakub Drhlik Sep 14 '14 at 23:52

LOAD DATA LOCAL INFILE - only one row gets inserted

1 Answers1