PHP read and insert tab delimited file into MySQL db

Does the file have headers?

no, no column headers just the rows of data.

Your php is a bit messy, this is what I use

$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
    $filedata[] = explode("\t",$line);
}

Select allOpen in new window

That should give you a correct array.
You could then either add the sql into the loop or loop the array again.

Remove the quotes from around your field names:

$columns = "examid, question, type, flag_active";

Select allOpen in new window

You might want to check out fgetcsv()

@ChrisStanyon

That got me up to being able to do my insert and actually inserting the data into the table. However, I ended up with 3 extra rows for some reason.

@GaryC123

Could you expand on what you meant?  I gave your code a shot and wasn't able to get it to work properlly.

Maybe you have blank lines in the file!!

Add var_dump after the code above

$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
    $filedata[] = explode("\t",$line);
} 
var_dump($filedata);

Select allOpen in new window

And let me know what you get.
Note you have to change import.txt to your file name

@ChrisStanyon

I cannot see any blank lines, that was my first assumption too. But if I open the file in a text editor that shows the line break and tab characters there aren't any extra.

@GaryC123

Here is the var_dump:

0: array(1) { [0]=> array(13) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" 
[2]=> string(1) "4" [3]=> string(4) "1 33" [4]=> string(27) "this is the second question" [5]=>
string(1) "4" [6]=> string(4) "1 33" [7]=> string(26) "this is the third question" [8]=> string(1) 
"4" [9]=> string(4) "1 33" [10]=> string(27) "this is the fourth question" [11]=> string(1) "4" 
[12]=> string(1) "1" } }

Select allOpen in new window

$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

$filedata= array();
foreach ($input as $line) {
	$filedata[] = explode("\t",$line);
} 

$columns = "examid,question,type, flag_active";
foreach ($filedata as $line) {
	$query=("INSERT into questions ($columns) VALUES ('$line[0]','$line[1]','$line[2]','$line[3]')");
	echo $query . "\n";
	mysql_query($query) ;
	echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
}

Select allOpen in new window

@GaryC123

This: (removed the single quotes around the column names)

