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
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?
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
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:
$ 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 );
}
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!
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!
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.
Well, I anticipate an unsophisticated end-user interface where spreadsheets are supplied by users on a web service or network directory, and loaded without operator intervention. If it was ad-hoc, you can just save as text and edit into sql.