DosTips.com

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%

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();

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>

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

Code: Select all

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

Code: Select all

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

Code: Select all

echo oBook.SaveAs dest_file, 6

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,,

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;