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

jdigneyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
fredmcConnect With a Mentor Commented:
To get your output in Excel format, use the cpan module Spreadsheet::WriteExcel

To use a tab character as a delimiter, change
$csv = join(',',

to
$csv = join('"\t",

Note that \t must be in double quotes.

To use comma-delimited, wrapped in double quotes, change it to
$csv = join('","',

but you will then need to add a double quote at the beginning and end of $csv


$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'}/)."';

Open in new window

0
 
evilrixSenior Software Engineer (Avast)Commented:
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
0
 
evilrixSenior Software Engineer (Avast)Commented:
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.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
lee555J5Commented:
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
0
 
fredmcCommented:
I missed a single quote in the last line of the code. Should be
0
 
fredmcCommented:
argh... didn't get the correct last line.

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

Open in new window

0
 
jdigneyAuthor Commented:
Thanks fredmc and all others who offered solutions. I really appreciate your help!

0
All Courses

From novice to tech pro — start learning today.