how to output excel to textfile?

[poolboi]

hi guys,
i've got the code below that extract cell value from an excel sheet
but any idea how i continue to output it to a text file?
i'm not too sure how to do it, thanks

[CODE=perl]
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Con st 'Microsoft Excel';
$Win32::OLE::Wa rn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObjec t('Excel.Applic ation')
|| Win32::OLE->new('Excel.App lication', 'Quit'); # get already active Excel
# application or open new
my $Book = $Excel->Workbooks->Open("C:\\DOCU MENTS\\test.xls "); # open Excel file
my $Sheet = $Book->Worksheets(1 ); # select worksheet number 1
$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
my $array = $Sheet->Range("A4:B$la st_row")->{'Value'}; # get the contents
$Book->Close;

[/CODE]

[manimarank]

It has returned two dimensional array i.e an array with references to arrays.

so the value to be get from the array reference
[CODE=perl]
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Con st 'Microsoft Excel';
$Win32::OLE::Wa rn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObjec t('Excel.Applic ation') || Win32::OLE->new('Excel.App lication', 'Quit');
# get already active Excel
my $Book = $Excel->Workbooks->Open("E:\\pe rl-scripts\\thescr ipts\\test.xls" ); # open Excel file
my $Sheet = $Book->Worksheets(1 ); # select worksheet number 1
my $last_row = $Sheet->UsedRange->Find({What => "*", SearchDirection =>xlPrevious, SearchOrder=>xl ByRows})->{Row};
my $array = $Sheet->Range("A4:B$la st_row")->{'Value'}; # get the contents
$Book->Close;

foreach my $cell_ref (@$array) {
foreach my $value (@$cell_ref) {
print "$value\t";
}
print "\n";
}
[/CODE]

-Manimaran

[poolboi]

hm..i think there's a problem with the code
u can pass it into an array and print out the values,
but it's not printing to a text file
any suggestion how i can print the values to a text file?

[numberwhun]

hm..i think there's a problem with the code
u can pass it into an array and print out the values,
but it's not printing to a text file
any suggestion how i can print the values to a text file?

If you want to output to a file, then you have to first open a file and get a file handle assigned, then use the file handle to point the print statement to the file for output.

Here is an example:

[code=perl]
# open a file for writing to and assign a file handle
open(FILE, ">file.txt" );


# Print out to the newly opened file
print FILE ("Some text\n");
[/code]

Regards,

Jeff

[poolboi]

hey guys thanks for the help got it printed out already but i need to do a few more things, tried a lot ways still can't get it

my excel file for example has data in its respective cell like:

1 4
2 5
3 6

when i print it to a text file it becomes:

1
4
2
5
3
6

any idea if i can change the print out format in text file to:

1 4
2 5
3 6

i tried putting 123 in one array and 456 in another array but could not work it out
below's my most recent code

[CODE=perl]



use Win32::OLE;
use Win32::OLE::Con st 'Microsoft Excel';

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObjec t('Excel.Applic ation')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.App lication', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}

####deducing if $save_file_name exist or not######


$book = $ex->Workbooks->Open("C:\\Docu ments and Settings\\clong \\Desktop\\Conv ert to Text file\\Quarantin ed_IMSIHLR2_200 70611_Completed 1.xls");
$sheet = $book->Worksheets(1 );

$data = $sheet -> Range("A4:B2873 ") -> {Value};


open(FILE, ">file.txt" );
foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){

print FILE ("$value \n");
}
}
[/CODE]

[nithinpes]

Change this block of code:

open(FILE, ">file.txt");
	foreach my $cell_ref (@$data) {
  		foreach my $value (@$cell_ref){ 	
  	print FILE ("$value    \n");
}
}

to:

open(FILE, ">file.txt");
	foreach my $cell_ref (@$data) {
  		foreach my $value (@$cell_ref){ 	
  	print FILE ("$value \t");
}
print FILE "\n";
}

[poolboi]

thanks for the codes

ok tried soemthing too..

i wanted another variable added to the text file:

1 4 DELETED
2 5 DELETED
3 6 DELETED

i amended my code to :
[CODE=text]


open(FILE, ">file.txt" );
foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){

print FILE ("$value \t","DELETED" );


}

print FILE "\n";
}
[/CODE]

and it became :

1 DELETED4 DELETED
2 DELETED5 DELETED
3 DELETED 6 DELETED

any idea where i went wrong?

[poolboi]

opps
ok found it out

just replaced it as below

[CODE=text]

open(FILE, ">file.txt" );
foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){

print FILE ("$value");
print FILE" \t";


}
print FILE ("DELETED");
print FILE "\n";
}

[/CODE]

[hutch75]

Hate to be redundant or insignificant in my response, yet here is my take.

Based on my limited understanding and personal roadblocks.

1) Reading/Looping Through an XLS file to extract data with perl is a $bear = killer koala

2) My path of least resistance to overcome said $bear, has been to require the XLS file to be converted (via save as) to a CSV format.

O.K -- I too am almost sick at the lame a$$ed effort to be cute with $variables

couldn't resist...

knowing enoughing to be dangerous and not much more

Regards,

hutch

:-)

[poolboi]

