Problem with tab delimited text file insert

Hi folks,

We have an internal project where we want to import several text files (list of places, county and page number) to a database so that we later can proof-read and combine all data into one text.
I have set up the php file but when I try to import it only inserts the first line of the tabdelimited text file. I can see in the first record in the table that there is a line shift after the last “column” and that the next place (first “column” in next line in the text) comes on the second line. This should of course be next record.
I guess there someting wrong with my script (not the text file).
Any help would be greatly appreciated.
Thank you!

if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0 )
{  
$fileName = $_FILES['userfile']['name'];  
$tmpName = $_FILES['userfile']['tmp_name'];  

$handle = fopen($tmpName, "r"); 

$sql = "INSERT INTO `mapindex` (`date`, `place`,`fylke`,`mappageinfo`) 
 VALUES ";
$values = array();
while(!feof($handle)) { 
  $data = explode("\t", fgets($handle));
   $place = mysql_real_escape_string($data[0]);
   $fylke = mysql_real_escape_string($data[1]);
   $mappage = mysql_real_escape_string($data[2]);
   $values[] = " (LOCALTIMESTAMP(), '$place', '$fylke', '$mappage')";
    }
$str_values = implode("\n",$values);
$sql .= $str_values;
$qry = mysql_query($sql) or die(mysql_error());
    
echo "<br>File $fileName uploaded<br>";  

}  

The script read online the first line you mean?

It reads the first line of the tab text file plus the first word (column) of the second line…

…but the first word on second line is being inserted into the last field of the first record. After a line shift.

The Problem with your script is that it’s read the Line of the Text file but when it’s found the
\n as every end of line , it will consider it NULL and stop reading the except text on your File. On your
script string perform this functionality. I suggest to change your script and try to use another one instead of string

Thank you for your reply!
I have tried many other ways to do this but none has worked for me.
Do you know some examples or tutorials I can look at?

Looks like a good place to use prepared statements in PDO rather than the no-longer-supported mysql functions. Instead of building a massive (presumably) array of values, prepare the query and then repeatedly bind the data as you read it from the text file.

$sql = "INSERT INTO `mapindex` (`date`, `place`,`fylke`,`mappageinfo`) VALUES (LOCALTIMESTAMP(), :place, :fylke, :mappage)";
$r = $dbc->prepare($sql);
while(!feof($handle)) { 
  $data = explode("\t", fgets($handle));
   $r->bindParam(':place', $data[0]);
   $r->bindParam(':fylke', $data[1]);
   $r->bindParam(':mappage', $data[2]);
   $r->execute();
    }
$r->CloseCursor();

I’m not sure if the LOCALTIMESTAMP part will work as part of a prepared statement, and obviously $dbc refers to a PDO database connection. You might also be able to split the bindParam() functions out before the loop, I’m not sure whether that would cause a problem or whether it’s just bindValue() that doesn’t like that approach.

Try this

$file = $_FILES[‘frmUpload’][‘tmp_name’]; // Get Temporary filename
if ($file) {
$handle = fopen($file,“r”); // Open the file and read
while($strBookData = fgetcsv($handle, 10000, " ")) {//To get Array from CSV, " "(delimiter)
$strDatas = $strBookData;
$strTableColumn = count($strBookData); // To Get Column count
}
}

Thank you for your replies. Had to run yesterday…
I have now changed the script to use PDO (new at this).
But when I try to import the file I get the same result. I have tried to use a csv file instead (separated with “;”), but I get the same result. Only one record inserted and still the first column (place) in second line of the text file in the third field (mappageinfo) after a line break. Like this:
place1 county1 mappageinfo1 (line break) place2

For fun I tried to replace the semicolon delimiter with a comma (comma separated value). Then all lines in my csv file (328 lines) was insterted in the place field in the table. Like this:
place1;county1;mappageinfo1
place2;county2;mappageinfo2
place3;county3;mappageinfo3…

$host = 'xxx';
$db   = 'xxx';
$user = 'xxx';
$pass = 'xxx';

$dsn = "mysql:host=$host;dbname=$db";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0 )
{  
$fileName = $_FILES['userfile']['name'];  
$tmpName = $_FILES['userfile']['tmp_name'];  

$handle = fopen($tmpName, "r"); 

$sql = "INSERT INTO `mapindex` (`place`,`fylke`,`mappageinfo`) VALUES (:place, :fylke, :mappage)";
$r = $pdo->prepare($sql);
while(!feof($handle)) { 
  $data = explode(";", fgets($handle));
   $r->bindParam(':place', $data[0]);
   $r->bindParam(':fylke', $data[1]);
   $r->bindParam(':mappage', $data[2]);
   $r->execute();
    }
$r->CloseCursor();    

echo "<br>File $fileName uploaded<br>";  
}  

What’s the end-of-line character in your CSV file? It sounds to me like fgets() isn’t recognising the end of line character, so it’s loading the entire file into a single line. That would account for why the ‘mappage’ field contains the last field of the first line, the newline character (which fgets should terminate at) and the first field of the second line. If you echo $data[3] you’ll probably see the second field on the second line.

There’s a note on the fgets manual page ( http://php.net/manual/en/function.fgets.php ) that talks about end of line delimiters that might help.

Wonderful:-)
Thank you very much!

I added ```
ini_set(“auto_detect_line_endings”, true);

on top of the script and then it got imported as I was hoping for.
It was a Mac issue I guess.

Excellent, glad it’s working.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.