473,382 Members | 1,529 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

how to output excel to textfile?

170 100+
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

Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. use Win32::OLE qw(in with);
  3. use Win32::OLE::Const 'Microsoft Excel';
  4. $Win32::OLE::Warn = 3;                                # die on errors...
  5. my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
  6.     || Win32::OLE->new('Excel.Application', 'Quit');  # get already active Excel
  7.                                                       # application or open new
  8. my $Book = $Excel->Workbooks->Open("C:\\DOCUMENTS\\test.xls"); # open Excel file
  9. my $Sheet = $Book->Worksheets(1);                     # select worksheet number 1
  10. $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
  11. my $array = $Sheet->Range("A4:B$last_row")->{'Value'};        # get the contents
  12. $Book->Close;
  13.  
  14.  
Mar 13 '08 #1
16 4603
It has returned two dimensional array i.e an array with references to arrays.

so the value to be get from the array reference
Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. use Win32::OLE qw(in with);
  3. use Win32::OLE::Const 'Microsoft Excel';
  4. $Win32::OLE::Warn = 3;                                # die on errors...
  5. my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); 
  6.  # get already active Excel
  7. my $Book = $Excel->Workbooks->Open("E:\\perl-scripts\\thescripts\\test.xls"); # open Excel file
  8. my $Sheet = $Book->Worksheets(1);                     # select worksheet number 1
  9. my $last_row = $Sheet->UsedRange->Find({What => "*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};
  10. my $array = $Sheet->Range("A4:B$last_row")->{'Value'};        # get the contents
  11. $Book->Close;
  12.  
  13. foreach my $cell_ref (@$array) {
  14.   foreach my $value (@$cell_ref) {
  15.     print "$value\t";
  16.   }
  17.   print "\n";
  18. }
  19.  
-Manimaran
Mar 13 '08 #2
poolboi
170 100+
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?
Mar 14 '08 #3
numberwhun
3,509 Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. # open a file for writing to and assign a file handle
  2. open(FILE, ">file.txt");
  3.  
  4.  
  5. # Print out to the newly opened file
  6. print FILE ("Some text\n");
  7.  
Regards,

Jeff
Mar 14 '08 #4
poolboi
170 100+
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4.     use Win32::OLE;
  5.     use Win32::OLE::Const 'Microsoft Excel';    
  6.  
  7.         # use existing instance if Excel is already running
  8.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  9.         die "Excel not installed" if $@;
  10.         unless (defined $ex) {
  11.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  12.                     or die "Oops, cannot start Excel";
  13.         }
  14.  
  15.     ####deducing if $save_file_name exist or not######    
  16.  
  17.  
  18.     $book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\Convert to Text file\\Quarantined_IMSIHLR2_20070611_Completed1.xls");
  19.     $sheet = $book->Worksheets(1);
  20.  
  21.     $data = $sheet -> Range("A4:B2873") -> {Value};
  22.  
  23.  
  24.     open(FILE, ">file.txt");
  25.     foreach my $cell_ref (@$data) {
  26.           foreach my $value (@$cell_ref){     
  27.  
  28.     print FILE ("$value    \n");
  29. }
  30. }    
  31.  
Mar 14 '08 #5
nithinpes
410 Expert 256MB
Change this block of code:

Expand|Select|Wrap|Line Numbers
  1. open(FILE, ">file.txt");
  2.     foreach my $cell_ref (@$data) {
  3.           foreach my $value (@$cell_ref){     
  4.       print FILE ("$value    \n");
  5. }
  6. }    
  7.  
to:
Expand|Select|Wrap|Line Numbers
  1. open(FILE, ">file.txt");
  2.     foreach my $cell_ref (@$data) {
  3.           foreach my $value (@$cell_ref){     
  4.       print FILE ("$value \t");
  5. }
  6. print FILE "\n";
  7. }    
  8.  
Mar 14 '08 #6
poolboi
170 100+
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 :
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.     open(FILE, ">file.txt");
  4.     foreach my $cell_ref (@$data) {
  5.           foreach my $value (@$cell_ref){     
  6.  
  7.         print FILE ("$value \t","DELETED");
  8.  
  9.  
  10.         }
  11.  
  12.     print FILE "\n";
  13. }    
  14.  
and it became :

1 DELETED4 DELETED
2 DELETED5 DELETED
3 DELETED 6 DELETED

any idea where i went wrong?
Mar 14 '08 #7
poolboi
170 100+
opps
ok found it out

just replaced it as below

Expand|Select|Wrap|Line Numbers
  1.  
  2.     open(FILE, ">file.txt");
  3.     foreach my $cell_ref (@$data) {
  4.           foreach my $value (@$cell_ref){     
  5.  
  6.         print FILE ("$value");
  7.         print FILE"    \t";
  8.  
  9.  
  10.         }
  11.     print FILE ("DELETED");
  12.     print FILE "\n";
  13. }    
  14.  
  15.  
Mar 14 '08 #8
hutch75
12
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

:-)
Mar 14 '08 #9
poolboi
170 100+
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

Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE;
  2.     use Win32::OLE::Const 'Microsoft Excel';    
  3.  
  4.         # use existing instance if Excel is already running
  5.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  6.         die "Excel not installed" if $@;
  7.         unless (defined $ex) {
  8.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  9.                     or die "Oops, cannot start Excel";
  10.         }
  11.  
  12.  
  13.     $book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\Convert to Text file\\@xlsFiles");
  14.     $sheet = $book->Worksheets(1);
  15.  
  16.  
  17.     $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
  18.     $last_row = $last_row-14;    
  19.     $total_run = $sheet -> Range("Z$last_row") -> {Value};
  20.     $last_row = $last_row-1;
  21.     $data = $sheet -> Range("A4:B$last_row") -> {Value};
  22.     $last_row = $last_row+4;
  23.     $deleted = $sheet -> Range("Z$last_row") -> {Value};
  24.     $last_row = $last_row-4;
  25.     $output = $sheet -> Range("T4:T$last_row") -> {Value};
  26.  
  27.  
  28.     open(FILE, ">file.txt");
  29.     foreach my $cell_ref (@$data) {
  30.           foreach my $value (@$cell_ref){     
  31.  
  32.         print FILE ("$value \t");
  33.  
  34.         }
  35.         foreach my $cell_ref (@$output)    {
  36.                 foreach my $value (@$cell_ref){ 
  37.                     print FILE ("$value");}
  38.                         }
  39.         print FILE ("\n");
  40.  
  41. }        
  42.         print FILE ("TOTAL RUN: $total_run    \n");
  43.         print FILE ("TOTAL DELETED: $deleted\n");
  44.         print FILE ("ROWS SELECTED: $total_run\n");
  45.         print FILE ("$last_row");
  46.  
  47.  
Mar 26 '08 #10
poolboi
170 100+
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. ##start of conversion of excel to text file
  4.  
  5.  
  6.     use Win32::OLE;
  7.     use Win32::OLE::Const 'Microsoft Excel';    
  8.  
  9.         # use existing instance if Excel is already running
  10.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  11.         die "Excel not installed" if $@;
  12.         unless (defined $ex) {
  13.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  14.                     or die "Oops, cannot start Excel";
  15.         }
  16.  
  17.  
  18.     $book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\Convert to Text file\\@xlsFiles");
  19.     $sheet = $book->Worksheets(1);
  20.  
  21.  
  22.     $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
  23.     $last_row = $last_row-14;    
  24.     $total_run = $sheet -> Range("Z$last_row") -> {Value};
  25.     $last_row = $last_row-1;
  26.     $data = $sheet -> Range("A4:B$last_row") -> {Value};
  27.     $output = $sheet -> Range("T4:T$last_row") -> {Value};
  28.     $last_row = $last_row+14;
  29.     $deleted = $sheet -> Range("Z$last_row") -> {Value};
  30.  
  31.  
  32.  
  33.     open(FILE, ">file.txt");
  34.  
  35.     foreach my $cell_ref (@$data) {
  36.           foreach my $value (@$cell_ref){     
  37.  
  38.  
  39.         print FILE ("$value \t");
  40.         }
  41.             print FILE ("$output");
  42.             print FILE ("\n");
  43.  
  44. }    
  45.  
  46.  
  47.  
  48.  
  49.         print FILE ("TOTAL RUN: $total_run    \n");
  50.         print FILE ("TOTAL DELETED: $deleted\n");
  51.         print FILE ("ROWS SELECTED: $total_run\n");
  52.  
Mar 26 '08 #11
poolboi
170 100+
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]
Mar 28 '08 #12
poolboi
170 100+
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:
Expand|Select|Wrap|Line Numbers
  1.     $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
  2.     $last_row = $last_row-14;    
  3.     $total_run = $sheet -> Range("Z$last_row") -> {Value};
  4.     $last_row = $last_row-1;
  5.     $data = $sheet -> Range("A4:B$last_row") -> {Value};
  6.     $last_row = $last_row+14;
  7.     $deleted = $sheet -> Range("Z$last_row") -> {Value};
  8.  
  9.  
  10.  
  11.     open(FILE, ">file.txt");
  12.  
  13.     foreach my $cell_ref (@$data) {
  14.           foreach my $value (@$cell_ref){     
  15.  
  16.  
  17.         print FILE ("$value \t");
  18.         }
  19.             $output = $sheet -> Range("T$row") -> {Value};
  20.             print FILE ("$output");
  21.             print FILE ("\n");
  22.             $row++;    
  23. }    
  24.  
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
Mar 28 '08 #13
poolboi
170 100+
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
Mar 28 '08 #14
poolboi
170 100+
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2.             while(@array1 || @array2 || @array3) {
  3.                 printf FILE ("%-10s        %-10s        %-10s\n",
  4.                     shift(@array1) || '    ',
  5.                 shift(@array2) || '    ',
  6.                 shift(@array3) || '    ',
  7.     );
  8. }    
