Excel to CVS

[EM-RGL]

Looking for some help !!!

 

Background:

We receive various files from clients and have tried over the years to get our clients to standardize their file formats to a format that we can use and automate the processing of the data.  Unfortunately there are always the few that do not want to spend the time or have the people with the knowledge to format their files to match our required format.  Hence we end up spending a tremendous amount of time reformating the files or manually keying the data into our system to use the information and this opens the door for human errors by our employees.

 

I have been given the task to try to automate as much of this process as possible to eliminate the need to manually key information or adjust the files we receive.

 

Solution:

Develop a web based system in PHP that would allow a client to upload a file that we could read and populate a form.  The form would allow the client to review the information and key in any information that is missing from their file and then submit it to us for processing.  This would eliminate our need to manually key in data or adjust the files and any errors in the information would be the result of the client and not our employees.

 

The file formats we receive regularly are Tab Delimited files, Excel files, and XML files.

 

Question:

Tab Delimited files are easy enough to deal with and XML can be easily parsed and used with PHP.  The trouble I have is with Microsoft Excel.

 

Is there a way to convert the xls file to a CSV with PHP ?

 

I have done some searching on the forum and on the net but I have not found anything that would fit my requirements.

 

Any help, comments etc. would be greatly appreciated.

 

Thanks

 

 

[cmgmyr]

Read my post here: http://www.phpfreaks.com/forums/index.php/topic,133807.msg563168.html#msg563168 That should get you going in the right direction

[per1os]

I am just throwing this out there, not sure if there is any truth or not. If you are running your PHP Server on Windows and you have Microsoft Excel installed you should be able to run a command line utility to convert xls to csv.

 

http://www.ozgrid.com/Services/convert-excel-csv.htm

 

For a utility. If you goto www.php.net/exec you will find some winexec functions to allow you to run batch files or command line information on files. What you would then need to do is have the user upload the file to the server, you get the path and run the exec command and convert it and viola it is in comma delimited format.

 

If you want this for linux, maybe look into using an openoffice.org command line utility to do the conversion.

[EM-RGL]

Thanks cmgmyr and frost for your replies I appreciate it.

 

The website will be hosted internally and will be on a Linux box so I will try the code you posted cmgmyr and I will check out OpenOffice as well.

 

Thank you both.

[EM-RGL]

cmgmyr,

 

I used the code you posted when I followed the link to try it out and it does read the file but I am also getting a bunch or other unwanted characters.

 

Any ideas as to how to eliminate the extra characters ?

[cmgmyr]

can you post your code?

[EM-RGL]

Sure...

 

I basically used what you had and changed the file name and removed the sql code for now.  The xls file had 1 row with 4 fields in 4 columns.

 

Thanks for your help.

 

<?php
// tab delimited file 
$file = "Info.xls"; 

// open file 
$handle = fopen($file, "r"); 

$x = 0;
echo "<table border=1>";
// loop through results with fgetcsv() function 
while(($data = fgetcsv($handle, 1000, "\t")) !== FALSE) { 
     
    // populate field vars just to make it easier to work with ..  
    // you could access the $data[] array directly in the sql if you want 
    $field1 = $data[0]; 
    $field2 = $data[1]; 
    $field3 = $data[2]; 
    $field4 = $data[3]; 

	if($x >0) echo "<tr><td>$field1</td></tr><tr><td height='10'></td></tr><tr><td>$field2</td></tr><tr><td height='10'></td></tr><tr><td>$field3</td></tr><tr><td height='10'></td></tr><tr><td>$field4</td></tr>";

	$x++;
} 
echo "</table>";
// close file 
fclose($handle); 
?> 

[cmgmyr]

Can you also post the results you got and also attach your info.xls file? Thanks

[EM-RGL]

The attached Zip file contains both the result and the xls file.

 

Thanks for the help.

 

[attachment deleted by admin]

[EM-RGL]

Just curious if anyone else has any ideas ?

[cmgmyr]

I'm getting the same thing with your file. It looks like its reading all of the header information on the excel sheet. Try saving it as a csv file and running your script again. You will have to change \t to ,

[EM-RGL]

Yeah I can easily do a save as in Excel to a csv file but I am going to build the upload page so that a client can upload an xml, csv or an excel file and then it will parse the file and populate textboxes on a form. I have the csv and xml portion working already, I just need to figure out this excel issue.

 

I want to give as many options as possible and our clients are not the most knowledgable people when it come to computers.  They know how to use Excel and do what they need to but not much beyond that. Trying to explain and get them to do a save as to a csv will only create issues and most of our clients that we have tried to help to this don't want to spend the time and would rather send it to us to deal with.  I know it is only a small thing but to the client it is a major issue.

 

I appreciate your help cmgmyr.

 

I did find a perl script called xls2csv but I don't really know perl so I will have to play around with it and see what I can do with it. I found one website that has used it and it appears to do what I need it to do so I guess I will have to try it out and play with it and maybe learn some perl.

 

Thanks again cmgmyr.

 

And if anyone else has any ideas please let me know.

 

Thanks