asked on

csv to open/save box into excel (csv)

SEE BELOW CODE, IT DOUBLEs THE QUESTION OF "FILE DOWNLOAD" AND IF I HIT OPEN, IT GOES INTO EXCEL BUT "UNABLE TO OPEN THE FILE" APPEARS. I WANT TO HAVE A ".CSV" FILE AND ALLOW SOMEONE TO DOWNLOAT. IS THERE A BETTER WAY?

#!/usr/bin/perl
use CGI;
my $cgi = new CGI;

my $filename = "2-20-9-48-22.csv";

print $cgi->header(
-type => 'application/vnd.ms-excel',
-content_disposition => qq(attachment; filename="$filename"),
-content_length => length($content),
-expires => 'now',
);

binmode STDOUT;

open (fileREAD, $filename) or die("Could not open it!");

print <fileREAD>;
close fileREAD;
exit;

ahoffmann

> "UNABLE TO OPEN THE FILE" APPEARS.
where does this appear?

precision

ASKER

It opens when excel opens, I checked the csv file, seems fine. If I open off local machine file opens right into excel. I also get the "double" box of "File Download", not sure why either.

ahoffmann

print while <fileREAD>;

precision

ASKER

ok, I replaced "print <fileREAD>;" with "print while <fileREAD>;"

I get the same response. "File Download" box opens with typical choice of "open" "save" "cancel". When I choose "open" it immediately asks me again, after I click open the second time it then goes into excel and a box pops open which says "Unable to read file", I click "OK", nothing appears.

CSV file is fine. I open on local machine and it open right up in excel with any issues. Running XP.

Thanks is advance.

ahoffmann

> box opens with typical choice of "open" "save" "cancel".
then you browser is not configured for that mime-type
Can you check and post the HTTP header send by the server

precision

ASKER

Not sure where to get the http header?

precision

ASKER

Maybe start over? I have a delimited file saved as csv, I just want someone to be able to click on a link and have the file ask "open" or "save", if open, then it goes into excel, if save, then it goes to ask where to save to on client computer.

Tintin

In the header, you have:

-content_length => length($content),

But you haven't defined $content anywhere.

You should always run your scripts with:

use strict;

precision

ASKER

ok, I see that. What do I use for $content? (I am not sure) Where do I call out "use strict;"?

I have a database that I want clients to be able to download their file as an option besides just viewing online. I have a delimited file saved as CSV that I want them to be able to click on that will automatically ask for "open" or "save", if open is chosen it goes right into excel. I see that system being used on many data companies.

Thanks in advance.

SOLUTION

Tintin

membership

This solution is only available to members.

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

Start Free Trial

ahoffmann

Tintin, wher is content_length now?

I'd replace first print with:

my size = -s $csvfile;
print $cgi->header(-type => 'application/vnd.ms-excel',-content_length=>$size);

precision

ASKER

Great, very very close!

Tintin: Opens great but I have 6 columns (csv delimited pipe "|") and all 6 columns arrive in excel column A, pipe included. Seems it's excel in IE browser, correct? Can it open in the actual program microsoft excel or is that the way it is?

ahoffmann: When I insert your code I get the following error:
Software error:
No such class size at download_excelC.cgi line 11, near "my size"
syntax error at download_excelC.cgi line 11, near "my size ="
Global symbol "$size" requires explicit package name at download_excelC.cgi line 12.
Execution of download_excelC.cgi aborted due to compilation errors.

ahoffmann

> No such class size at download_excelC.cgi line 11, near "my size"
damn, typo, should be

my $size = -s $csvfile;

precision

ASKER

ahoffmann: ok, it works, what did it do? I did not see a visual difference from not using that code piece.

group: Opens great but I have 6 columns (csv delimited pipe "|") and all 6 columns arrive in excel column A, pipe included. Seems it's excel in IE browser, correct? Can it open in the actual program microsoft excel or is that the way it is?

ahoffmann

> what did it do?
it write the length of the data being send from server in the HTTP header

> Opens great but I have 6 columns (csv delimited pipe "|") ..
that's a problem with your data.
Don't know what M$ excel expects to writ columns as you want it (it's magic to me most the time),
but if "|" is your delimiter, and you can enshure that it is not used otherwise, then you might simply change it to a comma or tab if that is not used too.

#!/usr/bin/perl
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser); #<== disable when tests finished!
my $csvfile = '/tmp/a.csv';
my $size = -s $csvfile;
open CSV, $csvfile or die "Could not open $csvfile $!\n";
undef $/;
my $csv = <CSV>;
$csv=~s/|/,/g; # replace , by \t for tab
my $cgi = new CGI;
print $cgi->header(-type => 'application/vnd.ms-excel',-content_length=>$size);
print $csv;
close CSV;

precision

ASKER

ahoffmann

outsch, need to escape |, please replace substition with:

$csv=~s/\|/,/g;

sorry

precision

ASKER

ok great, but I still get all this in COLUMN A, 9 rows, but all data in 1 column:

7773860386,awilda,nobles,555@1234.com,555-666-7777,Mon Apr 25 13:06:39 2005

I am not sure why it does not spread to column F. If I open file directly in excel, it opens up correctly, columns A-F, 9 rows.

Any ideas? Should I open new question to give more points due to continued help?

ASKER CERTIFIED SOLUTION

ahoffmann

membership

This solution is only available to members.

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

Start Free Trial

precision

ASKER

Wonderful! That did it, the missing link! What is meaning of ~s and the back slash and forward slash?

Thanks again guys!

ahoffmann

> What is meaning of ~s ..
=~ pattern match (change left hand value in scalar context)

> ..and the back slash and forward slash?
s/old/new/ substitute old by new, / is the delimiter
\| escapes the character | which has a special meaning in regular expressions otherwise

man perlre
man perlrequick
man perlretut