Mar 28 '08 #15
nithinpes
410 Expert 256MB
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2.             while(@array1 || @array2 || @array3) {
  3.                 printf FILE ("%-10s        %-10s        %-10s\n",
  4.                     shift(@array1) || '    ',
  5.                 shift(@array2) || '    ',
  6.                 shift(@array3) || '    ',
  7.     );
  8. }    

The logic of this is code is like this:
- The code block in while loop as long as any of the three arrays exist i.e. if the arrays are of unequal size, the loop iterates until the last element in the largest array is processed.
- The printf() statement will format the output. In this case, a ten characters' space is left between the first characters of consecutive words.
- The three parameters to be printed are the elements of the 3 arrays.
- shift() statement keeps on removing the first element from the array. If any of the arrays is emptied, the shift() fails and the space character after OR(||) will be printed.
Mar 28 '08 #16
poolboi
170 100+
i see..ok
thanks for the enlightenment
cheers, :)
Mar 31 '08 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Ep | last post by:
I need to put some text (and numbers) in Word and Excel. How do I put that into them? Also how can I use the "hot keys" to navigate in each. Thanks Ep
0
by: microsoft.news.com | last post by:
I'm creating an Excel spreadsheet template that I want to populate from a text file. I need to put certain data items in certain cells in the excel spreadsheet. I need to populate it from code. ...
1
by: Mamatha | last post by:
Hi I want to export data from textfile to Excel sheet in VB.NET application.When i copy the text file into Excel sheet the data of text file are copied to only one cell of the Sheet. Now i...
0
by: Mamatha | last post by:
Hi When i clicked a button, i want to insert data from listview in VB.NET to Excel sheet. If you know the solution either above or below is ok for me. I know how to insert from a textfile,but...
3
by: Avi | last post by:
I need to create a text file that has the data from the 10 tables in the database. The number of fields in the tables exceeds 255 and so I cannot make a new table with all the fields and then...
9
by: Justme | last post by:
Novice programmer needs help with using fgets to read and ignore the first two lines of a file. I've gone thru the previous posting regarding fgets, but none of them seems to help my situation. I...
1
by: kachokaratz | last post by:
well i have a problem in my project..since the contents of my textfile is too long, i was wondering how to output the textfile in such a way that you can divide the contents to be able to output...
1
by: maryanncanor | last post by:
Hi I would like to know if there is a macro that can export the data from a query to an output textfile that has NO QUOTES. If I run the concatenated query Expr1: & & & & & & & ...
1
by: asedt | last post by:
With my Excel macro and two text files I want to create a new textfile containing the first textfile then text from the sheet and then the second textfile. My problem is that i don't know how to...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.