Removing worksheets from xls using unix.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Removing worksheets from xls using unix.
# 1  
Old 04-11-2011
Removing worksheets from xls using unix.

HI All,

I have a .xls file , in which I have multiple worksheets , I need to remove all the worksheets except the worksheet by name CDR_LOAD_STATS, how can I do this in unix.

Please guide me.

Regards,
Deepti
# 2  
Old 04-11-2011
I used to do something similar using xls2txt.c, there are excel friendly PERL libs, and JAVA apis. Your challenge is to find the worksheet title. Do you want a one-worksheet xls as the product, or the data on the other pages?
# 3  
Old 04-11-2011
Hi ,


I need to retain the cdr_load_stats worksheet with data, and then this sheet as to be converted to .csv file which i will use for loading purpose.

Regards,
Deepti
# 4  
Old 04-12-2011
The big trick might be getting the sheet number you want; maybe you have to extract sheets until one comes out with the right sheet name. I suspect the new *.xlsx xml spreadsheet will be lots easier to work with!

I think you can get the sheet direct to csv with xls2txt.c, but if not, here is a "pipe delimited to csv converter" you can pipe it through:
Code:
$ cat mysrc/p2csv.c
#include <stdio.h>
main( int argc, char **argv )
{
        char    usage[] =
"\n"
"Usage: p2csv [ -s <sep> ]\n"
"\n"
"Changes the file column delimited by <sep> (pipe '|' default) from standard\n"
"input to a Comma Separated Values (CSV) formatted file on standard output.\n"
"\n" ;
        int     sep = '|' ;     /* input column separator character */
        int     c ;             /* character read */
        int     cl = 0 ;        /* found chars not leading/trailing space */
        int     fs = 0 ;        /* possibly embedded spaces found */
        int     fp = 0 ;        /* possibly trailing pipes found */
        int     fc = 0 ;        /* quote or comma found */
        char    b[65536];       /* field buffer */
        for ( c = 1 ; c < argc ; c++ )
        {
                if ( !strcmp( argv[c], "-s" )
                  && ++c < argc
                  && strlen( argv[c] ) == 1 )
                {
                        sep = argv[c][0] ;
                        continue ;
                }
                fputs( usage, stderr );
                exit( 1 );
        }
        do
        {
                switch( c = getchar() )
                {
                case EOF:
                        if ( ferror( stdin ) )
                        {
                                perror( "stdin" );
                                exit( 1 );
                        }
                        continue ;      /* Out of loop */
                case '\n':
                        if ( fc )
                        {
                                if ( 0 > printf( "\"%.*s\"\r%c", cl, b, c ) )
                                {
                                        if ( !feof( stdout )
                                          || ferror( stdout ) )
                                        {
                                                perror( "printf()" );
                                                exit( 1 );
                                        }
                                        exit( 0 );
                                }
                                fc = 0 ;
                        }
                        else
                        {
                                if ( 0 > printf( "%.*s\r%c", cl, b, c ) )
                                {
                                        if ( !feof( stdout )
                                          || ferror( stdout ) )
                                        {
                                                perror( "printf()" );
                                                exit( 1 );
                                        }
                                        exit( 0 );
                                }
                        }
                        cl = 0 ;
                        fs = 0 ;
                        fp = 0 ;
                        continue ;
                case ' ':
                        if ( cl )
                                fs++ ;
                        continue ;
                case ',':
                        fc = 1 ;
                        break ;
                case '"':
                        while ( fp )
                        {
                                fp-- ;
                                if ( EOF == putchar( ',' ) )
                                {
                                        if ( ferror( stdout ) )
                                        {
                                                perror( "stdout" );
                                                exit( 1 );
                                        }
                                        exit( 0 );
                                }
                        }
                        while ( fs )
                        {
                                fs-- ;
                                if ( cl == 65536 )
                                {
                                        fprintf( stderr, "Field too long!\n" );
                                        exit( 1 );
                                }
                                b[ cl++ ] = ' ' ;
                        }
                        if ( cl == 65536 )
                        {
                                fprintf( stderr, "Field too long!\n" );
                                exit( 1 );
                        }
                        b[ cl++ ] = c ;
                        break ;
                default:
                        if ( c == sep )
                        {
                                if ( !cl )
                                {
                                        fp++ ;
                                        continue ;
                                }
                                while ( fp )
                                {
                                        fp-- ;
                                        if ( EOF == putchar( ',' ) )
                                        {
                                                if ( ferror( stdout ) )
                                                {
                                                        perror( "stdout" );
                                                        exit( 1 );
                                                }
                                                exit( 0 );
                                        }
                                }
                                c = ',' ;
                                if ( fc )
                                {
                                        if ( 0 > printf( "\"%.*s\"%c",
                                                                cl, b, c ) )
                                        {
                                                if ( !feof( stdout )
                                                  || ferror( stdout ) )
                                                {
                                                        perror( "printf()" );
                                                        exit( 1 );
                                                }
                                                exit( 0 );
                                        }
                                        fc = 0 ;
                                }
                                else
                                {
                                        if ( 0 > printf( "%.*s%c", cl, b, c ) )
                                        {
                                                if ( !feof( stdout )
                                                  || ferror( stdout ) )
                                                {
                                                        perror( "printf()" );
                                                        exit( 1 );
                                                }
                                                exit( 0 );
                                        }
                                }
                                cl = 0 ;
                                fs = 0 ;
                                fp = 0 ;
                                continue ;
                        }
                        while ( fp )
                        {
                                fp-- ;
                                if ( EOF == putchar( ',' ) )
                                {
                                        if ( ferror( stdout ) )
                                        {
                                                perror( "stdout" );
                                                exit( 1 );
                                        }
                                        exit( 0 );
                                }
                        }
                        while ( fs )
                        {
                                fs-- ;
                                if ( cl == 65536 )
                                {
                                        fprintf( stderr, "Field too long!\n" );
                                        exit( 1 );
                                }
                                b[ cl++ ] = ' ' ;
                        }
                        break ;
                }
                if ( cl == 65536 )
                {
                        fprintf( stderr, "Field too long!\n" );
                        exit( 1 );
                }
                b[ cl++ ] = c ;
        } while ( c != EOF );
        exit( 0 );
}

