NB: These pages were mostly written in 2001 or so. The résumé dates are accurate but the code is aged and unlike whiskey, 8 year-old code doesn't usually taste better. For a look at my current skills and to see my CPAN modules, sample code, and code discussions, please see these pages instead: Perl resources and sample code and PangyreSoft.
Converting delimited text to Excel
Social links
View Ashley Pond V's profile on LinkedIn

Other pages


Non-technical people need to be able to work with data. They usually end up reaching for Excel or Access because we live in a malevolent Universe.

Fortunately for the Perl kids there are a couple excellent modules already done for you by our friends John McNamara (Spreadsheet::WriteExcel) and Kawai Takanori (Spreadsheet::ParseExcel). Here is an example of how you can turn Excel into delimited plain text: converting Excel to text.

Below is a very useful and fairly generic subroutine that can take all kinds of delimited files and turn them into straightforward Excel files.



Anyone who’s dealt with delimited files before knows that this approach is missing a way to balance delimiters. Eg: If your field delimiter is a tab and your record delimiter is a newline and one of the text fields has a tab or a return character in it, it will wreck the results.

To work with this, I often use the NULL character (”\0”) as a field delimiter and a double (”\0\0”) as a record delimiter. It will never appear in regular files so you don’t have to resort to Text::Balanced or something to ensure your data integrity.

If you will ever have empty fields that cause the field delimiter to double up, you’ll have to get crafty and do something like “\0”.‘_RS_’.”\0” for the record separator.

$xls_data = text_to_excel( file      => '/path/to/file.txt',
                           delimiter => "\0",
                           recordsep => "\0\0",
                           name      => 'NULL delimited file' );
Search these pages via Google
Text, original code, fonts, and graphics ©1990-2009 Ashley Pond V.