Hi,I would avoid Excel if possible. It bites. It has built-in functions for doing this sort of thing - attaching an external data source to a worksheet and handling updates, but it basically doesn't work with MySQL, so don't waste your time looking into it.
I would appreciate help on the following. I would like to create a small application that would involve Excel XP on
Windows 2000, and
perl and mysql on RedHat linux 9.0
The front end of the application will be Excel since it has a lot
of useful functions. Columns from the Excel sheet will be used to
update the mysql database. Data from the mysql database will also
be used to update the Excel sheet.
I am trying to map things out and see if all of the parts in my application will fit together prior to doing
too much developing.
Basically my main concern now is to create the flow of information. There is the Excel workbook part and then there is the UNIX mysql database part.
Writing a shell or perl script in the linux environment that will ftp files from
an external source that will be used to update the mysql database. They will be flat files. I will use perl to parse the data. Then use perl DBI to insert to information
Also using perl DBI to upload information from the Excel sheet (user's personal annotation) Into the database.
The part that I'm not too sure about is how to send the information from the Excel sheet to the database And how to grab the information from the database and update the Excel sheet. On top of that I am using two different operating systems.
Are there some tools in VBA or in perl that I could use to build this bridge?
Is this a practical solution?
Thanks for your help,
Having said that, yes you can do what you want to do.
In the few cases where I've been forced to do this, I've taken one of the following approaches ( depending on the needs of the problem ):
Option 1) Use Excel's CSV exported to create a comma-delimited file, and import it into your DB via the mysql command-line client ( ie mysql < excel_dump.csv ... you'll have to do a little more work, like select the right database and table, but this is the basic idea ). If you have to trigger an import from Excel, you can use ADO ( see below ) to send a 'load data infile' command to MySQL, which is basically the same thing. Of course MySQL will have to be able to 'see' the file. Do you have Samba working?
Option 2) Use VB & ADO to walk through the data and do the import one line at a time ( slow but sometimes necessary). Example:
' select the top cell ( start of data ) A1.select
' set up ADO objects and open connection to MySQL...
myconn = new adodb.connection
with myconn
.connectionstring = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql_ip_address;DATABASE=db;UID=user;PASSWORD=password"
.open
end with
set mycommand = new adodb.command with mycommand .activeconnection = myconn .commandtype = adcmdtext end with
do while not activecell.value = ""
if {some condition } then
with mycommand
.commandtext = "insert into mytable { fields in table } values { values from spreadsheet }"
.execute
end with
else
' whatever ... maybe do an 'update' statement instead of the above 'insert' statement?
endif
activecell.offset (1,0).select
loop
myconn.close
I'm putting together a web site that will have examples like this ( only more involved, and ones that are actually tested ... above code is not tested ... ). I'll add this type of question to the site when I finish my stuff on Access & MySQL.
Option 3) I've heard of people using Perl from inside Excel. Check back through the list archives - it wan't long ago. It may give you what you need if you want to use Perl for this.
-- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