# 5  
Old 04-12-2011
I have doubts that a *nix command line interface can properly translate a proprietary format such as '*.xls' and convert it to a 'non-proprietary simple *.csv' without utilizing some kind of binary program.

So, let's use the most common binary office suite...

I would think that OpenOffice Calc (spreadsheet) would be the easiest method to obtain the op's goal.

Just open the .xls file and save the tabbed spreadsheet as it's own file!
# 6  
Old 04-12-2011
No, PERL is sufficient, but not binary, and usually is the exception that proves the rule. Does JAVA qualify as binary? I am not sure yet which C# is!

If you want it to run unattended as batch or web, you need tools like xls2txt.c, but the trick here is getting the sheet title -- I never looked, tried.

PS: Originally, Access had a quote-ignoring "CSV" input processor, so test your tools!
# 7  
Old 04-12-2011
Quote:
Originally Posted by DGPickett
No, PERL is sufficient, but not binary, and usually is the exception that proves the rule. Does JAVA qualify as binary? I am not sure yet which C# is!
Perl, or binary, or JAVA, it doesn't matter; In a GUI, OOo has already solved the problem internally, so why re-write what has already been solved?

If it solves the OP's problem, let it be, unless they are running a headless system.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merging Multiple XLS into Different tabs in xls/ xlsx

HI, I have multiple files per dept in folder for eg : In a folder File1_Dept100.xls File2_Dept100.xls File3_Dept100.xls File1_Dept200.xls File2_Dept200.xls File3_Dept200.xls Output should be : Dept100.xls which has File1, File2, File3 in different tabs Dept200.xls which has... (1 Reply)
Discussion started by: venkyzrocks
1 Replies

2. UNIX for Advanced & Expert Users

Shell script for dealing with XLS file with multiple tabs/worksheets

Hey Guys , Recently working on a requirement , i had to deal with XLS file with multiple tabs and the requirement was as below : 1. Convert one XLS file with multiple tabs to multiple CSV files. -- As i was working on MAC , so it was quite easy through APPLESCRIPT to deal with this.But... (2 Replies)
Discussion started by: himanshu sood
2 Replies

3. UNIX for Dummies Questions & Answers

Merging the content of 2 cells in XLS using UNIX command

Hi All, I am creating an XLS using unix which fetches the data from database. In the first row i have 2 columns which i want to merge. Is it possible to merge the columns using unix command? Thanks, Sashanka (1 Reply)
Discussion started by: Sashanka_545
1 Replies

4. Shell Programming and Scripting

Expand cells in .xls file created in unix

Hi Guys, I am creating a comma separated file by quering the table and routing it to a file with an extension .xls. I am using the mailx command to send the .xls file to windows mail box. The file is coming as a excel attachment, but the cells are not expanded. Is there a way by which... (4 Replies)
Discussion started by: mac4rfree
4 Replies

5. UNIX and Linux Applications

Tool for Convert XLS into CSV in UNIX

Hi I wanted to convert some XLS files into CSV format in my UNIX box. Unix box is handling very important data which are related to data warehouse.It is fully optimized by installing minimum packages since server need more resources to handle reports generating. Just for convert XLS files... (6 Replies)
Discussion started by: luke_devon
6 Replies

6. Shell Programming and Scripting

How to create xls file using unix script

Hi All, I want to create an xls file using shell script. I mean that I have written one script. It is having sql output. So I want to save that sql output into one xls file and I want to send that file as a attachment to my team MAIL_IDs. Can You any guy help me in this? Thanks in... (3 Replies)
Discussion started by: raghu.iv85
3 Replies

7. UNIX for Dummies Questions & Answers

WINDOWS to UNIX : xls to csv

Hi, I need to ftp a .xls file from WINDOWS to UNIX. When I do it, the file coversion doesn't happen(??) or UNIX doesn't recognixe this format I know not. But there will be junk values in the ftp'd file. Note: The ftp will be done by a script and I don't think we could place a WINDOWS script on... (5 Replies)
Discussion started by: preethgideon
5 Replies

8. Shell Programming and Scripting

How to acheive ALT+Enter of xls in Unix ksh/sqlplus

Hi, I am using sqlplus (called inside my ksh) to create a file which is mailed to the users as an xls. There is one DESCRIPTION column which is currently coming as wrapped, but, the users want a new line for each of the fields present in the desricription column. Below is an example - ... (11 Replies)
Discussion started by: Sree_2503
11 Replies

9. Shell Programming and Scripting

copying the csv file into different worksheets of xls file

Hi, I have a script which will generate three csv files. i want to copy the contents of these csv files into a .XLS file but in different worksheets. Can a this be done in the same script? :confused: Can Perl come to my help in coping the csv files into different worksheets of .XLS file ?... (0 Replies)
Discussion started by: nimish
0 Replies

10. UNIX for Advanced & Expert Users

UNIX files to XLS

People, I am wondering if there is a Software(Filter) that can convert my UNix text file (delimeted text file) to Excel, for my business users to launch and see. Thanx Zia (4 Replies)
Discussion started by: rahzia
4 Replies
Login or Register to Ask a Question