Dealing with XLS file with multiple tabs

Linux and Unix-Like Emergency UNIX and Linux Support
1

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 during this , i read lots of forum and learnt that UNIX shell scripting do not have any utility or solution for this.Is this really true?bcaz i everywhere found ppl recommending PERL or other stuff .

----------------------
Real Problem.
----------------------
2. Once Files are converted to CSV , we have to rename all CSV files with names as in different tab from XLS i.e first CSV should be named as the first tab in that XLS and so on .

Is there anyway in scripting to capture those tab names from XLS?

and if not , what alternative i should try to make this happen ?

I am eagerly looking forward for your replies.

Cheers!!!
Himanshu Sood

2

Additional Information:-

Added attachment has 3 sheets in that and the new csv files need to have those names in it .

E.g :-
Origin File Name :- 20130430_Himanshu.xls

CSV File Name :-
20130430_DE000A0V7550_Himanshu.csv
20130430_DE000A0V7568_Himanshu.csv
20130430_DE000A0V7570 _Himanshu.csv

3

himanshu,
One solution would be to write a VBA/macro in Excel or Open Office itself to perform the actions in your requirement. XLS files are binary, not text, files so writing scripts to read and process XLS files will not work unless you first convert them to text/csv files (or you use API's such as in Open Office or ActiveX - Windows to interact with them).

4

mjf,

I have converted my files to csv using Applescript and now the problem is renaming themas required.
As i dont want to hard code anything so looking for some dynamic way to do so.

Any help ?

Regards,
Himansuh Sood

5

VBA Code

Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & ".xls"
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

---------- Post updated at 09:08 AM ---------- Previous update was at 09:02 AM ----------

This code is tested and working for me

6

Hello Sri,

Is it possible to add this VBA stuff to Shell script ?

Anyone out there can help me on this using scripting .

Himanshu Sood

7

I don't think that is possible with shell scripting
You will have to load some excel packages to deal with excel files and you have to go with Perl or java or some other programming code

---------- Post updated 04-22-14 at 12:53 AM ---------- Previous update was 04-21-14 at 01:42 PM ----------

Below links might help you to get started
Spreadsheet::WriteExcel
Spreadsheet::SimpleExcel - search.cpan.org

1 Like