1

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?

  • Here is an example: pastebin.com/waaLTqGh Edited original post to include create table. – raphael75 Feb 13 '17 at 16:10
  • 1
    Check if your csv file has BOM. If yes, this make the value of the first col from first col invalid int, and will be replaced by 0. – Jehad Keriaki Feb 13 '17 at 16:17
  • 1
    It looks like it's likely the BOM that's causing the issue. I'm exploring how to remove it. – raphael75 Feb 13 '17 at 17:51
  • 2
    That was it! The data was being exported from a Sybase database. In the Sybase output sql, I added "byte order mark off" and that fixed it. Jehad, if you add that as the answer I will select it. More info here: infocenter.sybase.com/help/index.jsp?topic=/… – raphael75 Feb 13 '17 at 17:56
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.