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
12  4380 
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
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
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
"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
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
"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
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
"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.
..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
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.
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.
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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()...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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,...
|
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...
| |
