Batch File to read excel

[dinero]

Hey all, so im kinda new to batch files and need some help coding something.
So basically, I want to use the batch file to read the values from the specific column and row from an excel sheet. So if I have a column with student grades, I want the batch file to look at a specific column eg column C row 12 and if the number is equal to 50, I want it to output “the student passed”.
Can anyone provide assistance on the code to be used? Could this be done without using windows powershell command? Appreciate any help provided.

[carlsomo]

Hi dinero,
This batch script 'XLS2CSV.bat' can be run from the command line or called from a separate batch routine with an excel spreadsheet file as an argument. It first creates a vbs script that copies/converts the excel to a CSV file. It produces a comma delimitied 'CSV' text file that can be read with your batch script with a for /f loop such as:
for /f "skip=%headerlines% tokens=3* delims=," %%C in (CSV_File) do ( your code here)
Your batch routine will can call this batch file giving the excel filename as argument.
This should work well unless you have commas in the individual cells in the spreadsheet. If you need more help with the script to read the CSV output file let us know...

Code: Select all

::XLS2CSV.bat <xlsx_file> [csv_file_name]
@echo off&setlocal
if "%~1"=="" echo(Excel file input missing...&goto:eof
if not exist "%~dp0excel2csv.vbs" (
(
echo Set objFSO = CreateObject^("Scripting.FileSystemObject"^)
echo src_file = objFSO.GetAbsolutePathName^(Wscript.Arguments.Item^(0^)^)
echo dest_file = objFSO.GetAbsolutePathName^(WScript.Arguments.Item^(1^)^)
echo Dim oExcel
echo Set oExcel = CreateObject^("Excel.Application"^)
echo oExcel.DisplayAlerts = FALSE
echo oExcel.Interactive = FALSE
echo Dim oBook
echo Set oBook = oExcel.Workbooks.Open^(src_file, 0 , TRUE^)
echo Set objWorksheet = oExcel.Worksheets^(1^)
echo objWorksheet.Activate
echo oBook.SaveAs dest_file, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, TRUE
echo oBook.Close False
echo Set objWorksheet = Nothing
echo set oBook = Nothing
echo oExcel.Quit
echo set oExcel = Nothing
)>>"excel2csv.vbs"
)
if "%~2"=="" set "outfile=%~dpn1.csv" else set "Outfile=%~2"
cscript /B %~dp0Excel2csv.vbs "%~1" "%Outfile%"
exit/b %errorlevel%

[Aacini]

@carlsomo: It seems that there are some small errors in the code you posted above. I fixed they and modified the code for Batch-JScript hybrid file format.


Below is a Batch file that convert a .XLS Excel file into a .CSV one; I also added the code to achieve the particular OP request, the added code is preceded by upper-case REM comments.

Code: Select all

@if (@CodeSection == @Batch) @then


@echo off
setlocal

rem XLSTOCSV.BAT: Convert a .XLS file into a .CSV one
rem Antonio Perez Ayala

if "%~1" equ "" (
   echo Convert a .XLS file into a .CSV one
   echo/
   echo XLSTOCSV.BAT inputFile.xls [outputFile.csv]
   goto :EOF
)
if "%~2" equ "" (set "outFile=%~dpn1.csv") else set "outFile=%~2"
Cscript //nologo //E:JScript "%~f0" "%~1" "%outFile%"

REM Read row number 12, field number 3 (column C)
for /F "usebackq skip=11 tokens=3 delims=," %%a in ("%outFile%") do (
   REM If the field is GREATER OR equal 50...
   if %%a geq 50 (
      echo The student passed
   ) else (
      echo The student NOT passed!
   )
   goto break
)
:break

goto :EOF


@end


// JScript section

var objFSO = new ActiveXObject("Scripting.FileSystemObject");
var src_file = objFSO.GetAbsolutePathName(WScript.Arguments(0));
var dest_file = objFSO.GetAbsolutePathName(WScript.Arguments(1));
var oExcel = new ActiveXObject("Excel.Application");
oExcel.DisplayAlerts = false;
oExcel.Interactive = false;
var oBook = oExcel.Workbooks.Open(src_file, 0, true);
// File format (fields): 3 = tab separated, 6 = comma separated, 9 = may be useful
oBook.SaveAs(dest_file, 6);
oBook.Close(false);
oExcel.Quit();

As carlsomo indicated: "This should work well unless you have commas in the individual cells in the spreadsheet".

Antonio

[Ocalabob]

@Aacini,
In trying your code I encountered an error. Here's a screen shot:

Code: Select all

C:\csv_test>xlstocsv input.xls output.csv
C:\csv_test\xlstocsv.bat(1, 6) Microsoft JScript compilation error: Conditional
compilation is turned off
The system cannot find the file output.csv.
C:\csv_test>

Any ideas to help me with testing?

Thank you!

[Ocalabob]

@Carlsomo
I tested your script with the batch file below:

Code: Select all

::GET_GRADE_50.BAT
@Echo off
Setlocal

Call XLS2CSV.bat my_grades.xlsx

For /f "Skip=11 Tokens=3 Delims=," %%b in (my_grades.csv) do set grade=%%b & Goto :score

:score
If %grade% GEQ 50 Echo "The student passed" & Goto :end
Echo "The student's score was %grade%"
:end
Pause
Del excel2csv.vbs
Del my_grades.csv

Everything worked as expected. We'll see what the OP has to say. Thank you!

[Aacini]

@Ocalabob:

This is somewhat strange. When I tried carlsomo's code no error was shown, but no .csv file was created, so I changed cscript's /B option by //nologo and got this error:

Input Error: There is no file extension in "C:\Documents".

because my path "C:\Documents and Settings\Antonio\My Documents\tests" have spaces. I enclosed in quotes the name of the .vbs file in this line:

Code: Select all

cscript //nologo "%~dp0Excel2csv.vbs" "%~1" "%Outfile%"

and now got this error:

C:\Documents and Settings\Antonio\My Documents\tests\Excel2csv.vbs(12, 1) Microsoft VBScript runtime error: Wrong number of arguments or invalid property assignment: 'SaveAs'

After several tests I discovered that the cause of this error was the last parameter in this line:

Code: Select all

echo oBook.SaveAs dest_file, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, TRUE

although the documentation indicate that the format of .SaveAs is right! The only way I found to eliminate this error was removing the last parameter in previous line, but because the nine zeros are placed there just to insert the TRUE at end, I also eliminated they this way:

Code: Select all

echo oBook.SaveAs dest_file, 6

After that, the .csv file was correctly created.

My computer have Windows XP SP3 and the Windows Script Host version reported by cscript.exe is 5.7. Perhaps this is the cause of my problem with SaveAs?

On the other hand, I used my method to write a Batch-JScript hybrid script many times with no problems. Just yesterday an user in SO confirmed me that this method correctly works.

Antonio

[foxidrive]

I tested them both with Windows 8 and Office 2013:

The both created identical .csv files when testing with .xlsx files and in folders that have spaces and & in the pathname.

c:\Files\bat\Misc\Excel-XLS-to-CVS-conversion2.bat "d:\abc\123 & 456\Movie list app1.xlsx"


Carlsomo's code needed to have these lines changed/added to handle long filenames and clean up the VBS file.

cscript /B "%~dp0Excel2csv.vbs" "%~1" "%Outfile%"
del "%~dp0Excel2csv.vbs"

and this line - to handle a case where the batch file is called from a remote folder.

)>>"%~dp0excel2csv.vbs"


