Re: Spreadsheet::WriteExcel and ODBC
On Jun 8, 2006, at 7:19 AM, Joel Rees wrote: Up front caveat, I don't have a lot of experience with this kind of thing, but, ... On 2006.6.8, at 02:36 AM, Mike Schienle wrote: Celeste Suliin Burris wrote: I'm a bit confused as to why you need to use ODBC. I just connect to the remote MySQL server via the DBI when I'm using Perl. I have my Linux machine running MySQL 5.0. Hi Celeste - I'm working on a program that will update a database throughout the day and provide the customer with a spreadsheet of the results. I'd rather have the customer be able to open the spreadsheet any time and get the latest data rather than having to create a new spreadsheet each time the data updates. I should be able to accomplish that by putting the ODBC connections directly in the spreadsheet. Why? Would it make more sense, perchance, to use a web browser as your front-end instead of MSOffice? Hi Joel - There is already a web-based charting package being used (Visual Engineering's KavaChart), but I know/expect some of the customers will want to do some additional analysis of the results beyond what the charting on the web page will provide. I did a similar package with a different data set for a customer a few years ago and have a decent idea of what they did with it in Excel. Basically, I'm just trying to anticipate the customer's needs on this, as well as remove a potential load on the server. The KavaChart package can dynamically build the data using multiple URLs, so I don't have to build the results for it each time the data needs updating. I'm trying to figure out how to get Excel to do the same kind of thing. I have no problem doing the DBI connection from Perl, which is what I've been doing for quite some time. What I'm after is a way to do the connection from an Excel spreadsheet that has been written by WriteExcel. How often does this spreadsheet need to be (mechanically, I assume) rebuilt? (That's the only reason I can think of for building such a spreadsheet from a perl script.) Every 15 minutes if I rebuild it each time data gets processed. If I provide a spreadsheet they can open whenever they want and it grabs the latest data off the server, I suspect it would probably happen a couple times a day, or thereabouts. I can also just build the spreadsheet from the web interface on demand and have them download it. So, this isn't a hard requirement, more of a wish list item. The program is for multiple customers, so I want/need to be able to write a spreadsheet that is for the particular customer. I might be able to do this as an Excel template, also. That would be Plan B or C. Oh. That's another reason, I suppose. My guess is you're going to be flying by the seat of your pants on this project with no radio. And no compass and poor visibility :-) I think I'd try to sell the customers on pushing the interface to MSOffice back a ways, doing the tables in HTML on a local-access- only server, and only dumping the relatively static results to Excel at the stage where things go to the archive. (Nursing the customers off of MSOffice as an archive format is also something I'd recommend, but one thing at a time.) The current plan is to keep things on the server and provide a couple ways for them to view the results. The basic view would be with KavaChart. Excel would be used for some additional things like trend analysis and other statistical stuff. From: Mike Schienle <[EMAIL PROTECTED]> Organization: Custom Visuals, LLC Date: Wed, 07 Jun 2006 16:51:36 +0100 To: macosx@perl.org Subject: Spreadsheet::WriteExcel and ODBC Hi all - I need to write some Excel files with ODBC access to a MySQL database. I've used Spreadsheet::WriteExcel in the past for writing formulas, formatting, etc., with no problems, but the ODBC connection is new to me. I tried it manually (just opening a blank spreadsheet and connecting to a remote MySQL server) last night using Actual's ODBC client demo and that worked fine. The WriteExcel docs are a bit vague [to me] on connecting to an ODBC source, though. Has anyone done this via Spreadsheet::WriteExcel? Do you mind passing along a couple hints? Would this require any customer/client to have an ODBC client on their system for this to work? If so, I may just go with static data and have the customer pick up a new spreadsheet each time. Also, as mentioned above I'm using Actual's ODBC client. Is there a better client out there? Or an Open Source one that's competitive? Thanks. -- Mike Schienle -- Mike Schienle Mike Schienle
Re: file creator id, etc
On Fri, 9 Jun 2006, Joel Rees wrote: > Not a perl topic, but isn't there a Finder setting that determines > whether Get Info allows access to this or not? Not a Perl solution (or is Ruby close enough to count?), but RCDefaultApp may help with problems like this: http://www.rubicode.com/Software/RCDefaultApp/ -- Chris Devers
Re: file creator id, etc
I am trying to read a CSV data file of names and addresses into Now Contact. However the import feature does not see this file as it is ghosted. My conclusion is that it is looking at the file creator information. How do I see this information? Apple-i, Get Info, does not show this. Not a perl topic, but isn't there a Finder setting that determines whether Get Info allows access to this or not?
Re: file creator id, etc
At 7:35 pm +0200 8/6/06, Dominic Dunlop wrote:
On 2006–06–08, at 18:46, Joseph Alotta wrote:
Thanks Dominic and John. After playing around with it a little,
the type attribute must be "TEXT". I guess I can just do a call to
system() to set this. Thank you for your help.
Careful! If you use SetFile to do this, you'll end up with a script
that works only on systems that have devtools installed...
The file type can also be set like this:
my $f = "$ENV{HOME}/desktop/trash.csv";
open F, ">$f" or die $!;
print F "a,b,c,d,e";
`osascript -e '
tell app "finder" to set file type of posix file "$f" to "TEXT"
'`;
JD
Re: file creator id, etc
On 2006–06–08, at 18:46, Joseph Alotta wrote: Thanks Dominic and John. After playing around with it a little, the type attribute must be "TEXT". I guess I can just do a call to system() to set this. Thank you for your help. Careful! If you use SetFile to do this, you'll end up with a script that works only on systems that have devtools installed. Fine for your own use, but not for distribution. -- Dominic Dunlop
Re: file creator id, etc
Thanks Dominic and John. After playing around with it a little, the
type attribute must be "TEXT". I guess I can just do a call to system
() to set this. Thank you for your help.
Joe.
[PowerBook-G4:~/Desktop] jja% /Developer/Tools/GetFileInfo c.txt
file: "/Users/jja/Desktop/c.txt"
type: ""
creator: ""
attributes: avbstclinmedz
created: 06/01/2006 17:58:50
modified: 06/08/2006 11:42:04
[PowerBook-G4:~/Desktop] jja% /Developer/Tools/SetFile -t "TEXT" c.txt
[PowerBook-G4:~/Desktop] jja%
On Jun 8, 2006, at 11:05 AM, Dominic Dunlop wrote:
On 2006–06–08, at 17:20, Joseph Alotta wrote:
I am trying to read a CSV data file of names and addresses into
Now Contact. However the import feature does not see this file as
it is ghosted. My conclusion is that it is looking at the file
creator information.
How do I see this information? Apple-i, Get Info, does not show
this. How can I inspect these file attributes and how can I
modify them with perl?
Firstly, I'd try giving the files a ".txt" extension. That might
persuade Now Contact of their acceptability.
If that doesn't work, you need the to use the MacPerl module:
$ perl -MMacPerl -lwe 'print join " ", MacPerl::GetFileInfo
("Desktop/random.tif")'
GKON TIFF
The corresponding interface to set the signature is
MacPerl::FSpSetFInfo -- see the man page for details. (Although
MacPerl is part of the Tiger essentials package, you have to load
the optional BSD package to get the man page. But, as a Perl
developer, you'll have done that.)
Finally, if this is a one-off job, you can do things by hand with
the GetFileInfo and SetFile utilities from Apple's developer tools.
They live in /Developer/Tools. Again, see the man pages for details.
--
Dominic Dunlop
Re: file creator id, etc
At 10:20 am -0500 8/6/06, Joseph Alotta wrote:
Greetings,
I am trying to read a CSV data file of names and addresses into Now
Contact. However the import feature does not see this file as it is
ghosted. My conclusion is that it is looking at the file creator
information.
How do I see this information? Apple-i, Get Info, does not show
this. How can I inspect these file attributes and how can I modify
them with perl?
Without loading Mac::Carbon you can get (and set) type and creator
with osascript in the shell, but it might be quicker (if milliseconds
are important) just to print the data to an anonymous file and have
NC read that, if it can.
#!/usr/bin/perl
my $csv = "$ENV{HOME}/factory/accounts/2006 accounts/bos_060216.csv";
my $temp_csv = "/tmp/temp.csv";
open CSV, $csv or die $!;
open TEMP, ">$temp_csv" or die $!;
print TEMP ;
my $type_creator = `
osascript -e '
tell app "Finder" to get {file type, creator type} of (posix file "$csv")
'`;
print "type, creator : $type_creator\n__\n\n";
close TEMP;
open TEMP, "$temp_csv";
print ;
JD
Re: file creator id, etc
On 2006–06–08, at 17:20, Joseph Alotta wrote:
I am trying to read a CSV data file of names and addresses into Now
Contact. However the import feature does not see this file as it
is ghosted. My conclusion is that it is looking at the file
creator information.
How do I see this information? Apple-i, Get Info, does not show
this. How can I inspect these file attributes and how can I modify
them with perl?
Firstly, I'd try giving the files a ".txt" extension. That might
persuade Now Contact of their acceptability.
If that doesn't work, you need the to use the MacPerl module:
$ perl -MMacPerl -lwe 'print join " ", MacPerl::GetFileInfo("Desktop/
random.tif")'
GKON TIFF
The corresponding interface to set the signature is
MacPerl::FSpSetFInfo -- see the man page for details. (Although
MacPerl is part of the Tiger essentials package, you have to load the
optional BSD package to get the man page. But, as a Perl developer,
you'll have done that.)
Finally, if this is a one-off job, you can do things by hand with the
GetFileInfo and SetFile utilities from Apple's developer tools. They
live in /Developer/Tools. Again, see the man pages for details.
--
Dominic Dunlop
file creator id, etc
Greetings, I am trying to read a CSV data file of names and addresses into Now Contact. However the import feature does not see this file as it is ghosted. My conclusion is that it is looking at the file creator information. How do I see this information? Apple-i, Get Info, does not show this. How can I inspect these file attributes and how can I modify them with perl? Joe.
Re: Spreadsheet::WriteExcel and ODBC
Up front caveat, I don't have a lot of experience with this kind of thing, but, ... On 2006.6.8, at 02:36 AM, Mike Schienle wrote: Celeste Suliin Burris wrote: I'm a bit confused as to why you need to use ODBC. I just connect to the remote MySQL server via the DBI when I'm using Perl. I have my Linux machine running MySQL 5.0. Hi Celeste - I'm working on a program that will update a database throughout the day and provide the customer with a spreadsheet of the results. I'd rather have the customer be able to open the spreadsheet any time and get the latest data rather than having to create a new spreadsheet each time the data updates. I should be able to accomplish that by putting the ODBC connections directly in the spreadsheet. Why? Would it make more sense, perchance, to use a web browser as your front-end instead of MSOffice? I have no problem doing the DBI connection from Perl, which is what I've been doing for quite some time. What I'm after is a way to do the connection from an Excel spreadsheet that has been written by WriteExcel. How often does this spreadsheet need to be (mechanically, I assume) rebuilt? (That's the only reason I can think of for building such a spreadsheet from a perl script.) The program is for multiple customers, so I want/need to be able to write a spreadsheet that is for the particular customer. I might be able to do this as an Excel template, also. That would be Plan B or C. Oh. That's another reason, I suppose. My guess is you're going to be flying by the seat of your pants on this project with no radio. I think I'd try to sell the customers on pushing the interface to MSOffice back a ways, doing the tables in HTML on a local-access-only server, and only dumping the relatively static results to Excel at the stage where things go to the archive. (Nursing the customers off of MSOffice as an archive format is also something I'd recommend, but one thing at a time.) From: Mike Schienle <[EMAIL PROTECTED]> Organization: Custom Visuals, LLC Date: Wed, 07 Jun 2006 16:51:36 +0100 To: macosx@perl.org Subject: Spreadsheet::WriteExcel and ODBC Hi all - I need to write some Excel files with ODBC access to a MySQL database. I've used Spreadsheet::WriteExcel in the past for writing formulas, formatting, etc., with no problems, but the ODBC connection is new to me. I tried it manually (just opening a blank spreadsheet and connecting to a remote MySQL server) last night using Actual's ODBC client demo and that worked fine. The WriteExcel docs are a bit vague [to me] on connecting to an ODBC source, though. Has anyone done this via Spreadsheet::WriteExcel? Do you mind passing along a couple hints? Would this require any customer/client to have an ODBC client on their system for this to work? If so, I may just go with static data and have the customer pick up a new spreadsheet each time. Also, as mentioned above I'm using Actual's ODBC client. Is there a better client out there? Or an Open Source one that's competitive? Thanks. -- Mike Schienle -- Mike Schienle
