By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,665 Members | 1,900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,665 IT Pros & Developers. It's quick & easy.

using php to move rows from text file into mysql table

P: n/a
Hello
Can anyone point me in the right direction for the way to read a text file a
line at a time and separate the fields on that line and use them as data in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.

Thanks for your time
Ian
Jun 19 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Rik
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.netwrote:
Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as data
in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.
--
Rik Wasmus
Jun 19 '07 #2

P: n/a
Rik
On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.comwrote:
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:
>Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.

Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.
Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus
Jun 19 '07 #3

P: n/a
thanks rik,
will look into your suggestions tomorrow.
ian

"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...
On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>
wrote:
>
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:
Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line
in
text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus

Jun 19 '07 #4

P: n/a

"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...
On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>
wrote:
>
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:
Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line
in
text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$datetime) = explode(",", $data);
//echo
$userid."<br>".$companyid."<br>".$aimlisted."<br>" .$amount."<br>".$price."<b
r>".$stamp."<br>".$comm."<br>".$datetime;
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
'$datetime')";
$add_buy = mysql_query($q) or die('<span class=RedWarning>Query failed: '
.. mysql_error().'</span>');

}
fclose($handle);
**************************************

I echoed the variables from the list (commented out in script) and they to
show they contain no data.
can anyone see where Ive gone wrong?

Ian

Jun 20 '07 #5

P: n/a
Rik
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@virgin.netwrote:
>
"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...
>On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>
wrote:
>>
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line
in
>text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL
can
do it practically on it's own.

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query
failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,
$datetime) = explode(",", $data);
Well, the power of fgetscv() is in the fact that your should not need this
explode.
Try the list() directly on the $data array. If that doesn't work,
print_r($data) to check wether everything is OK.

--
Rik Wasmus
Jun 20 '07 #6

P: n/a

"Rik" <lu************@hotmail.comwrote in message
news:op.tt8oweggqnv3q9@metallium...
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@virgin.netwrote:
>
"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...
>On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>
wrote:
>>
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line
in
>text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL
can
do it practically on it's own.

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query
failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,
$datetime) = explode(",", $data);
Well, the power of fgetscv() is in the fact that your should not need this
explode.
Try the list() directly on the $data array. If that doesn't work,
print_r($data) to check wether everything is OK.

--
Rik Wasmus
That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and time.
All other fields update ok
Any clues.

Ian
Jun 21 '07 #7

P: n/a
Rik
On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@virgin.netwrote:
That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and
time.
That is what happens if MySQL gets a string it cannot interpret as a
datetime field. Check the format of the date you have in the file, and
adjust it before inserting to MySQL's format.

--
Rik Wasmus
Jun 21 '07 #8

P: n/a

"Rik" <lu************@hotmail.comwrote in message
news:op.tt9gsfppqnv3q9@metallium...
On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@virgin.netwrote:
That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and
time.

That is what happens if MySQL gets a string it cannot interpret as a
datetime field. Check the format of the date you have in the file, and
adjust it before inserting to MySQL's format.

--
Rik Wasmus
Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?

Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.
Jun 21 '07 #9

P: n/a
..oO(mantrid)
>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?
Yep, but doesn't matter in this case.
>Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.
Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.

Micha
Jun 21 '07 #10

P: n/a
In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:
.oO(mantrid)
>>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?
Yep, but doesn't matter in this case.
>>Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.
Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.
Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something goes
screwy with the date and you send 'HH:MM:SS' first it will try to interpret
that as the date HH-MM-SS. For MM over 12 and SS over 31 that will always
produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own function
to manipulate the date string is good advice that will always work. If you
have a MySQL client on the pertinent server and a sandbox database, the best
way to determine exactly what your MySQL wants is to try it from the MySQL
client. You cannot expect to use PHP to write a query line that works if
you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/ <http://myspace.com/larseighner>
Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and other
USENET abusers, I do not see most posts from googlegroups.
Jun 21 '07 #11

P: n/a
Hello

I used phpmyadmin to run an sql to insert a record into the table. The only
one that worked was

INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')

ie Using a date in the correct format as the mysql table and in quotes (hrs
mins secs were not needed), leave out the quotes or in any other format and
it doesnt work.

ian
"Lars Eighner" <us****@larseighner.comwrote in message
news:sl********************@goodwill.larseighner.c om...
In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:
.oO(mantrid)
>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd
hh:mm:ss,
>it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?
Yep, but doesn't matter in this case.
>Do you know a php function that does a similar thing? I know the date
format
>characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.
Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.

Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something
goes
screwy with the date and you send 'HH:MM:SS' first it will try to
interpret
that as the date HH-MM-SS. For MM over 12 and SS over 31 that will always
produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own
function
to manipulate the date string is good advice that will always work. If
you
have a MySQL client on the pertinent server and a sandbox database, the
best
way to determine exactly what your MySQL wants is to try it from the MySQL
client. You cannot expect to use PHP to write a query line that works if
you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/>
<http://myspace.com/larseighner>
Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and
other
USENET abusers, I do not see most posts from googlegroups.

Jun 21 '07 #12

P: n/a
"mantrid" <ia********@virgin.netwrote in message
news:oj****************@newsfe2-gui.ntli.net...
Hello

I used phpmyadmin to run an sql to insert a record into the table. The
only
one that worked was

INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')

ie Using a date in the correct format as the mysql table and in quotes
(hrs
mins secs were not needed), leave out the quotes or in any other format
and
it doesnt work.

ian
"Lars Eighner" <us****@larseighner.comwrote in message
news:sl********************@goodwill.larseighner.c om...
In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:
.oO(mantrid)
>>Is it best to do that in Excel or use a php date format function?
>>In Excel it is dd/mm/yyy
>>but it wont allow me to save as a CVS file in the form yyy-mm-dd
hh:mm:ss,
>>it just changes it back.
>>ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the
sql
>>statement but its not working, am I using this function wrongly?
Yep, but doesn't matter in this case.
>>Do you know a php function that does a similar thing? I know the date
format
>>characters (eg d M Y etc) but can only see examples of their use with
>>date(), which is the current date, not a date from my CVS file.
Try strtotime(). If it doesn't work as expected (for example
dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and
create
your own date string in MySQL format.
Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something
goes
screwy with the date and you send 'HH:MM:SS' first it will try to
interpret
that as the date HH-MM-SS. For MM over 12 and SS over 31 that will
always
produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own
function
to manipulate the date string is good advice that will always work. If
you
have a MySQL client on the pertinent server and a sandbox database, the
best
way to determine exactly what your MySQL wants is to try it from the
MySQL
client. You cannot expect to use PHP to write a query line that works
if
you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/>
<http://myspace.com/larseighner>
Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and
other
USENET abusers, I do not see most posts from googlegroups.


ok got it

had to change

$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, "','")) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;

to

$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000)) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;
that will teach me not to just cut and paste others peoples use of the
functions before reading them properly.
and

$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";
to
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";

the insertion of the quotes around $dateandtime allowed it to be accepted. I
also didnt need to manipulate $dateandtime to change it from dd/mm/yyyy to
yyyy-mm-dd, this appears to have happened automatically when using fgetcsv()
or possibly at the mysql insert stage.

Thanks all for the help

Ian
Jun 21 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.