hi guys, i made some modification to my script above but quite puzzled again

well this time i got 2 sets of array
but i need to print them out in the same manner again like from excel it has:

1 4 DELETED
2 5 DELETED
3 6 DELETED

right the problem is the number are defined as cells from A1 to B3 as one array
and the deleted are taken from another cell say T1 to T3 in another array
so i got print out 2 arrays of info into text file in the manner:

1 4 DELETED
2 5 DELETED
3 6 DELETED

below is my code which i think is wrong when i tried to attempt to print the 2nd array out

[CODE=perl]
use Win32::OLE;
use Win32::OLE::Con st 'Microsoft Excel';

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObjec t('Excel.Applic ation')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.App lication', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}


$book = $ex->Workbooks->Open("C:\\Docu ments and Settings\\clong \\Desktop\\Conv ert to Text file\\@xlsFiles ");
$sheet = $book->Worksheets(1 );


$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
$last_row = $last_row-14;
$total_run = $sheet -> Range("Z$last_r ow") -> {Value};
$last_row = $last_row-1;
$data = $sheet -> Range("A4:B$las t_row") -> {Value};
$last_row = $last_row+4;
$deleted = $sheet -> Range("Z$last_r ow") -> {Value};
$last_row = $last_row-4;
$output = $sheet -> Range("T4:T$las t_row") -> {Value};


open(FILE, ">file.txt" );
foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){

print FILE ("$value \t");

}
foreach my $cell_ref (@$output) {
foreach my $value (@$cell_ref){
print FILE ("$value");}
}
print FILE ("\n");

}
print FILE ("TOTAL RUN: $total_run \n");
print FILE ("TOTAL DELETED: $deleted\n");
print FILE ("ROWS SELECTED: $total_run\n");
print FILE ("$last_row" );

[/CODE]

[poolboi]

alright found out something...if i change the code as shown
i just need to know how to put $output into an array so it prints out but it must be the same format as i've describe as above, thanks
any suggestion?

[CODE=perl]


##start of conversion of excel to text file


use Win32::OLE;
use Win32::OLE::Con st 'Microsoft Excel';

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObjec t('Excel.Applic ation')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.App lication', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}


$book = $ex->Workbooks->Open("C:\\Docu ments and Settings\\clong \\Desktop\\Conv ert to Text file\\@xlsFiles ");
$sheet = $book->Worksheets(1 );


$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
$last_row = $last_row-14;
$total_run = $sheet -> Range("Z$last_r ow") -> {Value};
$last_row = $last_row-1;
$data = $sheet -> Range("A4:B$las t_row") -> {Value};
$output = $sheet -> Range("T4:T$las t_row") -> {Value};
$last_row = $last_row+14;
$deleted = $sheet -> Range("Z$last_r ow") -> {Value};



open(FILE, ">file.txt" );

foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){


print FILE ("$value \t");
}
print FILE ("$output");
print FILE ("\n");

}




print FILE ("TOTAL RUN: $total_run \n");
print FILE ("TOTAL DELETED: $deleted\n");
print FILE ("ROWS SELECTED: $total_run\n");
[/CODE]

[poolboi]

hm...any one has suggestions how i can print the 2nd array out properly?
i tried using push @array, value
and i got things like

1 4 deleted deleted deleted
2 5 deleted deleted deleted
3 6 deleted deleted deleted

but i just need one deleted

1 4 deleted
2 5 deleted
3 6 deleted

i tried accomplishing this using a counter below:

[CODE=perl]

[poolboi]

hm...any one has suggestions how i can print the 2nd array out properly?
i tried using push @array, value
and i got things like

1 4 deleted deleted deleted
2 5 deleted deleted deleted
3 6 deleted deleted deleted

but i just need one deleted

1 4 deleted
2 5 deleted
3 6 deleted

i tried accomplishing this using a counter below:
[CODE=perl]
$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
$last_row = $last_row-14;
$total_run = $sheet -> Range("Z$last_r ow") -> {Value};
$last_row = $last_row-1;
$data = $sheet -> Range("A4:B$las t_row") -> {Value};
$last_row = $last_row+14;
$deleted = $sheet -> Range("Z$last_r ow") -> {Value};



open(FILE, ">file.txt" );

foreach my $cell_ref (@$data) {
foreach my $value (@$cell_ref){


print FILE ("$value \t");
}
$output = $sheet -> Range("T$row") -> {Value};
print FILE ("$output");
print FILE ("\n");
$row++;
}
[/CODE]

but the algorithm is a little slow..so i'm thinking of using arrays
but how can i get the 2nd array printed out correctly
pls help..thanks

[poolboi]

i hope u all can see this thread...seems like it's not posted under new
well take yr time
thanks for the help in advance

[poolboi]

ok i figured out found it somewhere
basically it's to print arrays into columns

so say @array1, @array2, @array3 into column
the below code works,
but can someone explain to me the principle behind this code how it works?

[CODE=perl]

while(@array1 || @array2 || @array3) {
printf FILE ("%-10s %-10s %-10s\n",
shift(@array1) || ' ',
shift(@array2) || ' ',
shift(@array3) || ' ',
);
} [/CODE]