asked on

non-comma delimted csv or xls file from perl cgi script

Hello,

I am gathering input from an online form, which is put into an array and sent via email to me via a perl cgi script. That all works fine.

I would also like the info dumped into a spreadsheet but am running into problems where some people answer with a comma within a field, thus messing up the alignment of the csv file columns.

I am a beginner at perl and cgi, and would like to change the output to be delimited by another character, e.g. a pipe |. Just swapping the commas out in the script doesn't work, so can someone please help and tell me what would?

It would also be grand if the file written was an excel file (.xls) rather than csv, but that is not of great importance.

Below is what I have.

Thanks in advance for any help!
Joan

$csv = join(',',
        qq/$in{'lastname'}/, qq/$in{'firstname'}/,
        qq/$in{'title'}/, 
        qq/$in{'email'}/, qq/$in{'officephone'}/, qq/$in{'cellphone'}/,
        qq/$in{'photolink'}/,
        qq/$in{'attending'}/,
        qq/$in{'advisor'}/,
        qq/$in{'project'}/,
        qq/$in{'breakfast'}/, qq/$in{'lunch'}/, qq/$in{'dinner'}/,
        qq/$in{'bus'}/, qq/$in{'passenger'}/, qq/$in{'ridingwith'}/,
        qq/$in{'mealpref'}/, qq/$in{'specialmeal'}/,
        qq/$in{'under21'}/,
        qq/$in{'comments'}/);

#
#  Produce a record
#
open(CSV, ">>/registrations/SVD10-cmu.csv");
print CSV "$csv\n";
close(CSV);

Open in new window

evilrix

The correct way to handle CSV where the field has a comma in it is to encase it in double quotes.

eg.

qwqwe, rwerer, "erwe,werwe", eweee

http://en.wikipedia.org/wiki/Comma-separated_values#Basic_Rules

evilrix

There is a Perl module that might help you.

Text::CSV - comma-separated values manipulator (using XS or PurePerl)
http://search.cpan.org/~makamaka/Text-CSV-1.17/lib/Text/CSV.pm

Text::CSV provides facilities for the composition and decomposition of comma-separated values using Text::CSV_XS or its pure Perl version.

An instance of the Text::CSV class can combine fields into a CSV string and parse a CSV string into fields.

The module accepts either strings or files as input and can utilize any user-specified characters as delimiters, separators, and escapes so it is perhaps better called ASV (anything separated values) rather than just CSV.

lee555J5

If you want to change the delimiter, I personally prefer tab-delimited. It works very well with Excel and Access and is very common with other apps as well. Technically, you can encounter a [Tab] in user input. However, it much less common than commas and you can still enclose in double quotes as @evilrix suggested.
Lee

ASKER CERTIFIED SOLUTION

Frederick McIntyre

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frederick McIntyre

I missed a single quote in the last line of the code. Should be

Frederick McIntyre

argh... didn't get the correct last line.

qq/$in{'comments'}/).'"';

Open in new window

jdigney

ASKER

Thanks fredmc and all others who offered solutions. I really appreciate your help!