?
Solved

csv to open/save box into excel (csv)

Posted on 2005-05-02
21
Medium Priority
?
1,968 Views
Last Modified: 2013-12-25
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;
0
Comment
Question by:precision
  • 10
  • 9
  • 2
21 Comments
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13911263
> "UNABLE TO OPEN THE FILE" APPEARS.
where does this appear?
0
 

Author Comment

by:precision
ID: 13911370
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.
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13911624
print while <fileREAD>;
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.

 

Author Comment

by:precision
ID: 13911740
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.
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13911928
> 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
0
 

Author Comment

by:precision
ID: 13912455
Not sure where to get the http header?
0
 

Author Comment

by:precision
ID: 13912510
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.
0
 
LVL 48

Expert Comment

by:Tintin
ID: 13913918
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;

0
 

Author Comment

by:precision
ID: 13914188
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.
0
 
LVL 48

Assisted Solution

by:Tintin
Tintin earned 700 total points
ID: 13914649
I tried the following script and it opened in Excel just fine.

#!/usr/bin/perl
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $csvfile = '/tmp/a.csv';

open CSV, $csvfile or die "Could not open $csvfile $!\n";

my $cgi = new CGI;
print $cgi->header(-type => 'application/vnd.ms-excel');
print <CSV>;
close CSV;
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13915565
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);
0
 

Author Comment

by:precision
ID: 13916505
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.

0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13916578
> No such class size at download_excelC.cgi line 11, near "my size"
damn, typo, should be

my $size = -s $csvfile;
0
 

Author Comment

by:precision
ID: 13916695
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?
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13916774
> 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;
0
 

Author Comment

by:precision
ID: 13917152
Ok, I am getting frustrated!  Not you guys fault at all, I just cannot seem to get it right!

I use this exactly, as above:

#!/usr/bin/perl
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser); #<== disable when tests finished!
my $csvfile = 'test_file.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;

This is my database, "test_file.csv":

7773860386|awilda|nobles|555@1234.com|555-666-7777|Mon Apr 25 13:06:39 2005
7773860386|karen|gabriele|555@1234.com|555-666-7777|Mon Apr 25 16:23:18 2005
7773860386|gail|sernio|555@1234.com|555-666-7777|Mon Apr 25 17:56:30 2005
7773860386|laurie|goldstein|555@1234.com|555-666-7777|Mon Apr 25 20:58:32 2005
7773860386|Joan|Fedschun|555@1234.com|555-666-7777|Tue Apr 26 04:49:06 2005
7773860386|RINA ROSE|BATTISTA|555@1234.com|555-666-7777|Tue Apr 26 10:09:18 2005
7773860386|MARY ANN|PIZZI|555@1234.com|555-666-7777|Tue Apr 26 11:51:19 2005
7773860386|Melva|McMillan|555@1234.com|555-666-7777|Wed Apr 27 06:23:08 2005
7773860386|Donna|Barker|555@1234.com|555-666-7777|Wed Apr 27 11:55:56 2005

When I run in browser, "cleared temps, cookies", this is what I get IN COLUMN A:

,7,7,7,3,8,6,0,3,8,6,|,a,w,i,l,d,a,|,n,o,b,l,e,s,|,5,5,5,@,1,2,3,4,.,c,o,m,|,5,5,5,-,6,6,6,-,7,7,7,7,|,M,o,n, ,A,p,r, ,2,5, ,1,3,:,0,6,:,3,9, ,2,0,0,5,
ETC ETC ETC....

AHHHHHHHHHHHHHHHHHHH!!!

0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13917291
outsch, need to escape |, please replace substition with:

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

sorry
0
 

Author Comment

by:precision
ID: 13917356
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?
0
 
LVL 51

Accepted Solution

by:
ahoffmann earned 1300 total points
ID: 13920531
> ..  but I still get all this in COLUMN A ..
this is a problem with your application (excel or whatever) not the delivered data
try to change , to \t
0
 

Author Comment

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

Thanks again guys!
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 13921254
>  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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question