They both have issues when the batch file name has spaces in it, variously in the current folder or called from a remote folder.


FWIW there was an odd artifact at the top of each .csv file - see below - one or more lines of just commas.

Code: Select all

,,,,,,,,,,
Year,Title,Critics Score,Audience Score,Cast,Director,Genre,MPAA rating,Format,Comments,ID
1994,Forrest Gump,70,93,"Tom Hanks, Robin Wright, Gary Sinise, Mykelti Williamson",Robert Zemeckis,"Drama, Romance, Comedy",PG-13,DVD,,10036
1946,It's a Wonderful Life,93,94,"James Stewart, Donna Reed, Lionel Barrymore, Thomas Mitchell",Frank Capra,"Drama, Kids & Family, Classics, Science Fiction & Fantasy",PG,VHS,Original B&W version,18062
1988,Big,96,73,"Tom Hanks, Elizabeth Perkins, John Heard, Jared Rushton",Penny Marshall,"Kids & Family, Science Fiction & Fantasy, Comedy",PG,DVD,,10396
1988,Rain Man,88,88,"Dustin Hoffman, Tom Cruise, Valeria Golino, Jerry Molen",Barry Levinson,Drama,R,DVD,,15245
1990,The Bonfire of the Vanities,23,27,"Tom Hanks, Bruce Willis, Melanie Griffith, Kim Cattrall",Brian DePalma,Comedy,R,Blu-ray,,14676

Code: Select all

