PHP People

the_Grinch

So I am beginning to look into web programming and figured I'd stay open source. Thus, once I finish going through html (truly knowing it) I am going to begin with PHP. I was hoping some PHP whiz would come threw and answer this question for me. Every morning I have a report I have to make for work. I import a text file into Excel (using a delimited character of |) and then separate it into different sheets. Basically the info is orders that weren't shipped for various reason yesterday and each sheet is a reason. So I wondered if I put the info into a MySQL database, if I would be able to write a PHP script that would automatically sort it for me and then I could export it as a CSV (or perhaps an Excel file)? Thus I come in, import all the info into Excel, save as a CSV, place the CSV in MySQL, run script, and report is ready.

Thanks in advance!

Sepiraph

You can although you don't necessarily need to do it with php, you would basically need to first get all the info into your database first using some sort of language (e.g. perl) and the corresponding module for the database API. The sorting would be done from the sql query itself, so in your case your query would get the date info and search for whether the order is shipped or not.

You would want to:

1) install a DB and the DB API for that language (e.g. perl)
2) Write a script that input from whatever source you are feeding it and split out result in a format that the DB will recognize (easier to split out some text/csv file first for troubleshooting)
3) Insert data into the database using that script
4) modify your sql query to give you your desired result.

Pash

never really looked at doing this before, but this might work:-

PHP-ExcelReader | Get PHP-ExcelReader at SourceForge.net

This dude has a nice blog post about it as well:-

xml.lt: Blog: Reading Excel files with PHP

dynamik

The database step is optional. You could just have PHP read the text file and output a sorted CSV from that. I wouldn't messed with the DB stuff unless you wanted to archive things.

dmsoft

Try DBConvert for Excel & MySQL
You can transfer your data in both directions

samsungaca

First post. I've been reading these forums for a long time, but never really had anything to say. Btw grinch I've read a lot of your threads and I'll be glad if I'm able to help you out.

I do a lot of my work with php, but I'm not much of a guru. You would probably be able to do what you want with a few explode functions and some file reading functions.


<?php
$closed=array();
$pending=array();

//Select file
$texfile=("file.txt");


//Read file line by line
forech($textfile as $textline){

//Remove the delimiter and place into an array
$temp=explode("|",$textline);

//Sort each line and add to array called closed or whatever you want to do with it.
if($temp[4]=="closed"){
$closed[]==$temp;
}
if($temp[4]=="pending"){
$pending[]==$temp;
|
}

//Output contents of closed array
var_****($closed);
exit(0);
?>

With something like this you'll have two arrays, one for closed entries and another for pending entries. Depending if you are running php off of a webserver or through command line, you can output the data to a text file or to a webpage. If this helps, or need me to elaborate on something more let me know.

Alif_Sadida_Ekin

I design ETL jobs at work that perform functions very closely related to what you are doing. I use this open-source tool: Browse Pentaho - Business Intelligence Files on SourceForge.net

It's called Pentaho Data Integration (homepage: Pentaho Commercial Open Source Business Intelligence Kettle Project ). You can design a transformation that does exactly what you want it to do , ie. read info from an input file, transform the data, and output however you want. Best of all, you can schedule your jobs to run whenever you want as well. So, if you need that report generated every morning, just schedule the job to run before you get to the office and presto! It took a little while to get used to the system so just let me know if you have any questions.