Description

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.

Discussion

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