rrattie
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:
The file looks like:
My read the file, convert to array and insert code looks like this:
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
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
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";
}
}
Does the file have headers?
ASKER
no, no column headers just the rows of data.
Your php is a bit messy, this is what I use
That should give you a correct array.
You could then either add the sql into the loop or loop the array again.
$input = file('import.txt',FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$filedata= array();
foreach ($input as $line) {
$filedata[] = explode("\t",$line);
}
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";
You might want to check out fgetcsv()
ASKER
@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.
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
Note you have to change import.txt to your file name
$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);
And let me know what you get.Note you have to change import.txt to your file name
ASKER
@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:
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" } }
$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";
}
ASKER
@GaryC123
This: (removed the single quotes around the column names)
produced this:
And it did insert 1 row, but only 1.
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";
}
produced this:
0: INSERT into questions (examid, question, type, flag_active) VALUES ('33','this is the first question','4','1 33') 0:
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);
}
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?
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
}
HTH, ~Ray
@ray - the same problem will exist as using file()...The line endings aren't being recorgnized!
ASKER
@GaryC123
That code gave me:
I changed it to:
and got the following:
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" } }
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);
}
}
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" }
Can you attach a sample of the source data.
ASKER
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
testfile.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If you enabled the auto_detect_line_endings in your php.ini, you may have to restart your apache server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This worked for me.
http://www.laprbass.com/RAY_temp_rrattie.php
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
}
ASKER
@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.
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);
}
?>
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.
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.