If you're reading Excel 2007 files (.xlsx extension), consider using Spreadsheet::XLSX as a replacement for Spreadsheet::ParseExcel (mentioned in earlier posts).

We've had to do similar in the past, we did use ansi C mind you, but the 'method' may be of use to you.

We exported the xls files to csv with headers (ensuring all the headers were the same text/name, reguardless of order.) Then we made a 'hash' table of arrays for each column. We then processed each file in turn, adding each row's of data into the correct 'hash->array', at the end we then simply wrote out a new csv file with all the combined data. It worked well and was only an hour or so of coding and testing.

In C we used a structure and had to manage all our memory etc. Thankfully this is not necessary in Perl!

I think you can accomplish your goal by using a combination of two CPAN modules: Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. Use ParseExcel to get the data from the 20 excel files while using WriteExcel to add the data (organized as you like) to a new sheet using WriteExcel.

Since the columns are not in the same order on each sheet you'll need to get the value of A1, B1, .... G1 for the names of the columns. As long as the naming convention is the same in each sheet you should be able to keep all the data together by an IF statement.

I'm confused. What do you mean you need to store the data 'somewhere until all the files are parsed'? Does the amount of data exceed a RAM limitation or are you just inquiring how you would build the nested data structure?

Hi, dtm81, and welcome to the Monastery.

First of all, I'd like to point out that when you post a question here, and one of the earliest replies requests further elucidation, it is considered bad form not to respond. In your case, dcmertens asked you:

What do you mean you need to store the data 'somewhere until all the files are parsed'? Does the amount of data exceed a RAM limitation or are you just inquiring how you would build the nested data structure?

More than four hours later, you still haven't replied.

Of course, you might have been unavoidably detained elsewhere, eg by having your house burned down. In which case, no apologies are necessary. Otherwise, if you return to this thread, please do dcmertens the decency to reply to his post.

Thank you

Fortunatelly i am currently working as well, so maybe you should think that i am busy working to something else... And i am very greatfull that peoples are trying to help here, thank you all for the quick solutions that you bother gave me :) To respond to our colegue's question, i usually write a script that will be OK for 20 lines as well as for 2 billions of lines, so i was thinking i will use threads to split the file in. As a quick solution, indeed i will export the file into a csv format and just the task to be done quick, but i ll want to "beatify" the script as i mentioned earlier. I think the solutions are more than enough, so i ll stop here... Cheers, to all!

Had time this weekend, to "meditate" on it... The implementation was without external module, but with a "big" - hash of arrays, that i parsed it later on... It was in a way funny in the end, because i forgot to set the IFS to \r\n, reading from win files :) but finally thought of it... Any way i found a nice module: Spreadsheet::ExcelHashTable that is parsing the excel files straight into the hash... Thanks to all! Proud to be a part of this community.