$input=file($uploadPath.$name,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
		  
		  $filedata= array();
		  foreach ($input as $line) {
			  $filedata[] = explode("\t",$line);
		  } 
		  
		  $columns = "examid, question, type, flag_active";
		  
		  foreach ($filedata as $line) {
		  $query=("INSERT into questions ($columns) VALUES ('$line[0]','$line[1]','$line[2]','$line[3]')");
		  echo $query . "\n";
		  mysql_query($query) ;
		  echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
		  }

Select allOpen in new window

produced this:

0: INSERT into questions (examid, question, type, flag_active) VALUES ('33','this is the first question','4','1 33') 0:

Select allOpen in new window

And it did insert 1 row, but only 1.

It looks like your data is being read into the array incorrectly. You end up with one big array, instead of a separate array for each line. You file's end of line markers are not being read correctly. Forget about the database bits for a minute (you need to move away from the mysql library anyway) and just concentrate on reading your file.

$input = file('data.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$filedata=array();

foreach ($input as $line) {
   $filedata = explode("\t",$line);
   var_dump($filedata);
}

Select allOpen in new window

Once that's working, we can move onto the database bits (PDO or mySQLi)

There is something wrong with your source data, it is coming in as one line of data.
Can you attach it?

Note: If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem.

I must be typing slow.

It looks like you may be overthinking this problem.

<?php // RAY_fgetcsv.php
error_reporting(E_ALL);

// SHOW HOW TO READ A CSV FILE 

// NAME OF THE CSV FILE
$csv = 'RAY_sample_csv.csv';

// OPEN THE FILE FOR READING
$fpo = fopen($csv, 'r');
if (!$fpo) die("CANNOT OPEN $csv");

// READ THE ROWS
while (!feof($fpo))
{
    $arr = fgetcsv($fpo);
    var_dump($arr);
    
    // CONSTRUCT AND RUN YOUR INSERT QUERY HERE
}

Select allOpen in new window

HTH, ~Ray

@ray - the same problem will exist as using file()...The line endings aren't being recorgnized!

@GaryC123

That code gave me:
 

0: array(1) { [0]=> array(13) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" 
[2]=> string(1) "4" [3]=> string(4) "1 33" [4]=> string(27) "this is the second question" [5]=>
string(1) "4" [6]=> string(4) "1 33" [7]=> string(26) "this is the third question" [8]=> string(1) 
"4" [9]=> string(4) "1 33" [10]=> string(27) "this is the fourth question" [11]=> string(1) "4" 
[12]=> string(1) "1" } }

Select allOpen in new window

Which is what I got last time I ran it (like above).

I changed it to:

$input=file($uploadPath.$name,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
		  
			$filedata=array();
			
			foreach ($input as $line) {
			   $filedata = explode("\r",$line);
			   foreach ($filedata as $thing) {
				   $value = explode("\t",$thing);
				   
			   var_dump($value);
				}
			}
		  

Select allOpen in new window

and got the following:

0: array(4) { [0]=> string(2) "33" [1]=> string(26) "this is the first question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(27) "this is the second question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(26) "this is the third question" [2]=> string(1) "4" [3]=> string(1) "1" } 
array(4) { [0]=> string(2) "33" [1]=> string(27) "this is the fourth question" [2]=> string(1) "4" [3]=> string(1) "1" }

Select allOpen in new window

Can you attach a sample of the source data.

I've also enabled the auto_detect_line_endings in the php.ini file.  Yes, the attached file was created on a Mac.
testfile.txt

Your file has a CR (Carriage Return) line ending because it was created on a Mac. Normal files have a CRLF (Carriage Return Line Feed).

If you enabled the auto_detect_line_endings in your php.ini, you may have to restart your apache server.

This worked for me.
http://www.laprbass.com/RAY_temp_rrattie.php

<?php // RAY_temp_rrattie.php
error_reporting(E_ALL);
echo '<pre>';

// SHOW HOW TO READ A CSV FILE
// http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28236830.html#a39484705

// NAME OF THE CSV FILE
$csv = 'http://filedb.experts-exchange.com/incoming/2013/09_w37/676827/testfile.txt';

// COPY AND REPAIR THE FILE
$txt = file_get_contents($csv);
$arr = explode("\r", $txt);
$txt = implode(PHP_EOL, $arr);
file_put_contents('RAY_temp_rrattie.csv', $txt);

// OPEN THE REPAIRED CSV
$fpo = fopen('RAY_temp_rrattie.csv', 'r');
if (!$fpo) die("CANNOT OPEN 'RAY_temp_rrattie.csv'");

// READ THE ROWS
while (!feof($fpo))
{
    $arr = fgetcsv($fpo,0,"\t");
    var_dump($arr);
    
    // CONSTRUCT THE QUERY HERE
}

Select allOpen in new window

@ChrisStanyon


The code below worked like a charm. Hopefully the client doesn't mine me using PDO, the current staff code it all in regular mysql.


Thanks Chris for your help!, Gary thanks for your assistance as well.

<?php
//sort out line endings
ini_set('auto_detect_line_endings',true);

//connect to your database
$dbUser = "username";
$dbPass = "password";
$conn = new PDO('mysql:host=localhost;dbname=yourDB', $dbUser, $dbPass);

//read the file into an array
$input = file('yourFile.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

//prepare the SQL statement
$query = $conn->prepare("INSERT INTO questions (examid, question, type, flag_active) VALUES (?,?,?,?)");

//loop through the array and insert the database records
$filedata=array();
foreach ($input as $line) {
    $filedata = explode("\t",$line);
    $query->execute($filedata);
}
?>

Select allOpen in new window

They certainly shouldn't mind you switching to PDO. All those that use the old mysql library will also have to switch at some point (to either PDO or mysqli), so they should be following your lead :)

If they do mind, point them to any of the PHP mysql pages - they'll see a big red warning !!

Thanks for the points and good luck with it.