MySQL Load Data – Load Data Local Infile Skipping First Column of First Row

linuxloadMySQL

We have a PHP script that has been running from the command line on a WAMP server for a few years. I am now converting it to run on Linux (AWS EC2 AMI instance). The script basically loads data from a csv into a mysql table. It is almost working but for some reason it skips the data in the 1st column of the 1st row. Here is the sql:

LOAD DATA LOCAL INFILE '/var/scripts/_output/ref_dtl_output.csv'
REPLACE INTO TABLE  MY_TABLE.ref_dtl

FIELDS TERMINATED BY ','
ENCLOSED BY ''

LINES TERMINATED BY '\n'

The files it's loading are anywhere from a few hundred bytes to > 100K bytes. In every case, whether it's getting thousands of rows, or 1 row, the 1st column of the 1st row is always "0" or an empty string. I have checked every csv, and there is data there. This never happened on the Windows server.

I tried running the SQL above from within the PHP script, and I also tried using HeidiSQL to import it and got the same results.

CREATE TABLE `ref_dtl` (
`site_id` INT(11) NOT NULL,
    `business_date` DATE NOT NULL,
    `trans_seq` INT(10) NOT NULL,
    `dtl_seq` INT(10) NOT NULL,
    `parent_trans_seq` INT(10) NOT NULL,
    `parent_dtl_seq` INT(10) NOT NULL,
    `ref` CHAR(20) NOT NULL,
    `ref_type` CHAR(3) NOT NULL,
    PRIMARY KEY (`site_id`, `business_date`, `trans_seq`, `dtl_seq`, `parent_trans_seq`, `parent_dtl_seq`),
    INDEX `site_id` (`site_id`),
    INDEX `business_date` (`business_date`),
    INDEX `trans_seq` (`trans_seq`),
    INDEX `dtl_seq` (`dtl_seq`),
    INDEX `parent_trans_seq` (`parent_trans_seq`),
    INDEX `parent_dtl_seq` (`parent_dtl_seq`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Any ideas for how to fix?

Best Answer

Depending on the source of the text file, the csv file here, it may have the byte of order (BOM) character.

In this case, the very first column from fist row will be an invalid integer, and will be saved as 0 in the DB.

To check if the file has BOM letters at its beginning, refer to this link.

Related Question