Avatar of rrattie
rrattieFlag for United States of America asked on

PHP read and insert tab delimited file into MySQL db

What I'm trying to do:


I'm uploading a tab delimited text file and then reading it via PHP.
Then I'm trying to turn the rows of text into an array to insert into a MySql database.

I feel very close to getting this, but I've run out of Mountain Dew and need some new eyes.

My problem:

This is the error I receive:
0: INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the first question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the first que' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the second question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the second qu' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the third question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the third que' at line 1
 INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('33','this is the fourth question','4','1')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('33','this is the fourth qu' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1 
INSERT into questions ('examid', 'question', 'type', 'flag_active') VALUES ('','','','')
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''examid', 'question', 'type', 'flag_active') VALUES ('','','','')' at line 1

Open in new window





The file looks like:

33	this is the first question	4	1
33	this is the second question	4	1
33	this is the third question	4	1
33	this is the fourth question	4	1

Open in new window



My read the file, convert to array and insert code looks like this:

$readfile=file($uploadPath.$name);
	for($k=0; $k<=count($readfile); $k++)
    {
      $fields=explode("\r",$readfile[$k]);
	  for($i=0; $i<=count($fields); $i++) 
	  {
	  $values=explode("\t",$fields[$i]);
		$columns = "'examid', 'question', 'type', 'flag_active'";
      $query=("INSERT into questions ($columns) VALUES           ('$values[0]','$values[1]','$values[2]','$values[3]')");
		echo $query;
        mysql_query($query) ;
		echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
	  }
	}

Open in new window

PHPMySQL ServerSQL

Avatar of undefined
Last Comment
Chris Stanyon

8/22/2022 - Mon
Gary

Does the file have headers?
ASKER
rrattie

no, no column headers just the rows of data.
Gary

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);
}

Open 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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Stanyon

Remove the quotes from around your field names:

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

Open in new window

Ray Paseur

You might want to check out fgetcsv()
ASKER
rrattie

@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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

Maybe you have blank lines in the file!!
Gary

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);

Open in new window

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

@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" } }

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gary

$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";
}

Open in new window

ASKER
rrattie

@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";
		  }

Open 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:

Open in new window


And it did insert 1 row, but only 1.
Chris Stanyon

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);
}

Open in new window

Once that's working, we can move onto the database bits (PDO or mySQLi)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gary

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

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.
Gary

I must be typing slow.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ray Paseur

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
}

Open in new window

HTH, ~Ray
Chris Stanyon

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

@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" } }

Open 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);
				}
			}
		  

Open 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" }

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gary

Can you attach a sample of the source data.
ASKER
rrattie

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
SOLUTION
Gary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chris Stanyon

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ray Paseur

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
}

Open in new window

ASKER
rrattie

@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);
}
?>

Open in new window

Chris Stanyon

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.