A stored function to guess which line terminator (‘\r’ or ‘\r\n’) to use with LOAD DATA INFILE
CREATE FUNCTION whichLineTerminator(fileName varchar(255)) RETURNS varchar(20) NOT DETERMINISTIC BEGIN DECLARE cr_count int; DECLARE lf_Count int; DECLARE f text; SET f := LOAD_FILE(fileName); IF f IS NULL THEN RETURN 'Cannot read file.'; END IF; SET cr_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\r', '')); SET lf_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\n', '')); IF cr_count >= lf_count THEN RETURN '\\r\\n'; ELSE RETURN '\\n'; END IF; END
