Click to Email Performant Systems

CSV to XML conversion utility

 

 

While my corporate clients already get output from their Multivalue Database systems in PCL Print, PDF emails and system FAX media, I am often asked to provide output in a spreadsheet file. I had been using the XLS output mode built into AccuTerm, which works fine, but I wanted more formatting control. Finding an Open Source PHP module that does CSV file to XLS file output with a high degree of formatting, I built a shell around that to use as a production conversion tool, to accomplish high format XLS file creation from an ASCII CSV file, for corporate user's consumption. As a PHP program, it should work on any O/S that you can install PHP 4.3 or better onto.

 

 

CSVtoXLS.php Program Notes

A PHP utility to convert CSV data into business class XLS spreadsheet files, tested as compatible with OpenOffice 2.0 Calc and Excel 2000. It is based on the PHP Spreadsheet_Excel_Writer module by Mika Tuupola & Xavier Noguer, which provides for a high level of spreadsheet formatting and also offers formulas. CSVtoXLS.php takes advantage of those features and provides meaningful formatting and a simple Column SUM automatically generated at the bottom of any Decimal Column.

Download the package at http://performantsystems.com/CSVtoXLS.zip

The distribution CSVtoXLS.zip file contains:
CSVtoXLS.php
example.csv
/OLE and /Spreadsheet directories
README.txt

Note: the /OLE and /Spreadsheet directories need to be copied to the php include_path directory. Sourced from http://pear.php.net/package/Spreadsheet_Excel_Writer/


Command line syntax is: php -f CSVtoXLS.php {InputFile}.csv {OutputFile}.xls

If CSVtoXLS.php is not in the current directory, provide the full path to it.

In order to get more formatting that just a data dump, three lines of META data are inserted into the top of the CSV data file. This allows the developer to define a Title, Column Headings and Column Formats in Spreadsheet terms.

The first line of CSV data is expected to be the Title only (in Quotes).
The second line of CSV data is expected to be comma delimited Column Formats (in Quotes).
The third line of CSV data is expected to be comma delimited Column Headings (in Quotes).
Live data is expected to start on CSV file line 4. (Quotes on Text Fields or All Fields).

Dates are expected in either the ISO date format 'YYYY-MM-DD', or 'DD/MM/YYYY'.

Decimal numbers expected with just the decimal included, no other formatting. ("8.2","1995.23")

Line 2 Column formatting should be associated 1 to 1 with the Column Headings on Line 3.
Choices for Column Formatting are:
"TextLeft","TextRight","Date","Decimal0","Decimal1","Decimal2","Decimal3","Decimal4"

Install steps:
1) Find your PHP include_path; ex: php -i|grep include_path
2) Copy the /OLE and /Spreadsheet directories to the PHP include_path
3) Copy the CSVtoXLS.php and example.csv files somewhere ($HOME is fine).

Test it
1) Navigate (cd) to where the CSVtoXLS.php and example.csv files are.
2) At a command line, do: php -f CSVtoXLS.php example.csv example.xls
3) Open example.xls in a Spreadsheet App,, compare it to the example.csv text content.

Here is what the results of this example.xls file looks like:

 

 

Compare this spreadsheet with the example.csv file, noticing the TextLeft, TextRight, Decimal1, Date and Decimal2 formats. The SUM formulas on row 25 are automaticaly built for any Decimal Column format.