|
PHP - Parsing Excel Files
my issue is each time a user selcts a category or sub category, the excel file stored in server is fetched, read and output its contents and then closed again.....its quite cumbersome cos what if multiple users were logged into the system doing this same process....server load wud b definitely high......so,i would like to parse excel files and store it as an array so that data can be retrieved from the array rather than opening the file,fetching data and closing it, then again opening it,fetching it and closing and so on.....i am thinking of reading the data into an array and then unset the array if a different category or subcategory is selected....is this solution even possible?
Similar TutorialsI am looking for some help with extracting links from log files, as it is a pain to do this manually (which I do right now). I basically have some log files which I need to check for ERROR messages and copy and paste the found URL's into another text file. My log file format looks like this: Code: [Select] INFO <11 Feb 2012 00:00:23,822> <index> <D2> <Processing URL : http://www.domain1.com/> INFO <11 Feb 2012 00:00:23,842> <index> <D4> <Indexed: http://www.domain2.com/> <Time:146 msecs> INFO <11 Feb 2012 00:00:23,842> <index> <D4> <Processing URL : http://www.domain3.com/> ERROR <11 Feb 2012 00:00:23,924> <index> <D1> <http://www.domain4.org/operas/2003-2004/mourning/composer.aspx: > org.apache.commons.httpclient.HttpRecoverableException: org.apache.commons.httpclient.HttpRecoverableException: Error in parsing at org.apache.commons.httpclient.HttpMethodBase.readResponse(HttpMethodBase.java:1965) at org.apache.commons.httpclient.HttpMethodBase.processRequest(HttpMethodBase.java:2659) at org.apache.commons.httpclient.HttpMethodBase.execute(HttpMethodBase.java:1093) at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:674) at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:529) at com.searchblox.scanner.http.HTTPScanner.b(Unknown Source) at com.searchblox.scanner.http.HTTPScanner.scan(Unknown Source) at com.searchblox.scanner.http.HTTPScanner.work(Unknown Source) at com.searchblox.scanner.Scanner.run(Unknown Source) at java.lang.Thread.run(Unknown Source) INFO <11 Feb 2012 00:00:23,968> <index> <D5> <Indexed: http://domain6.com/~cdobie/kearnsindex.htm> INFO <11 Feb 2012 00:00:23,968> <index> <D5> <Indexed: http://domain7.com/~cdobie/kearnsindex.htm> INFO <11 Feb 2012 00:00:32,988> <index> <D1> <Processing URL : http://www.domain8.com/> INFO <11 Feb 2012 00:00:33,072> <index> <D5> <Indexed: http://www.domain9.com/> <Time:128 msecs> INFO <11 Feb 2012 00:00:33,072> <index> <D5> <Processing URL : http://www.domain10.com/> ERROR <11 Feb 2012 00:00:33,116> <index> <D2> <http://www.domain11.com/: Connection timeout> org.apache.commons.httpclient.HttpConnection$ConnectionTimeoutException at org.apache.commons.httpclient.HttpConnection.open(HttpConnection.java:736) at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:661) at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:529) at com.searchblox.scanner.http.HTTPScanner.b(Unknown Source) at com.searchblox.scanner.http.HTTPScanner.scan(Unknown Source) at com.searchblox.scanner.http.HTTPScanner.work(Unknown Source) at com.searchblox.scanner.Scanner.run(Unknown Source) at java.lang.Thread.run(Unknown Source) INFO <11 Feb 2012 00:00:33,154> <index> <D1> <Indexing http://www.domain12.com/ ...> INFO <11 Feb 2012 00:00:33,159> <index> <D1> <http://www.domain13.com/ - Last-Modified date: Sat Feb 11 00:00:33 CET 2012> ERROR <11 Feb 2012 00:00:33,207> <index> <D6> <http://www.domain14.com/: Connection timeout> Now what I am after is some piece of code which basically saves the http://domain.com/ part to a text file IF the line starts with ERROR. There are many different error reasons, so the strings are all different at the start and at the end, so maybe you know a way to open a log file, look out for the word ERROR at the beginning of a line and if that's the case, either save the whole line to another text file or if possible just the domain part (which would be even more great) If possible, please post a fully functional code block, as I am extremely bad with anything that has to do with regex, opening and closing files etc. Your help would be greatly appreciated I attached a sample log file to this post in case it helps (same as the lines above) This topic has been moved to PHP Regex. http://www.phpfreaks.com/forums/index.php?topic=317036.0 I need some help and hopefully will respond. I'm trying to put some information to the second row of the excel sheet once extracted. Can somebody help me with this script? I'm trying to move the address line to the second row. Thanks. Hello, i am currently getting an Microsoft Excel formatted text file whose save type is .Txt from a URL.I used to open it and will change the save type as excel file. Please suggest whether we can do this with php code. currently my code is like this, <? php copy("http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/emergencyplanexport.cfm?Region=&District=&State=&County=&City=LAS%20VEGAS&Use=&Certification=","./contactsexport.xls"); ?> where as the contactsexport.xls type is .Txt which i need it in .xls Thanks in Advance. So far I have managed to create an upload process which uploads a picture, updates the database on file location and then tries to upload the db a 2nd time to update the Thumbnails file location (i tried updating the thumbnails location in one go and for some reason this causes failure) But the main problem is that it doesn't upload some files Here is my upload.php
<?php
include 'dbconnect.php';
$statusMsg = '';
$Title = $conn -> real_escape_string($_POST['Title']) ;
$BodyText = $conn -> real_escape_string($_POST['ThreadBody']) ;
// File upload path
$targetDir = "upload/";
$fileName = basename($_FILES["file"]["name"]);
$targetFilePath = $targetDir . $fileName;
$fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION);
$Thumbnail = "upload/Thumbnails/'$fileName'";
if(isset($_POST["submit"]) && !empty($_FILES["file"]["name"])){
// Allow certain file formats
$allowTypes = array('jpg','png','jpeg','gif','pdf', "webm", "mp4");
if(in_array($fileType, $allowTypes)){
// Upload file to server
if(move_uploaded_file($_FILES["file"]["tmp_name"], $targetFilePath)){
// Insert image file name into database
$insert = $conn->query("INSERT into Threads (Title, ThreadBody, filename) VALUES ('$Title', '$BodyText', '$fileName')");
if($insert){
$statusMsg = "The file ".$fileName. " has been uploaded successfully.";
$targetFilePathArg = escapeshellarg($targetFilePath);
$output=null;
$retval=null;
//exec("convert $targetFilePathArg -resize 300x200 ./upload/Thumbnails/'$fileName'", $output, $retval);
exec("convert $targetFilePathArg -resize 200x200 $Thumbnail", $output, $retval);
echo "REturned with status $retval and output:\n" ;
if ($retval == null) {
echo "Retval is null\n" ;
echo "Thumbnail equals $Thumbnail\n" ;
}
}else{
$statusMsg = "File upload failed, please try again.";
}
}else{
$statusMsg = "Sorry, there was an error uploading your file.";
}
}else{
$statusMsg = 'Sorry, only JPG, JPEG, PNG, GIF, mp4, webm & PDF files are allowed to upload.';
}
}else{
$statusMsg = 'Please select a file to upload.';
}
//Update SQL db by setting the thumbnail column to equal $Thumbnail
$update = $conn->query("update Threads set thumbnail = '$Thumbnail' where filename = '$fileName'");
if($update){
$statusMsg = "Updated the thumbnail to sql correctly.";
echo $statusMsg ; }
else
{ echo "\n Failed to update Thumbnail. Thumbnail equals $Thumbnail" ; }
// Display status message
echo $statusMsg;
?>
And this does work on most files however it is not working on a 9.9mb png file which is named "test.png" I tested on another 3.3 mb gif file and that failed too? For some reason it returns the following Updated the thumbnail to sql correctly.Updated the thumbnail to sql correctly. Whereas on the files it works on it returns REturned with status 0 and output: Retval is null Thumbnail equals upload/Thumbnails/'rainbow-trh-stache.gif' Failed to update Thumbnail. Thumbnail equals upload/Thumbnails/'rainbow-trh-stache.gif'The file rainbow-trh-stache.gif has been uploaded successfully. Any idea on why this is? Hello I have a simple question about file handling... Is it possible to list all files in directories / subdirectories, and then read ALL files in those dirs, and put the content of their file into an array? Like this: array: [SomePath/test.php] = "All In this php file is being read by a new smart function!"; [SomePath/Weird/hello.txt = "Hello world. This is me and im just trying to get some help!";and so on, until no further files exists in that rootdir. All my attempts went totally crazy and none of them works... therefore i need to ask you for help. Do you have any ideas how to do this? If so, how can I be able to do it? Thanks in Advance, pros I am using WPSQT plugin in my blog site .I code some files in PHP also.how to add that files in plugin files.
I need to parse xls files (Microsoft Excel). The option to tell the users who will be uploading the files to convert it to a more friendly format like XML or CSV is not an option (I know its unfortunately, though I have to make it so it parses those formats as well (fun is)). Users must be able to just save the file, and upload it as is. below is a link to a example xls file. So I am looking to see if there's anything native to php that will allow me to open and read xls formatted files quickly and efficiently and work with the values independently on a per row per column basis when outputting them. Or if there's a recommended library/class I can use that someone here can suggest. My biggest issue is I am on a time crunch so I need something I can work with quickly and efficiently all around. Any samples would be greatly appreciated. http://mtpdev.com/dev/xls-reader/sample.xls Hi All, I am trying to export a table into excel and it is working, kind of.. I'm trying to import data that has commas in it. Unfortunately, it looks like this "PHP to CVS" process is comma delimited. How can I get around that, while having the data in the correct columns? Here is my code: Code: [Select] $file = 'Notes_Export'; $csv_output = ''; $sql = "SHOW COLUMNS FROM prop_notes WHERE Field IN ('note', 'created_by', 'created_on', 'updated_by', 'updated_on')"; $result = mysqli_query($connect, $sql); $i = 0; if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { $csv_output .= $row['Field'].", "; $i++; } } $csv_output .= "\n"; $sql = "SELECT note, created_by, created_on, updated_by, updated_on FROM prop_notes"; $values = mysqli_query($connect, $sql); while($rowr = mysqli_fetch_row($values)) { for ($j=0;$j<$i;$j++) { $csv_output .= $rowr[$j].", "; } $csv_output .= "\n"; } $filename = $file."_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); // header( "Content-disposition: filename=".$filename.".csv"); header("Content-disposition: attachment; filename=".$filename.".csv"); print $csv_output; exit; Hi I have a database set up using MySQL and want to be able to query this and then export to Excel using PHP. I've had a look around on the web but a lot of what I'm reading is going over my head. I don't have PEAR so can't use the ready made stuff. Basically, I want to be able to query the database, then export the arrays to an excel file and a pop up to appear asking the user to open / save / cancel (standard pop-up) Anyone know of any good places to start looking for the information for this? Sorry, I'm new to all this so any help / pointers would be greatly appreciated. Cheers I need a way of parsing Excel files.. both xls and xlsx in what seems to be various formats.. the concept of the way the expected sheets are to come in is with 2 tabs first tab row one column one will be a single value.. second tab will be any number of values only 2 columns though. I had found one parser class that I could use but it was very limiting and for some reason didn't work when I saved a fresh file... whats the best class, or way of handling an excel file? is there anything out there freely avaliable to work with thats kept upto date? or that works? Hi I have a script which references a DB and then produces an XML output saved as XLS to then open in Excel. This works an absolute dream in my dev environment using IE7 and in live using Firefox. However my entire audience uses IE7 and this is where it breaks in the live environment. For some reason, I cannot get the download to work. I get a windows pop-up with an error on it - see attached. If you need to see code, let me know which bit of code could possibly cause this, i.e. headers etc. I've not posted code as there's loads of it and I can't understand why it works in dev in IE 7 but not in live, and works in Firefox in live. Hi Attached file I get sent a excel file as attached each day updating products and stock levels and prices.. I am trying to find away to dump that file into my database without having to restructure the excel file. It needs to be in the data base in such away that I can create a popout menu with the headings you see in the file i.e CPUs :: Intel CPUs :: AMD etc etc to filter the procucts in my shopping cart.. I have 3 tables atm, maincat, subcat and products.. But it takes way to long to try and manually sort the changes. Any Ideas on how I can do it by just dumping the excel file? ok, so I am not good with arrays, they confuse me. how can I set this up so that $data is all results that are returned from the database that correspond to it? Code: [Select] $data = array( array("firstname" => '$firstname', "lastname" => '$lastname', "age" => '$age')); the original code looked like: Code: [Select] <?PHP $data = array( array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25), array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18), array("firstname" => "James", "lastname" => "Brown", "age" => 31), array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7), array("firstname" => "Michael", "lastname" => "Davis", "age" => 43), array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24), array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27) ); ?> but that won't do it for me. I need it so that the results from the database will automatically be filled in. basically, everyone that is in the database, so when a new member is added, they will show up too. please help Hi, I am attempting to export some table data to an excel spreadsheet. Below is the code I am working with. <?php include('includes/admin_session.php'); require_once("includes/connection.php"); function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } // file name for download $filename = "student_list_" . date('Ymd') . ".xls"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: application/vnd.ms-excel"); $flag = false; $result = mysqli_query($connection,"SELECT * FROM students ORDER BY tutor") or die(mysqli_error($connection)); while(false !== ($row = mysqli_fetch_assoc($result))) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\n"; $flag = true; } array_walk($row, 'cleanData'); echo implode("\t", array_values($row)) . "\n"; } ?> It is exporting the rows as requested and I am able to open the file in excel....however...when downloading it becomes stuck on a loop and continues to download over 20mb of nothing. As a result I am forced to stop the download. When I open my excel file, below my table data is the following error, over and over and over and over! Quote <br /> <b>Warning</b>: array_walk() [<a href='function.array-walk'>function.array-walk</a>]: The argument should be an array in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>33</b><br /> <br /> <b>Warning</b>: array_values() [<a href='function.array-values'>function.array-values</a>]: The argument should be an array in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>34</b><br /> <br /> <b>Warning</b>: implode() [<a href='function.implode'>function.implode</a>]: Invalid arguments passed in <b>/home/inschool/public_html/student_list_export.php</b> on line <b>34</b><br /> Any help on this would be awesome I have an excel file with many rows and columns but depending on the user I need it to show only one row. I am a php newbie. Is it possible to export a single value from a mysql DB into a pre-existing excel spread sheet? I know how to create a spread sheet from php and mysql, but not a single value into a pre-existing excel document cell. here's basically what I'm doing: Creating a financial expense tracking report in php, user will input each expense, it will keep a running tally of the available funds in user's bank account. The user has an excel spread sheet that requires the current value of available funds and updates a budget forecast according. SO, I need to somehow get the available balance of the bank account (generated by user input) into the pre-existing excel spreadsheet. Any thoughts? I'm totally at a loss. Thanks! Hi I have a page displaying orders from a mysql table. How can i add a button which exports this to an excel file? Hi. I have a large amount of data in an excel spread sheet. I have isolated 3 rows containing a date (yyyy-mm-dd plus two decimal values). I saved this as a tab delimited text instead of a comma separated csv. What I want to do is read each line of a text file and split it up into 3 and construct an INSERT INTO sql. the problem is this is not separated by comas for a split function. Can I split on a tab instead? I have used commas in the past only. TIA Desmond.
Hi people. Need some help if anyone could give a hand! I'm currently trying to incorporate an export to Excel feature on search returns on my web page. Here is how my searches are returned, so what I want now is when the user clicks on the 'Export to Excel' button it downloads the search results and opens it in the same format in Excel. I've tried using http://phpexcel.codeplex.com/ but can't understand how to use it properly, does anyone have any suggestions? I don't have any code written for the Export to Excel yet as it's just a submit button. How I see it in my head is the user clicks the button, it goes to a new page and performs a new query then downloads that but I don't no if thats right or not. |
|||||||