Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

export data from multiple files in a folder to an excel file

by veeru612 (Initiate)
on Nov 29, 2012 at 16:51 UTC ( #1006300=perlquestion: print w/replies, xml ) Need Help??
veeru612 has asked for the wisdom of the Perl Monks concerning the following question:

Hello oh great monks of perl, I am very very new to perl - started today. I have multiple files in a folder and these files have the same format and the data is arranged in exact same way in all of them. I need to extract 2 columns of each file and put them all in one spreadsheet preceeded by the file name. this is what I have written (copied from one of the threads on this site) to extract data and put in an excel,, then grab first two columns from all the files in the folder and put them in one giant spreadsheet. thank you so much for the help,,
#!/usr/bin/perl -w use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new('test.xls'); my $worksheet = $workbook->add_worksheet(); open INPUT, 'data.SSM' or die "Couldn't open file: $!"; $worksheet->write($.-1, 0, [split]) while <INPUT>;
I wrote another but its not working, am pretty sure it's not the right code/is full of errors.
#!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; use Spreadsheet::WriteExcel; my %count; my $directory = "c:\\perl test scripts"; my $workbook = Spreadsheet::WriteExcel->new('test.xls'); my $worksheet = $workbook->add_worksheet(); opendir(DIR, $directory ) || die "Unable to open directory - $!\n"; my @files = grep /\.txt/, readdir( DIR ); closedir( DIR ); foreach my $file (@files) { open( FH, "$directory\\$file" ) || die "Unable to open $file - $!\n"; while( <FH> ) { $worksheet->write($.-1, 0, [split]) while <FH>; } close( FH ); }

Replies are listed 'Best First'.
Re: export data from multiple files in a folder to an excel file
by space_monk (Chaplain) on Nov 29, 2012 at 17:42 UTC
    Please edit your question to use the <code> tags to format the code better.
    A Monk aims to give answers to those who have none, and to learn from those who know more.
Re: export data from multiple files in a folder to an excel file
by Anonymous Monk on Nov 29, 2012 at 18:43 UTC

    What is the error you are getting? The second code is working for me. Except that you don't need Spreadsheet::ParseExcel. Because, you are not Parsing any excel files.
    Also change this : <#!/usr/bin/perl -w> to this: #!/usr/bin/perl -w. In your first code.

      Hello Sir, Please forget the first code, but in the 2nd code it says it cannot parse the formula and nothing gets written in the excel file, :(

        Which Formula? I can't see any in your second code. Except, there is a part of the code you are not showing.

Re: export data from multiple files in a folder to an excel file
by rpnoble419 (Pilgrim) on Nov 30, 2012 at 22:18 UTC
    It looks like you are using the default var $_ to hold the data to be written to the excel file. Try storing the data to be written to the excel file in its own variable. You are relying on the excel module to read the default var and it may not be correctly.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1006300]
Approved by herveus
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2018-06-23 01:32 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (125 votes). Check out past polls.