1

I just want to know if there could be any way by which we can read a value from an .xls file using a .bat file. For eg:If i have an .xls named test.xls which is having two columns namely 'EID' and then 'mail ID'.Now when we give the input to the .xls the EID name.it should extract the mail id which corresponds to the EID and echo the result out.

**EID**                  **MailID**
E22222                    MynameisA@company.com

E33333                    MynameisB@company.com

...
...

So by the above table,when i give the input to the xls file using my .bat file as E22222,it should read the corresponding mail ID as MynameisA@company.com and it should echo the value.

So i hope i am able to present my doubt.Please get back to me for more clarifications.

Thanks and regards Maddy

schnaader
  • 49,103
  • 10
  • 104
  • 136
  • 1
    If you can afford Perl on your system, there are ways to convert the XLS into a Comma Separated Values (CSV) file. I had checked this sometime back for myself on this SO Question: http://stackoverflow.com/questions/943153/converting-an-excel-xls-file-to-a-comma-separated-csv-file-without-the-gui. You can then grep for your data very easily. – nik Jun 20 '09 at 05:56
  • Actually, you don't need to have Perl, other things may also work as you'll find on that answer. – nik Jun 20 '09 at 06:00

4 Answers4

3

There is no facility to do this directly with traditional .bat files. However, you might investigate PowerShell, which is designed to be able to do this sort of thing. PowerShell integrates well with existing Windows applications (such as Excel) and may provide the tools you need to do this easily.

A quick search turned up this example of reading Excel files from PowerShell.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
  • Thanks a lot Greg.But just confused as to which to use.Because i need this mail ID so that i can add to my cruiseControl.NEt config files mail ID section –  Jun 20 '09 at 06:13
2

You can't do this directly from a batch file. Furthermore, to manipulate use Excel files in scripting you need Excel to be installed.

What you can do is wrap the Excel-specific stuff in a VBScript and call that from your batch.

Joey
  • 344,408
  • 85
  • 689
  • 683
2

You can do it with Alacon - command-line utility for Alasql database.

It works with Node.js, so you need to install Node.js and then Alasql package:

To take data from Excel file you can use the following command:

> node alacon "SELECT VALUE [mail ID] FROM XLS('mydata.xls', {headers:true}) 
                   WHERE EID = ?" "E2222"

Fist parameter is a SQL-expresion, which read data from XLSX file with header and search data for second parameter value: "E22222". The command returns mail ID value.

agershun
  • 4,077
  • 38
  • 41
0

This will be hard (very close to impossible) in BAT, especially when using the original XLS file, but even after an export to CSV it will be much easier to use a script/programming language (Perl, C, whatever) to do this.

schnaader
  • 49,103
  • 10
  • 104
  • 136