Search code examples
csvmariadbload-data-infile

LOAD DATA INFILE consistently skips first line, while not set to IGNORE

I'm trying to load a csv file with stock prices into a prices table. The csv file has 3 lines that I want to ignore, including a blank one. When I set IGNORE 1 LINES, it runs into an error processing the column headers. When I set IGNORE 2 LINES, it consistently skips the first line of data. All other data is loaded just fine, starting from the 2nd data row ("11-03-2020" in this case).

How do I fix this, without changing the data in the csv?

The csv looks like this:

"Some instructions"

"date";"price"
"12-03-2020";133.08
"11-03-2020";143.68
"10-03-2020";149.14
...

The CREATE TABLE code:

CREATE TABLE `prices` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `isin` CHAR(12) NOT NULL,
    `price_date` DATE NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `isin_date` (`isin`, `price_date`),
    CONSTRAINT `prices_stocks` FOREIGN KEY (`isin`) REFERENCES `stocks` (`isin`) ON UPDATE CASCADE ON DELETE NO ACTION
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=157532
;

The LOAD DATA SQL statement:

LOAD DATA LOCAL INFILE 'price_history_LU0792910050.csv'
        REPLACE INTO TABLE stock_db.prices
            CHARACTER SET utf8
            FIELDS TERMINATED BY ';'
            OPTIONALLY ENCLOSED BY '"'
            ESCAPED BY '"'
            LINES TERMINATED BY '\r\n'
            IGNORE 2 LINES
            (@vdate, @vprice)
        SET
            isin = 'LU0792910050',
            price_date = STR_TO_DATE(@vdate, '%d-%m-%Y'),
            price = @vprice;

hexdump of an example csv:

00000000: EF BB BF 22 44 65 20 69  6E 73 74 65 6C 6C 69 6E  ..."De instellin
00000010: 67 65 6E 20 76 61 6E 20  75 77 20 45 78 63 65 6C  gen van uw Excel
00000020: 2D 73 6F 66 74 77 61 72  65 20 6B 75 6E 6E 65 6E  -software kunnen
00000030: 20 64 65 20 77 65 65 72  67 61 76 65 20 76 61 6E   de weergave van
00000040: 20 6F 6E 64 65 72 73 74  61 61 6E 64 65 20 67 65   onderstaande ge
00000050: 67 65 76 65 6E 73 20 62  65 C3 AF 6E 76 6C 6F 65  gevens be..nvloe
00000060: 64 65 6E 20 64 6F 6F 72  20 64 65 20 63 65 6C 6F  den door de celo
00000070: 70 6D 61 61 6B 20 76 61  6E 20 64 65 20 67 65 67  pmaak van de geg
00000080: 65 76 65 6E 73 63 61 74  65 67 6F 72 69 65 20 28  evenscategorie (
00000090: 62 69 6A 76 2E 20 61 61  6E 74 61 6C 20 69 6E 20  bijv. aantal in 
000000A0: 70 6C 61 61 74 73 20 76  61 6E 20 64 61 74 75 6D  plaats van datum
000000B0: 29 2E 22 0D 0A 0D 0A 22  64 61 74 65 22 3B 22 70  )."...."date";"p
000000C0: 72 69 63 65 22 0D 0A 22  31 35 2D 30 37 2D 32 30  rice".."15-07-20
000000D0: 32 30 22 3B 35 31 2E 37  36 0D 0A 22 31 34 2D 30  20";51.76.."14-0
000000E0: 37 2D 32 30 32 30 22 3B  35 31 2E 31 37 0D 0A 22  7-2020";51.17.."
000000F0: 31 33 2D 30 37 2D 32 30  32 30 22 3B 35 31 2E 30  13-07-2020";51.0
00000100: 33 0D 0A 22 31 30 2D 30  37 2D 32 30 32 30 22 3B  3.."10-07-2020";

(Hexdump was not installed on Synology, so used Python hexdump. Hope this works)

Solution

  • "12-03-2020" cannot be put directly be put into a DATE column. Instead, put it into an @ variable, then use str_to_date(...). (Let us know if you need help; there are many examples floating around.)

    I see C3 AF, which is utf8 for ï, as in ... beïnvloegen ... -- Does that sound "right"? CHARACTER SET utf8 should have read it correctly.

    The initial EF BB BF is "BOM". I don't know if LOAD FILE is smart enough to silently skip over it. This may be causing your problem. One approach is to edit the file to remove the first 3 bytes.

    Later comes 0D 0A 0D 0A, which matches your description of there being 3 header lines, the second being blank. And LINES TERMINATED BY '\r\n' should be correct for that.