,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,
,NO.,YEAR,TITLE,Review, ,  ,   ,     ,    ,STARRING ACTORS,DIRECTOR,GENRE,RATING,FORMAT,COMMENTS,
,1,1994,Forrest Gump,5 Stars,5,5,5,5,5,"Tom Hanks, Robin Wright, Gary Sinise",Robert Zemeckis,Drama,PG-13,DVD,Based on the 1986 novel of the same name by Winston Groom,
,2,1946,It’s a Wonderful Life,2 Stars,2,2,2,2,2,"James Stewart, Donna Reed, Lionel Barrymore ",Frank Capra,Drama,G,VHS,Colorized version,
,3,1988,Big,4 Stars,4,4,4,4,4,"Tom Hanks, Elizabeth Perkins, Robert Loggia ",Penny Marshall,Comedy,PG,DVD,,
,4,1954,Rear Window,3 Stars,3,3,3,3,3,"James Stewart, Grace Kelly, Wendell Corey ",Alfred Hitchcock,Suspense,PG,Blu-ray,,

[dinero]

Thanks a lot for all your help guys!!
PS sorry for the late response btw, ive just been a bit busy.

[Samir]

Forgive me for bumping an older thread, but since it is exactly on topic with what I'm trying to do, I thought it best to continue this discussion versus start a new one.

Since the xlsx format is actually xml, does anyone have a pure batch way to convert from xlsx to csv?

[Squashman]

Forgive me for bumping an older thread, but since it is exactly on topic with what I'm trying to do, I thought it best to continue this discussion versus start a new one.

Since the xlsx format is actually xml, does anyone have a pure batch way to convert from xlsx to csv?

You can have a batch file build a VBscript but ultimately the code that converts it has to be VBscript because it needs to use the Excel.Application Object to do it. In essence you need to have Excel installed on your computer to do the conversion.

[penpen]

Since the xlsx format is actually xml, does anyone have a pure batch way to convert from xlsx to csv?

If this file really is a valid xml file, you could use xslt on this xml file using a hybrid JScript/batch.
See this example: http://www.dostips.com/forum/viewtopic.php?p=32941#p32941.

penpen

[Squashman]

XLSX, DOCX, PPTX etc etc.... are just zipped xml structures. Change the extension to .zip and extract it. Good luck getting it to convert to csv from there. I have tried and couldn't find any software to do it correctly.

[carlsomo]

Here is a Jscript hybrid version that does not need to write a separate VBS
More documentation and error checking
Update: Tested on Win7 and XP
More error checking
Still needs Excel though

Code: Select all

