[Fr3dY]

Hi guys..i'm new here..so help me if u can all right..thanks in advanve..
i'm new to php however i need to find a script where i'm able to import my excel or CVS file to mySQL. There is only 2 columns of data namely Date and Price. Do any of you have any idea?I have searched around but can't really understand some of the solutions/codes provided (i'm new..remember??)..And due to some restrictions i have, it is not convenient for me to buy any softwares such as Navicat or DB Tools Manager Pro. Therefore i need to use php scripts. In addition, i also need to extract the 2 columns of data from mySQL and view it in a webpage(html). FYI, I'm using mambo CMS to build my website. I will be very grateful if anyone who can help me.
Thanks..god bless..
Fredy

[NeverMind]

ok Fredy, this will be easy
save your Excel file as a CSV file.
now use this script which will take the data from the file and put them in a mysql table:

PHP Code:

/*   DO MYSQL CONNECTION HERE    */



$file = 'DatePrice.csv'; //file name here



//we read the CSV file here

$lines = file($file);



//now we take each line and explode it then insert to DB

foreach ($lines as $line) {

   $cols = explode(';', $line);

   mysql_query("INSERT INTO table (Date, Price) VALUES ('$cols[0]', '$cols[1]')")or

   die(mysql_error());

}



echo mysql_affected_rows(); 

of course you have to change "table" to the table name you have as well as the field names ..

now, to show the data from mysql use something like this:

PHP Code:

/*   DO MYSQL CONNECTION HERE   */



//fetch data from here

$fetch = mysql_query("SELECT * FROM table")or

die(mysql_error());



while ($row = mysql_fetch_assoc($fetch))

   echo "$row[Date] - $row[Price]"; 

and again you have to change the names of the table and fileds


there is another way to do it with COM, but it's kinda advanced for you now since you said you are new

HTH

[moronovich]

and in php-4.3.10 release, the COM is broken (except you get the new one from php snap).

[Fr3dY]

Wow...it works..thanks a million NeverMind..however i still have a little tiny problem..hehe..u see i have managed to insert the data into mySQL table (huge relief) and i have also managed to extract it successfully but the output is not what i expected.
The output i get are:
Date - Price1-Jan - Sat2-Jan - Sun3-Jan - USD 162.504-Jan - USD 165.005-Jan

My data in csv file are like this: (in columns of course)
Date | Price
1-jan | Sat
2-Jan | Sun
3-Jan | USD 162.50
4-Jan | USD 165.00

So i would like to ask you is it possible to show the results in columns?
what do you mean by

PHP Code:

echo "$row[Date] - $row[Price]"; 

?

I need to ask you another thing and that is if i would like to upload the CSV file through an upload interface box like the way you upload image or any other file by clicking on "upload" and you can choose the file u want and uploaded it directly into mySQL. Is this possible to have an upload script together with the codes you provided me to change CSV file to mySQL? So that the administrator will only have to look for the file and uploaded it and the file would be inserted straight into mySQL table. The reason i need to do this is because the site i'm building now does not allow the administrator to access the codes or database. Only the "super administrator" have access to them.

Thanks a million again NeveMind..i will be very grateful if u can help me out on this.God bless you..

Cheers,
Fredy

[NeverMind]

glad it works
to show the data in columns replace:

PHP Code:

while ($row = mysql_fetch_assoc($fetch))

   echo "$row[Date] - $row[Price]"; 

with:

PHP Code:

echo '<table>

  <tr>

   <td><b>Date</b></td>

   <td><b>Price</b></td>

  </tr>';



while ($row = mysql_fetch_assoc($fetch))

   echo "

  <tr>

    <td>$row[Date]</td>

    <td>$row[Price]</td>

  </tr>"; 



echo '</table>'; 

and about that upload script which will do the magic work by iteself, yes it can be done easily as well ..
all you have to do is instead of predefining the variable $file ..
make the script read the variable $_FILES['fileupload']['tmp_name'] instead after the file has been uploaded ..
look at:
http://www.php.net/manual/en/features.file-upload.php
to get more info about file uploads ..
if you got stuck post here again and I will help

[Fr3dY]

Hi NeverMind...thanks again for the help..unfortunately i'm still not very sure how to do the upload part. I am using the manual as my guide. I have managed to create this page.

