Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Converting a txt file to excel file

by MonkPaul (Friar)
on Apr 02, 2005 at 14:29 UTC ( #444373=perlquestion: print w/replies, xml ) Need Help??

MonkPaul has asked for the wisdom of the Perl Monks concerning the following question:

Tahks for the reply,

What i intended to do was to get a file in zip format off thw web, unzip it, then get its contents in a table format so i can build up some graphs of my own.

Is there a way unzip the file in perl, then save the zipped file as .xls or whatever excel files are saved as. Im not interested in opening the file getting the data into a table format.

I tried to work around having the file open and writing to a spredsheet using Spreadsheet:WriteExcel but it seems a longa away around. Any ideas???

MonkPaul. Sorry about the change of node.. im not quite sure how to work this stuff yet. :(. With regards to the Unzipping of files.. The Archive::Zip::MemberRead method looks to be useful for searching through the file without actually unzipping the damn thing. Have i interpretated it correctly or am i way off.

Replies are listed 'Best First'.
Re: Simple HTML parser
by borisz (Canon) on Apr 02, 2005 at 14:37 UTC
      My favourite is HTML::TokeParser::Simple, where you get a token (tag, piece of text, comment...) at a time, like you read a text file one line at a time; and each token is an object, which eases unified access. For example, $token->as_is returns the original token's text, whatever kind of token it is.
      Cheers my good man. I'll let you know how it goes. MonkPaul
Re: Converting a txt file to excel file
by tlm (Prior) on Apr 02, 2005 at 16:23 UTC

    MonkPaul, judging by the other replies in this thread, I think you morphed a previous post of yours (asking about HTML parsers) into another. :-)

    The short answer to the new question is Spreadsheet::WriteExcel.

    the lowliest monk

Re: Converting a txt file to excel file
by jZed (Prior) on Apr 02, 2005 at 16:59 UTC
    To convert a tab-delimited text file into Excel, you simply open it in excel, you don't need no stinkin' perl.

    update the word stinkin' is a joke, no offence meant :-) ... cf. Treasure of the Sierra Madre

    update2 yes, of course perl can be brought into the mix, and modules like Spreadsheet::WriteExcel and DBD::Excel can be useful for that.

    MonkPaul PLEASE stop changing your node. This website is intended to be a permanent record to help others with questions. Never erase a node's content, rather use update as I have. Think about other people coming to the site - they want to be able to read a node and see the responses in a way that answers questions. If you start a new question, start a new node. If you modify your question, update it, don't erase it and start over.

      re jZed's, suspect either
      (1) there's some trick I don't know
      (2)"simply" may be an oversimplification.

      Excel has better luck (for me, anyway) with text files beginning at fixed column locations; takes mucho twiddling to get it to handle tabs correctly if text (of NON-uniform length) includes numerals unless data intended for each cell is double-quoted.

      and, <G>, isn't it "don't need no steenkin'...?"

      Sure, I assume the OP knows that and is asking in PM because he has a big raft of these files to convert and wants to do it programmatically. It's no fun to convert 500 tab-delimited files by opening them from within Excel, even if one uses the "lazy-evaluation" model.

      the lowliest monk

        The point i was trying to make was that the user shouldnt have to leave the web page environment, but should only have to choose which file they want to view the data of.

        The user doesnt want to see any of the actual raw data, but merely an overall graph to display the data on the screen in a graphical format that can easily be understood.

        ** not meaning to be arsey about it :)****

        I may be a bit stupid but can you tell me of a way that i can loop through all the column headings in the file (say writing them to the screen), i.e. stored in an array, then for each row do the same. Currently i have the following code, although i know it doesnt work. I was thinking of a hash table for the column and value but im new to this perl game. The problem i have is to recognise the number of columns there are and loop through each row upto and including the last column, then go to the next line and start again. My brain is wrecked at this stage. Im normally a java programmer and cant seem to see a way forward....HELP!.

        # read from a file and extract the contents into an Excel file sub write_to_Excel() { use Spreadsheet::WriteExcel; my @columnNames; my @rowValues; my $count = 0; my $row = 0; my $fileArray = "F:/User_Interface/dbStuff/ArrayExpress.txt"; open( INFILE, "<$fileArray") or die "Cannot open the file"; print("Searching File..."); foreach $line ( <INFILE> ) { if( $line =~ /^Name/ ) { @columnNames = split(/\t/, $line); } else { @rowValues = split(/\t/, $line); } } # Create a new workbook and save a file called example.xls # and add a worksheet called Summary to that. my $workbook = Spreadsheet::WriteExcel->new("ArrayExpress.xls"); my $worksheet = $workbook->add_worksheet(`Summary`); # Set text bold, red, underlined for values # designated as high # my $high_val = $workbook->add_format(); # $high_val->set_bold(); # $high_val->set_color(`red`); # $high_val->set_underline(); ###### not sure how to loop through array to put rows across then do +wn and same with cols $worksheet->write($row,$count,$element); foreach $element(@columnNames) { $count++; $worksheet->write($row,$count,$element); } foreach my $element(@rowValues) { $worksheet->write($row,$count,$element); $count++; $row++; } }

        Sorry about the mess with the node changing. Again im new to this.

Re: Simple HTML parser
by ambs (Pilgrim) on Apr 02, 2005 at 14:47 UTC
    Although XML::DT is a XML tool, it uses libxml2 and XML::LibXML which support HTML. XML::DT has a very simple syntax and might be what you want.

    Alberto Simões

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://444373]
Approved by RazorbladeBidet
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (5)
As of 2023-12-07 17:38 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (33 votes). Check out past polls.