Convert Excel file to CSV

Topics: User Forum
Jun 17, 2009 at 6:32 PM

Hello,

I have users who need to upload .csv files to a web app I've developed. They inevitable are not saving their Excel files correctly to .csv. In order to improve their experience I wanted to allow them to simply upload their .xls files and do the conversion myself with PHPExcel. Can someone point me in the right direction to get this accomplished? This seems like a very useful tool, but it is somewhat intimidating getting started with it. Any help is greatly appreciated!

Developer
Jun 17, 2009 at 6:40 PM

$reader = PHPExcel_IOFactory::createReader('Excel5');
$reader->setReadDataOnly(true);

$excel = $reader->load('read.xls');

$writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
$writer->save('write.csv');

Jun 17, 2009 at 6:49 PM

Wow! Thanks for the quick reply!! Saved me a lot of time digging. I'm going to try this get back to you. Thanks again!!

Jun 20, 2009 at 8:10 AM

Just some pointers that might help you along the way:

You might want to check the file extension to save the time of users who do upload it correctly.

You could use the excel itself and not bother converting it to CSV as the excel format will be much more reliable - your script might break if any of the columns had a comma in it for instance.

Just my $0.02.

Jun 22, 2009 at 4:54 PM

Hey - thanks for the tips!

I am running into a problem, though. It seems that PHPExcel thinks there is data in far more columns than those that actually contain data. For example, I only have data through column D, but this:

$objPHPExcel->getActiveSheet()->getHighestColumn(); 

Is returning column IE - which is way more than I have!

Is this a bug, or am I doing something wrong?

Jun 22, 2009 at 6:01 PM

My files are being read just fine - one thing I noticed though, even if a cell has formatting information (and no data), php-excel will still read that as having data (and rightly so!) - set $reader->setReadDataOnly(true); and then echo

$objPHPExcel->getActiveSheet()->getHighestColumn(); 

to see if any cells have formatting information you're missing (if this echo returns 4, then you know there's no data beyond D).

If you already have $reader->setReadDataOnly(true); set then it certainly means that there's data in column IE - one quick
and easy way to find out is to go to cell IE1 (first cell in the column) and pressing Ctrl + down arrow. This will take
you to the first cell with data in it. If it goes to 65536, that means there's no data in that column - not even white
spaces.

Sorry about the long post - wanted to make sure it was clear!
Developer
Jun 23, 2009 at 11:02 PM

>> Is returning column IE - which is way more than I have!

>> Is this a bug, or am I doing something wrong?

Is it returning IE or IV ? I am noticing this behavior for some other sample xls files that I have.

karpar is correct that setReadDataOnly(true) may be the solution for now.

The problem has to do with the way column information is stored in xls files. It is a bit tricky. Will see if I can do something about it and post back here.

 

Developer
Jun 27, 2009 at 3:31 AM

Problem with too many columns may have been fixed with this:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10204