Code:

<!-- <!-- The data encoding type, enctype, MUST be specified as below -->
<form enctype="multipart/form-data" action="__URL__" method="POST">
    <!-- MAX_FILE_SIZE must precede the file input field -->
    <input type="hidden" name="MAX_FILE_SIZE" value="30000" />
    <!-- Name of input element determines name in $_FILES array -->
    Send this file: <input name="userfile" type="file" />
    <input type="submit" value="Send File" />
</form>

Next i proceed to do this

PHP Code:

<?php

// In PHP versions earlier than 4.1.0, $HTTP_POST_FILES should be used instead

// of $_FILES.



$uploaddir = '/var/www/uploads/';

$uploadfile = $uploaddir . basename($_FILES['userfile']['name']);



echo '<pre>';

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) {

   echo "File is valid, and was successfully uploaded.\n";

} else {

   echo "Possible file upload attack!\n";

}



echo 'Here is some more debugging info:';

print_r($_FILES);



print "</pre>";



?>

My question is how do i combine this code (the one that you teach me) with the one above??:

PHP Code:

*   DO MYSQL CONNECTION HERE    */ 



$file = 'DatePrice.csv'; //file name here 



//we read the CSV file here 

$lines = file($file); 



//now we take each line and explode it then insert to DB 

foreach ($lines as $line) { 

$cols = explode(';', $line); 

mysql_query("INSERT INTO table (Date, Price) VALUES ('$cols[0]', '$cols[1]')")or 

   die(mysql_error()); 

} 



echo mysql_affected_rows(); 

So sorry for the trouble, i have been looking round but can't really get the idea. Thank you so much again for your help..God bless you..

Fredy (your student)

[Beck]

there is such an easy simple about your problem...

http://www.websoldier.net/index.php?...&article_id=51

check it... if you will have any problem i will be waiting for help...

[NeverMind]

ok Fredy, here is the full solution..
create this php file and name it as you like..

PHP Code:

<?php

if (!isset($_POST['SFCSV'])) {

   echo '<form enctype="multipart/form-data" action="'.$_SERVER['PHP_SELF'].'" method="POST">

    Send this file: <input name="SFCSVfile" type="file" />

    <input type="submit" name="SFCSV" value="Send File" />

</form>';

} else {

   $file = $_FILES['SFCSVfile']['tmp_name'];



//we read the CSV file here

   $lines = file($file);



/*   DO MYSQL CONNECTIO HERE    */



//now we take each line and explode it then insert to DB

   foreach ($lines as $line) {

   $cols = explode(';', $line);

   mysql_query("INSERT INTO table (Date, Price) VALUES ('$cols[0]', '$cols[1]')")or

      die(mysql_error());

   }



   echo mysql_affected_rows(); 

}

?>

this should do and I tested it and it works fine
the script will upload a file and reads its contents and then insert them..
the uploaded file will remain in the temp folder on your server and will be deleted eventually..

as for Beck's solution, it uses COM and is good..
but it's not cross-platform! because the server has to be Windows and it has to have Microsoft Office Excel installed on it..
however if you want to use his solution, just add the upload form with this if (isset($_POST['SFCSV'])) parts and change the file name to $_FILES['SFCSVfile']['tmp_name'] and it should work fine

[Fr3dY]

Once again u saved the day...hehe..thank u so much for the solution..
its perfect..and everything is going smoothly...i feel bad to ask u again..but might as well..since you're the "man"..
i have managed to create a border for my columns by using
echo <table border = "1">, no problem at all on that.
However, i need to ask you what if i need to overwrite the data in the mySQL everytime i upload CSV file?How do i do that?
Do i have to 1st check whether there is existing data in the table before overwriting it? If yes, how do i do it?Is it using the UPDATE syntax?
Can you show how is it done?

thank you very much again..you have my utmost gratitude.

[NeverMind]

you could use REPLACE INTO but you have to have an index key (primery, unique or whatever) to make REPLACE INTO overwrite existing data ..
REPLACE INTO has the same pattern as INSERT INTO but it looks for index keys and if they exist in a row, it removes it and put the new one.. otherwise it inserts it as a new record ..

if you don't want to use it, you could check the data first and then UPDATE as you said..