Re: [PLUG] Text file to Excel, automated

[JP Vossen <jp@jpsdomain.org>]

 > Date: Thu, 3 Jul 2008 06:50:08 -0700 (PDT)
 > From: Julien Mills <julienfmills@yahoo.com>
 >
 > I'm looking for a way to put the text output of some
 > sql queries into an excel document which I can email
 > to others in the company.

+1 for CSV
+1 for the Perl modules Kyle mentioned

"CSV" will be easier, since odds are that you can either output that 
directly or trivially convert to it.  You didn't mention the SQL tool 
you are using.  IIRC MySQL and PgSQL both offer native output of CSV. 
If not, it's trivial to convert TAB delimited to CSV using awk or Perl. 
    bash Cookbook recipe 13.18 "Converting a Date File to CSV":

$ awk 'BEGIN { FS="\t"; OFS="\",\"" } { gsub(/"/, "\"\""); $1 = $1; 
printf "\"%s\"\n", $0}' tab_delimited

$ perl -naF'\t' -e 'chomp @F; s/"/""/g for @F; print q(").join(q(","), 
@F).qq("\n);' tab_delimited

Oracle, OTOH, is a bloody nightmare to script at the CLI level.  I think 
SQL*Plus in 10i+ has an HTML output format, which might be reversible, 
and newer Oracle might do better, though I doubt it.  The older 9i I'm 
stuck with totally and completely sucks!  I finally wrote a Perl script 
to run queries and give me CSV or tab output.  Ask and ye shall receive 
though some of the code is a bit ugly, I keep meaning to clean it up a bit.


*Parsing* CSV into your program (which is out-of-scope for your question 
but may be of general interest) is non-trivial, because "CSV" is not 
really well defined.  It sounds simple, but it isn't.  There are tools 
and libs out there, and a really neat if hairy regexp in MRE2 (Mastering 
Regular Expressions).


Good luck,
JP
----------------------------|:::======|-------------------------------
JP Vossen, CISSP            |:::======|        jp{at}jpsdomain{dot}org
My Account, My Opinions     |=========|      http://www.jpsdomain.org/
----------------------------|=========|-------------------------------
"Microsoft Tax" = the additional hardware & yearly fees for the add-on
software required to protect Windows from its own poorly designed and
implemented self, while the overhead incidentally flattens Moore's Law.
___________________________________________________________________________
Philadelphia Linux Users Group         --        http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion  --   http://lists.phillylinux.org/mailman/listinfo/plug