Need Help in converting Post: 303027633

Sponsored Content

Top Forums Shell Programming and Scripting Need Help in converting Post 303027633 by Corona688 on Thursday 20th of December 2018 03:06:01 PM

12-20-2018

Registered User

I have this tool for converting XLS files to flatfiles:

Code:

#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;
use strict;

my $filename = shift || "Book1.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
#    print "#Worksheet $sheet: $sheetName\n";
    next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCol})));
    foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
        foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
            if (defined $eSheet->{Cells}[$row][$column])
            {
                print $eSheet->{Cells}[$row][$column]->Value . "|";
            } else {
                print "|";
            }
        }
        print "\n";
    }
}

You will have to install the Perl module Spreadsheet::ParseExcel. Sorry. No matter how you do it you have to install something to handle XLS in UNIX.

Using it:

Code:

$ ./xlstoflat.pl ./xlstest.xls | awk -F"|" -v OFS="|" '{ sub("^" $1 "_", "", $2); } 1'
Serial Number|Serial Name|Serial Brand|
111|test|sample|
123|test2|sample1|
134|test3|sample2|
135|test4|sample3|

$

This User Gave Thanks to Corona688 For This Post:

Corona688

View Public Profile for Corona688

Visit Corona688's homepage!

Find all posts by Corona688

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

converting kb to mb

When I create filesystems in AIX i often get confused(using smit) When you specify size in aix, it is asked like this SIZE of file system (in 512-byte blocks) I never seem to grasp this, what is the equation to get say 500mb? Or is there a program anyone knows of that does this, like a...

2. UNIX for Dummies Questions & Answers

converting .txt

Hello, I transferred some .txt files from windows to Unix. When i used the editor in Unix to open up the file, all the <cr> show up. How to I get rid of all of them?

3. Shell Programming and Scripting

Converting to Uppercase

I want to convert string into uppercase string. How can i do that ? Ex: Enter the user name: read name show=upper(name) echo $show --- This output should be the uppercase output. Thanks

4. SCO

Converting

I use Sco_Sv 3.2v5.0.5 with parellel conection using dump terminals and i want to convert them to desktop pc. Anybody knows what hardware and other thing that would be involved?

5. Shell Programming and Scripting

Converting \0 to a \n

Hi - I have seen some similar posts but I am a bit stumped here below is the first line of a 'od -c filename' command. I want to change the \0 to \n 0000000 l s \0 c d - \0 c d . . \0 l s I have tried a sed construct in a script......... sed...

6. Shell Programming and Scripting

Converting Coins

Hi all, I have never written a script so I guess you could say I am very yellow.:) However, I could use some help.... I need to write a Korn Shell Script that converts coins. Meaning if I print or echo "Please enter amount:" and the user puts in 3.87, I need to know the least amount of coins...

7. SCO

library converting

Hi everybody Is there any sco unix command to convert .so library to .a (under sco unix openserver.5.0.6) tnx

8. UNIX for Advanced & Expert Users

Need help converting ctlU (^U) to a \12...

I have a file that ends with a ctlU: > cat -v test.file blah,blah,blah,GEAEA*1*xx0000111xxx^UIEA*xxx0^U would like to replace the ctlU (^U) with a "\12"...due to printers or something. I believe I might be able to utilize the tr command, if I could only identify the correct...

9. Solaris

Help converting my Script

So i have been asked to write a script for a friend who has a server running solaris 11. My problem is the correct syntax i can't seem to get it work with the follow code, i have read through the date man page and found no work around Below is the code for part of the rsync script. The code i...

10. Shell Programming and Scripting

converting empty value into NA

I have a set of values separated by a tab ch:12 1 3 4 ch:13 3 3 4 ch:25 1 8 ch:23 2 8 1 There is a missing value in the third column and i would like replace it with NA such that the final output would look like ch:12 1 3 ...

LEARN ABOUT DEBIAN

tk::tablematrix::spreadsheet

TableMatrix::Spreadsheet(3pm)				       perl/Tk Documentation				     TableMatrix::Spreadsheet(3pm)

NAME

       Tk::TableMatrix::Spreadsheet - Table Display with Spreadsheet-like bindings.

SYNOPSIS

	 use Tk;
	 use Tk::TableMatrix::Spreadsheet;

	 my $t = $top->Scrolled('Spreadsheet', -rows => 21, -cols => 11,
				     -width => 6, -height => 6,
				     -titlerows => 1, -titlecols => 1,
				     -variable => $arrayVar,
				     -selectmode => 'extended',
				     -titlerows => 1,
				     -titlecols => 1,
				     -bg => 'white',
			   );

DESCRIPTION

       Tk::TableMatrix::Spreadsheet is a Tk::TableMatrix-derived widget that implements some bindings so the resulting widget behaves more like a
       spreadsheet.

       Bindings Added:

       o
	Row/Col resize handles appear when the cursor is placed over a row/col border line in the rol/col title area.

	Dragging these handles will resize the row or column. If multiple rows or columns are selected, then the new row/col size will apply to
	all row/cols selected.

	Note: With the base Tk::TableMatrix, it is possible to resize the row/cols by dragging on any cell border. To be more spreadsheet-like,
	Tk::TableMatrix::Spreadsheet  defaults to enable row/col resizing only thru the title row/col dragging. To override this default behavoir,
	set the -resizeborder option to 'both' at startup.

       o
	A popup menu for row/col insert/delete appears when the mouse is right-clicked in the row/col title areas.

       o
	Cells activate (i.e. the contents become edit-able) only when the cell is double-clicked or the F2 button is pressed. The default
	Tk::TableMatrix behavior is for the cell to be activated when the cell is single-clicked.

       o
	The Escape key causes any changes made to a cell to be canceled and the current selection cleared.

       o
	The return key causes the the current cell to move down.

       o
	The tab (or shift tab) key causes the current cell to be moved to the right (left).

       o
	The delete key will delete the current selection, if no cell is currently active.

       o
	The Mouse button 2 (middle button) paste from the PRIMARY. (Control-v pastes from the clipboard).

Additional Information
       Widget methods, options, etc, are inherited from the Tk::TableMatrix widget. See its docs for additional information.

Tk1.23								    2007-01-09					     TableMatrix::Spreadsheet(3pm)

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

converting kb to mb

Discussion started by: csaunders

2. UNIX for Dummies Questions & Answers

converting .txt

Discussion started by: laila63

3. Shell Programming and Scripting

Converting to Uppercase

Discussion started by: dreams5617

4. SCO

Converting

Discussion started by: seeyou