473,574 Members | 2,582 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

using php to move rows from text file into mysql table

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
12 4402
Rik
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@vir gin.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
Rik
On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************ @hotmail.comwro te:
On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@vir gin.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
thanks rik,
will look into your suggestions tomorrow.
ian

"Rik" <lu************ @hotmail.comwro te in message
news:op.tt6t46t 6qnv3q9@metalli um...
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********@vir gin.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

"Rik" <lu************ @hotmail.comwro te in message
news:op.tt6t46t 6qnv3q9@metalli um...
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********@vir gin.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,companyi d,aimlisted,amo unt,price,stamp ,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
'$datetime')";
$add_buy = mysql_query($q) or die('<span class=RedWarnin g>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
Rik
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@vir gin.netwrote:
>
"Rik" <lu************ @hotmail.comwro te in message
news:op.tt6t46t 6qnv3q9@metalli um...
>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********@vir gin.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

"Rik" <lu************ @hotmail.comwro te in message
news:op.tt8oweg gqnv3q9@metalli um...
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@vir gin.netwrote:
>
"Rik" <lu************ @hotmail.comwro te in message
news:op.tt6t46t 6qnv3q9@metalli um...
>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********@vir gin.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
Rik
On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@vir gin.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

"Rik" <lu************ @hotmail.comwro te in message
news:op.tt9gsfp pqnv3q9@metalli um...
On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@vir gin.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
..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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
7147
by: grumfish | last post by:
I'm trying to add a row to a MySQL table using insert. Here is the code: connection = MySQLdb.connect(host="localhost", user="root", passwd="pw", db="japanese") cursor = connection.cursor() cursor.execute("INSERT INTO edict (kanji, kana, meaning) VALUES (%s, %s, %s)", ("a", "b", "c") ) connection.close() After running, a SELECT * on the...
3
17799
by: Tomo | last post by:
I'm trying to import text file into some oracle table. I created control file, and text file, create table ..and run sqlldr80 from command promt.. Imort was not succesfull..error occurs..sql*loader-524:partial record found at end of data file (proba.ctl) See what i done, and please help me.. CREATING TABLE ******************* create tabale...
4
2589
by: Dave | last post by:
Hello all, The scheme shown below to move a text file's contents into a std::string works with one exception: it drops the carriage return and line feed characters. How may I, in a Standard-compliant way, read in a text file's contents and keep the carriage returns and line feeds? Thanks, Dave
9
2746
by: carl.barrett | last post by:
Hi, Let me explain. I have been given a file that I need to bring into an Access table. Here is a snippet of the file. 100833983 1 MRS M I BATTY 1 000001 00833983
3
2439
by: Dave G | last post by:
I will shortly be receiving data in the form of a text file, like this: id: 123456 first name: Fred surname: Bloggs age: 26 and so on, for about 60 fields. Each line ends with a carriage return/line feed.
1
4748
by: Osoccer | last post by:
...to a different folder and in the relocated file concatenates all of the lines in one long string with a space between each line element. Here is a fuller statement of the problem: I need a Visual Basic Script file, call it "Move and Reformat Text File.VBS," that will run from a Windows Script Host command-prompt-based version as follows: ...
2
3380
by: mamin | last post by:
I have a table that contains file name, file seq num, etc. I would like to compare the two rows to make sure the file seq num is being incremented by one or more. If it is incremented >1 then I l know that there is a file missing.
2
2791
by: prateekbtbl | last post by:
Hello Sir, I have a table with userID and articleID, now if a user access an article it is stored in this table, how do i write a query where no duplicate entry for article viewed by a user is store. after that i need total count of a user to fetch and store in another table. Can anyone help me writing a query. Thanks in advance Prateek...
1
1822
by: phpmel | last post by:
Hi guys, I am new to this stuff and i have NO IDEA what to do. I have a text file where each line is a value for the name of a department.There are 500 departments.how do I insert that into a mysql database in a table.Please explain fully.the id for the table is an auto number so that is not a problem Thanks alot
8
8348
by: fishnfrogs | last post by:
Basically, I need to insert a million rows into a mysql table from a .txt file. The "easiest" way I can think about doing this is loading the file into an AIR application and have it strip down into 1000 workable .txt files of 1000 lines. Then after that is done, use the app to upload each file one after the other in a loop. Can anyone tell me...
0
7807
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8241
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7820
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8100
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5624
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5301
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1061
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.