@if (@X)==(@Y) @end /* Harmless hybrid line that begins a JScript comment

::**************** Documentation ***************
:::XLS2CSV.bat <xlsx_file> [csv_file_name] [/Y] [/S <"delim">]
:::
:::  Converts Excel spreadsheet file to a comma delimited CSV file
:::  If optional CSV output filename is not specified the output
:::  will use the original path\filename with a .csv extension
:::
:::  The default field delimiter is the comma "," character
:::  Note: The CSV file may have unexpected results if cells contain commas
:::  To change the separator a single character must follow the '/S' option
:::  or the 'tab' character may be specified by using: /S /t
:::  The pipe '|' character may be specified by using: /S /p
:::  The separator may need to be quoted, ie. /S ";" to be recognized
:::  as a non space character or special character at the command line
:::
:::  Use /Y to force confirmation of overwright if the CSV file exists
:::  Otherwise the user is prompted before the existing file is overwritten
:::
:::EXAMPLE:
:::
:::  XLS2CSV "D:\XLpath\my file.xlsx" "D:\CSVfiles\my file.csv" /Y /S ";"
:::  Will overwright the existing CSV file using ';' as the delimiter  
:::
:::  This hybyid script uses CScript:Jscript for the conversion and
:::  Returns any errors encountered in the errorlevel
:::
::**************** Batch portion ***************
@echo off&setlocal enabledelayedexpansion
echo(>con
if "%~1"=="" echo(Excel file input missing...Type %~n0 /? for help&call :seterr 1&goto :end
if "%~1" equ "/?" (
  for /f "delims=: tokens=1*" %%A in ('findstr /n "^:::" "%~f0"') do echo(%%B
  goto :end
)
if not exist "%~f1" (
  echo(Excel Filespec: %~f1
  echo(Excel file not found or does not exist...
  exit/b 2
)
set "Outfile=%~dpn1.csv"
set "Overwright="
Set "Separator=,"
set /a sep = 6
set "TAB="
call :Parse %*
if exist "%Outfile%" (
  2>nul ( >>"%Outfile%" (call )) && (
    if not defined Overwright (
      echo(%Outfile% already exists...>&2
      set/p "ANS=Do you wish to overwright? ">&2 && echo(
      if /i "!ANS:~0,1!" neq "Y" call :seterr 2&goto :end
    )
  ) || (
    echo(Cannot write to %Outfile% >&2
    echo(It is either write protected or locked by another process...>&2
    call :seterr 3&goto :end
  )
) else (
  rem.>%outfile% >nul 2>&1
  if !errorlevel! neq 0 (
    echo(Cannot write filename to path or directory...>&2 
    echo(Access is denied or filename is invalid>&2
    goto :end
  )
)
<nul set/p="Processing CSV File Conversion..."
cscript //E:JScript //nologo "%~f0" "%~f1" "%Outfile%" "%sep%"
if not exist "%Outfile%" echo(Process failed>&2&goto :end
if defined TAB (
  rem.>"%Outfile%.tmp"
  for /f "usebackq tokens=*" %%a in (`type "%Outfile%"`) do (
    set "line=%%a"
    set "line=!line:%TAB%=%Separator%!"
    echo(!line!>>"%Outfile%.sep"
  )
  copy /Y "%Outfile%.sep" "%Outfile%" >nul
  del "%Outfile%.tmp" >nul 2>&1
)
echo(Process completed
:end
endlocal&exit/b %errorlevel%

:Parse command line and set global variables
set "Next=%~2"
if not defined Next exit /b
if /i "%Next%" equ "/Y" (
  set "Overwright=true"
  if "%~3" neq "" shift&goto :Parse
) else if /i "%Next%" equ "/S" (
  if "%~3" neq "" (
    set "Next=%~3"
    if "!Next:~0,1!" neq "," if "!Next:~0,1!" neq ";" (
      if /i "!Next!" neq "/t" if /i "!Next!" neq "/p" set "Next=!Next:~0,1!"
    )
    if defined Next (
      if /i "!Next!" equ "/P" set "Next=|" 
      set "Separator=!Next!"
      if "!Separator!" neq "," (
        set/a sep=-4158
        if /i "!Separator!" neq "/t" call :CreateTAB TAB
      )
      shift
    )
  )
) else if defined Next (
  if "%Next:~0,1%" neq "/" set "Outfile=%Next%"
)
if "%~3" neq "" shift&goto :Parse
exit/b

:seterr
exit/b %~1

:CreateTAB tabVar
setlocal EnableDelayedExpansion
for /F "skip=40 tokens=*" %%a in ('shutdown /?') do (
   set "line=%%a"
   set "line=!line: =!"
   for /F "tokens=1,2*" %%x in ("!line!") do (
     set "split=!line:%%x=!"
      if "%%y" NEQ "" (      
         set "tab=!split:~0,1!"
         goto :tab_found
)  )  )
:tab_found
(
   endlocal
   set "%~1=%tab%"
   goto :eof
)

**************** JScript portion ***************/
var args = WScript.Arguments;
var objFSO=new ActiveXObject("Scripting.FileSystemObject");
var xls_file=objFSO.GetAbsolutePathName(args.Item(0));
var csv_file=objFSO.GetAbsolutePathName(args.Item(1));
var xlText = parseInt(args.Item(2));
var oExcel=new ActiveXObject("Excel.Application");
oExcel.Visible = false;
oExcel.DisplayAlerts = false;
oExcel.Interactive = false;
oExcel.ScreenUpdating = false;
var oBook=oExcel.Workbooks.Open(xls_file, 0 , true);
var objWorksheet=oExcel.Worksheets(1);
objWorksheet.Activate;
oBook.SaveAs(csv_file, xlText, 0, 0, 0, 0, 0, 0, 0, 0, 0, true);
oBook.Close();
objWorksheet=null;
oBook=null;
oExcel.Quit();
oExcel=null;

[Samir]

Since the xlsx format is actually xml, does anyone have a pure batch way to convert from xlsx to csv?

If this file really is a valid xml file, you could use xslt on this xml file using a hybrid JScript/batch.
See this example: http://www.dostips.com/forum/viewtopic.php?p=32941#p32941.

penpen

Turns out there is a way to get a tabbed delimited csv, so I don't have to try to convert real xml to csv. Now I just have to change the tabs to commas...

[dbenham]

Don't forget about my parseCSV.bat utility. It can easily handle tab delimited files.


Dave Benham