Search code examples
mysqldatabasewindowscsvmamp

LOAD DATA INFILE error: #1265 - Data truncated for column X at row 1

Using the following query:

LOAD DATA INFILE 'Path/OfFile'
INTO TABLE sample
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(file_path, name, size, length, pack_id);

The following error is generated:

#1265 - Data truncated for column 'length' at row 1

The first row (apparently the source of the error) of the CSV File (not including the ignored line) is:

"C:\Path","Filename.wav","2230852","8","1"

The "8" corresponds to the "length" field that is being complained about... what could be being truncated?

Is there an error in the CSV or Query?

Note: 'file_path' and 'name' are type VARCHAR(256) 'size' is type BIGINT UNSIGNED 'length' and 'pack_id' are type INTEGER UNSIGNED

Solution

  • After many hours of searching and trial-and-error, it was discovered that the problem was the file paths (from a windows machine) were stored with backslashes instead of forward ones, resulting in the confounding error message.

    Hopefully someone can benefit from the turmoil and anguish :)