Determining line terminator on import

[gerardg]

Anybody have a full-proof way of determining what the line terminator is (or having mysql automagically figuring it out :-) in a text file (\n or \r\n) before using 'load data infile ...'. The only thing I can think of is reading the first few lines of the file and checking the last two chars/last char.

[gerardg]

Hmmm, that doesn't work. It never detects '\r'. ???

while (!feof($fp)) {
$line = fgets($fp, 1000);

if (strpos($line, "\r") > 0) {
echo('\r\n');
} elseif (strpos($line, "\n") > 0) {
echo('\n:');
}

echo($line . '<br>');
}

Going through each line and getting the ascii codes it's always '0A 00' and never '0D 0A' (\r\n), even though I'm sure \r\n is in the file.

?

[[deleted]]

The most 'fullproof' way is to let the user decide. Annoying, but there are so many formats that trying to guess it is very complex.

[gerardg]

That's who I'm trying to foolproof it against. Most users probably won't have a clue whether the line terminator is \n or \r\n. Most text editors will handle both so it's hard to tell that way. Platform's no help either since it's quite easy to have files with \n's on windows and vice versa.

[[deleted]]

Here's a script that seems a little hairy but does a good job of finding the delimiter, line breakes. It also figures out if the fields are enclosed in quotes or not.

// COUNTING THE QUOTES
$fp = fopen ($file,"r");
$line = fgets($fp, 1000);
$quote_array = explode("\"", $line);
// COUNTING THE TABS
$fp = fopen ($file,"r");
$line = fgets($fp, 1000);
$tab_array = explode("\t", $line);
fclose ($fp);
// COUNTING THE COMMAS
$fp = fopen ($file,"r");
$line = fgets($fp, 1000);
$comma_array = explode(",", $line);
fclose ($fp);
// COUNTING THE \Ns
$fp = fopen ($file,"r");
$line = fread($fp, 10000);
$n_array = explode("\n", $line);
fclose ($fp);
// COUNTING THE \Rs
$fp = fopen ($file,"r");
$line = fread($fp, 10000);
$r_array = explode("\r", $line);
fclose ($fp);
// COUNTING THE \R\Ns
$fp = fopen ($file,"r");
$line = fread($fp, 10000);
$rn_array = explode("\r\n", $line);
fclose ($fp);
$tabcount = count($tab_array);
$commacount = count($comma_array);
$rcount = count($r_array);
$ncount = count($n_array);
$rncount = count($rn_array);
$quotecount = count($quote_array);
$halfquote = (($quotecount)/2);

$maxcommatab = max($commacount, $tabcount);
$maxterm = max($rcount, $ncount, $rncount);
if ($maxterm==$rcount) {
$term="\r";
}
if ($maxterm==$ncount) {
$term="\n\";
}
if ($maxterm==$rncount) {
$term="\r\n";
}
//echo "the term is $term<br>";

if ($commacount < $tabcount) {
$delimit="\t";
} else {
$delimit=",";
}

$quotediff = ($maxcommatab-$halfquote-$blankcount);

if ($quotediff < 0) {
$quotediff = ($quotediff*(-1));
}

if ($quotediff < 5) {
echo "the fields are enclosed in quotes<br>";
$enclosed = " enclosed by '\"'";
echo "$enclosed<br>";
}
$query="load data infile \"/path/to/$import_file\" into table $temp_table fields terminated by '$delimit'$enclosed lines terminated by '$term'";