Program a solution

[Admiral Frosty]

OK, here the skinny.

I have a huge database of invoices in exel files. Now I need to extract data from certen fields (all in the same place), and place them on a large master sheet. Now what are some ideas on how I can best automate this, and what programing language whould be best ect. ect.

Now I only have some experiance in javascript, but I wouldn't mind learning!

Thanks for the time

[bwkaz]

Originally posted by Admiral Frosty
I have a huge database of invoices

OK, that's not a bad idea.

in exel files.

That, on the other hand, is HORRIBLE.

GET THEM OUT OF EXCEL!

Excel is NOT a database, and was never meant to be used as a database. You're using the wrong tool for this job.

Besides that, there's nothing in Linux that can reliably read all XLS files -- the closest you can come is probably OpenOffice or kcalc or gnumeric, but if you made this with a recent version of Excel, then you're probably going to be out of luck for a few years (until the secret format gets reverse engineered to the point where reading it is reliable).

You want this information in a database, first. Probably a MySQL database, since that will run natively on Linux (i.e., if you bring it into Access, you're making another huge mistake).

Once you have that, there are MySQL / PostgreSQL / most-other-SQL bindings for a lot of scripting languages (and of course, there are C bindings if you need it in a C program). OpenOffice has a couple of database bindings too, I think, if all you need is a pretty looking frontend.

[lagitus]

If you must use Excel you could look into its visual basic capabilities. But Excel should really not be used for a "huge database"!

[Admiral Frosty]

Well, MySQL is it? Allrighty than!

http://www.weberdev.com/get_example.php3/1270

Found this over at weberdev, its about reading exel files from php (pearl, if am not mistaken). See what you think.

Now let me confess. I really dont know were to start with all of this. I Was looking around and I found this:

http://www.amazon.com/exec/obidos/tg...Fencoding=UTF8

Sorta' kills two birds (three, depending how you count) with one stone! I've a few read Mr. Harrises other books, and think that perfect (for me, the begainer). 'Think I'll buy it today!

PS: Heck, I didnt put the stuff in exel in the first place! But guess who gets to clean it up...

[JuiceWVU202]

navicat http://www.navicat.com/

[bwkaz]

<nitpick>

Originally posted by Admiral Frosty
its about reading exel files from php (pearl, if am not mistaken).

First, it's not Pearl, it's Perl. Second, no, PHP is not Perl. PHP is a hypertext preprocessor (it's meant to be embedded in HTML documents and parsed on the server side). Perl can be used to generate HTML documents (CGI-style), but I've never seen it embedded in HTML like PHP is.

</nitpick>

Third, that solution will never work on Linux... there is no ODBC driver for Excel on Linux. I don't think there are actually any ODBC drivers for Linux (though I could be wrong).

Learning PHP and MySQL would definitely be a good thing to do, though. It won't help you get the data out of Excel, but it will help you after you stuff it into a portable database engine (MySQL).

[dchidelf]

Originally posted by bwkaz
Third, that solution will never work on Linux... there is no ODBC driver for Excel on Linux. I don't think there are actually any ODBC drivers for Linux (though I could be wrong).

Perl has a database interface called DBI. It's not ODBC, but it also has a driver for reading Excel files as if they are databases.

http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm

I have never used it at all, so I don't know how well it works.

It uses WriteExcel and ParseExcel to read and write the Excel databases.
WriteExcel (except for the most recent version) only creates Excel 5 files.

I have used the parser it is built from (ParseExcel) and have had a fair amount of luck with it. If data is all you are after ParseExcel works great. I believe I have even been able to read Excel files from Office 2000. ParseExcel sometimes fails to get the correct formatting information, and can not read the formulas from cells. It only reads the calculated values.

http://search.cpan.org/~kwitknr/Spre.../ParseExcel.pm

The main difficulty with ParseExcel is that it parses the file into a datastructure and then leaves you to figure out which elements of the datastructure you want. The online documentation should be enough to guide you through it if you decide to go that route.