Jump to content

Excel to CVS


EM-RGL

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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); 
?> 

Link to comment
Share on other sites

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 ,

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.