Creating Batch files.

[lvr]

Hello every one,

I am a newbie to linux. Here is my scenario. I do some step of processes daily to some excel files which are located in the linux box. This excel files are in.csv(comma separated values). I copy these to my windows "My documents" using Winscp. Then I save them and convert that in to .xls(By importing and delimiting comma's). Then i have to delete certain columns and format some columns, which are simple so I do them using macros. As this is a daily step for deleting and formating columns I used macros and I have saved them(macros).

Them I save these formatted files under different folder. All the files have same name but they just differ in date(abcsep102008.xls,abcsep112008...)as I do this daily. Then i upload to FTP.

I have no idea on how to automate it using batch files.
I have a couple of questions:

1) how can I proceed doing this.
2) I have created macros for excel files, so is it possible to run the macros in batch files automatically.
3)what is the better approach to do this a) doing it in the windows(.bat) or in linux(shell pgm)
4)if i do it in the windows i need to copy from linux to windows? which will be a manual step then using batch files it can be automated.
5)if I use shell pgm in the linux box, how can i use marcos. Do i need to save the .vbs separately then call them in the shell pgm?

Guys it would be a lot of help if some one can help me with this, as I do this daily

[Cabhan]

Well, DOS Batch files are pretty rudimentary. I don't know them at all, but I imagine it would be difficult to use them for something like this.

One option that I do see would be skipping the Excel step entirely. If the files are in CSV format already, you could probably very easily write a Bash or (more likely) Perl/Python/Ruby script to do the processing entirely on the Linux box. If you needed them to be in Excel format, you could use some Excel converter (I know there's a Perl module for this; I imagine that there are more utilities as well) to convert them on the Linux box, and then upload. All of this is easily automatable on Linux.

If you still prefer to use Windows, I imagine that through some combination of Bash/Cygwin (a port of Bash to Windows; use to automate the scp step) and VBScript (to do Excel transformations) could accomplish this for you. However, I know nothing about VBScript or how Cygwin interacts with Windows programs, so that's as far as I can take this.

[lvr]

Thanks Cabhan.


I really get ur point. Coneverting the files from linux to windows is a huge and complicated thing. Yes the files are in .csv. u mentioned that if i need to process in linux usinf bash or perl, u mean deleting some rows naming then according to the dates and saving them in a folder right?

Can i have some examples of the bash script or some links to that.

And in the above mess u mentioned the excel converter's. can i have some links to this.

i agree with u doing this automable in linux usinf bash.

thanks for ur help.
lvr

[HROAdmin26]

Everything you need can be found in this Bash guide.

[Cabhan]

So let's say that we wanted to do this in Perl.

Using CPAN, I can find a module for parsing CSV files:
Class::CSV - Class based CSV parser/writer - search.cpan.org
(or, alternatively, you can use the CSV module for DBI)

You could then use a module like this to create an Excel worksheet:
Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file. - search.cpan.org

Alternatively, here is a guide to working with Excel spreadsheets via Perl:
Cultured Perl: Reading and writing Excel files with Perl

So you would use Perl to read the CSV and do whatever your macros used to do, and then output it to an Excel file with your naming scheme.

Make sense?