CODEWITHSUNDEEP
phpmysqlload-data-infile
BGTabulation BGTabulate
BGTabulation BGTabulate

Reputation: 1737

Load data infile skipping 1 row in every each rows

I'm trying to upload my csv file using mysql's Load Data infile syntax, but upon uploading the data on my database it loads only half the rows and I notice its a patchy row. It's like all of even numbers only have inserted and the odd does not.

Here's my sample format of csv file:

storeid,txndate,productcategory1,qty,totalamt,uploaddate
"1100","19JAN2019","ADD ONS","1363","333.59","20JAN2019"
"1100","19JAN2019","KFC LP","58","1736.96","20JAN2019"
"1100","19JAN2019","KFC SP","269","1093.02","20JAN2019"
"1100","19JAN2019","LTO","26","495.39","20JAN2019"
"1100","19JAN2019","VALUE","71","534.13","20JAN2019"
"1102","19JAN2019","ADD ONS","244","32.5","20JAN2019"
"1102","19JAN2019","KFC LP","9","239.91","20JAN2019"
"1102","19JAN2019","KFC SP","70","277.63","20JAN2019"
"1102","19JAN2019","LTO","3","88.48","20JAN2019"
"1102","19JAN2019","VALUE","18","99.95","20JAN2019"
"1104","19JAN2019","ADD ONS","930","124.32","20JAN2019"
"1104","19JAN2019","KFC LP","21","680.79","20JAN2019"
"1104","19JAN2019","KFC SP","196","971.11","20JAN2019"
"1104","19JAN2019","LTO","17","338.84","20JAN2019"

Here's my load data infile script

$loadData = sprintf("
    LOAD DATA local INFILE '%s' IGNORE INTO TABLE prodmix 
    CHARACTER SET UTF8 FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' 
    LINES TERMINATED BY '\\n' IGNORE 1 LINES 
    (
        @storeid,
        @txndate,
        @productcategory1,
        @qty, 
        @totalamt, 
        @uploaddate
    )
    SET
    id=null, 
    storeid=@storeid, 
    txndate=@txndate, 
    productcategory1=@productcategory1,
    qty=@qty,
    totalamt=@totalamt,
    uploaddate=@uploaddate,
    unique_row=CONCAT(@txndate,'_',@storeid,'_',
    @productcategory1,'_',@qty,'_',@totalamt,'_',@uploaddate),
    created_at=now()
    ", addslashes($absolute_path));
    if(DB::connection()->getpdo()->exec($loadData)){
        //$total_success = $total_success +1;
        $response['status'] = 'success';                    
    }

What might be causing every other row to fail.

Upvotes: 0

Views: 522

Answers (1)

MarcoS
MarcoS

Reputation: 17711

Are you sure your local data csv file line termination character is '\n' and not for example '\r\n' ?

Upvotes: 1

Related Questions