Convert Excel Worksheet to CSV & store filename in TXT. Skip filename on future script execution

Question

I would like to make a batch script, that

a.) converts XLS or XLSX to CSV b.) writes the excel's name into a TXT c.) skips this excel on any consecutive runs of the script

Complete script below the question

---

I currently have the following:

1.) The Batch File that runs the converter (courtesy of someone in the link below)

FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "Sheet2" "%%i" "%%i.csv" "CSVlog.txt"

2.) The Converter by ScottF & Christian Lemer at Convert XLS to CSV on command line

if WScript.Arguments.Count < 3 Then
WScript.Echo "Please specify the sheet, the source, the destination files. Usage: ExcelToCsv <sheetName> <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 23

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(1))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(2))
CSVlog = objFSO.GetAbsolutePathName(WScript.Arguments.Item(3))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.Sheets(WScript.Arguments.Item(0)).Select
oBook.Application.Columns("A:J").NumberFormat = "@"
oBook.SaveAs dest_file, csv_format

oBook.Close False

objFSO.opentextfile(CSVlog)
CSVlog.Write(dest_file)
oExcel.Quit

The script quits with an error, "Object required: 'CSVlog', after having made the CSV.

Could someone please help me

1.) remove the "xlsx" from the CSV name? (currently the CSVs are stored as "Workbook.xlsx.csv")

2.) write the xlsx name the CSVlog.txt

3.) Query the log file before starting the conversion. And if the file has already been processed, not to process it again.

thanks in advance, Henning

---

Complete Script

Hi, for all that need it, attached the whole script.

This script will look for *.xls in its folder and convert them to *.csv.

You can replace *.xls below to *.xlsb, *.xlsm, *.xlsm

Thanks to ScottF, Christian Lemer and MC ND for the originals and the help!

Batch file:

:: Feed something into CSVLog to avoid "no search strings" errors
echo ;%date: =_%_%time: =_% >> CSVLog.txt

FOR /f "delims=" %%i IN (
    'DIR *.xls /b ^| findstr /v /r /x /g:CSVlog.txt'
) DO ExcelToCSV.vbs "Details" "%%i" "%%~ni.csv" "CSVlog.txt"

I have added /r because /l would have issues on some filenames. If you are having issues, replace

findstr /v /r /x /g:CSVlog.txt with

findstr /v /l /x /g:CSVlog.txt'

VB Script

REM based on ScottF & Christian Lemer at https://stackoverflow.com/questions/1858195/
REM & MC ND at https://stackoverflow.com/questions/23381408/
REM script will convert a defined Worksheet from an XLS into CSV.Then store the name of the XLS in the CSVlog.txt.
REM it will not process any XLS that are found in the CSVlog.txt
if WScript.Arguments.Count < 4 Then
WScript.Echo "Please specify the sheet, the source, the destination files. Usage: ExcelToCsv <sheetName> <xls/xlsx source file> 

<csv destination file>"
Wscript.Quit
End If

csv_format = 23

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(1))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(2))
CSVlog = objFSO.GetAbsolutePathName(WScript.Arguments.Item(3))
entry = Wscript.Arguments.Item(1)

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.Sheets(WScript.Arguments.Item(0)).Select
oBook.Application.Columns("A:J").NumberFormat = "@"
oBook.SaveAs dest_file, csv_format,,,,,,,,,,True

oBook.Close False

oExcel.Quit
objFSO.opentextfile(CSVlog,8,True).WriteLine(Wscript.Arguments.Item(1))

This will make a semi-colon separated "CSV". To make a comma-separated CSV,

remove ,,,,,,,,,,True from oBook.SaveAs dest_file, csv_format,,,,,,,,,,True

Answer 1

For the 1 and 3 points

:: Feed something into CSVLog to avoid "no search strings" errors
echo ;%date: =_%_%time: =_% >> CSVLog.txt

FOR /f "delims=" %%i IN (
    'DIR *.xlsx /b ^| findstr /v /l /x /g:CSVlog.txt'
) DO ExcelToCSV.vbs "Sheet2" "%%i" "%%~ni.csv" "CSVlog.txt"

The findstr will filter the list returning only the files not contained in CSVlog.txt (just ensure this file exists). The %%~ni.csv (only the filename, without the extension, with added .csv text) will handle the .xlsx removal

For the point 2

....
oBook.Close False
oExcel.Quit

objFSO.opentextfile(CSVlog,8,True).WriteLine(Wscript.Arguments.Item(1))

If you open a file, you have two options. Store a reference to the opened file in a variable and use this reference to write to the file, or directly use the obtained reference to write to the file. This second option is what is used. The name of the .xlsx file is written to the log, as is what will be used to filter the list of input files. Close method has been omitted, as the file will be closed when the reference to the file goes out of scope.