Page 1 of 1
Help me in creating a batch
Posted: 09 Feb 2015 00:06
by broadband
friends i need help in creating a batch file. can someone please create a batch file for my below requirment...
i have a excel sheet from where i need to copy column G, Column A and auto create a new excel file in the same folder. the new excel file should only contains content of column G and column A. This task needs to be done via batch file.
please help...
Re: Help me in creating a batch
Posted: 09 Feb 2015 07:10
by Squashman
Batch files cannot directly read, edit, or create excel files.
Re: Help me in creating a batch
Posted: 09 Feb 2015 08:47
by ShadowThief
You want an excel vba macro.
Alternatively, if these are xlsx files and you have archival software (WinZip, WinRAR, 7-Zip, etc) installed, they're technically archive files with XML inside of folders. But you really, really, really don't want to create xlsx files that way. It's just a giant headache, and it probably won't even work.
You want an excel vba macro because that will do what you want and you already have the software for that installed.
Re: Help me in creating a batch
Posted: 11 Feb 2015 22:24
by Samir
The only possible way you can do this in batch is to convert the excel files to csv and then manipulate the csv files in batch and then reimport them into excel.
Re: Help me in creating a batch
Posted: 19 Feb 2015 19:01
by Grobu
You could recycle the VBS code found on
http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line to convert your Excel spreadsheet into a CSV, in conjunction with the following batch file :
Code: Select all
@echo off
set book=book1.xls
set tempFile=_tmpcsv_.tmp
set output=columnsAG.csv
prompt>%output%
for %%a in (%tempFile%,%output%) do if exist %%a del %%a
wscript save_as_csv.vbs %book% %tempFile%
for /f "tokens=* skip=1" %%a in (%tempFile%) do call :getFields %%a
del %tempFile%
goto end
:getFields
echo "%~1","%~7">>%output%
:end
code suggestion for VBS file (called "save_as_csv.vbs" in aforementioned batch file) :
Code: Select all
dim CSV_FORMAT, fso, srcFile, dstFile, excel, book
CSV_FORMAT = 6
Set fso = CreateObject("Scripting.FileSystemObject")
srcFile = fso.GetAbsolutePathName(Wscript.Arguments.Item(0))
dstFile = fso.GetAbsolutePathName(WScript.Arguments.Item(1))
Set excel = CreateObject("Excel.Application")
Set book = excel.Workbooks.Open(srcFile)
book.SaveAs dstFile, CSV_FORMAT
book.Close False
excel.Quit
Re: Help me in creating a batch
Posted: 20 Feb 2015 08:23
by Grobu
You could use a VBS script in conjunction with a batch file to :
- save your Excel sheet into a CSV file (VBS)
- then extract columns A and G from each line and write it to an output file (batch)
Code suggestion for the VBS file :
Code: Select all
dim CSV_FORMAT, fso, srcFile, dstFile, excel, book
CSV_FORMAT = 6
Set fso = CreateObject("Scripting.FileSystemObject")
srcFile = fso.GetAbsolutePathName(Wscript.Arguments.Item(0))
dstFile = fso.GetAbsolutePathName(WScript.Arguments.Item(1))
Set excel = CreateObject("Excel.Application")
Set book = excel.Workbooks.Open(srcFile)
book.SaveAs dstFile, CSV_FORMAT
book.Close False
excel.Quit
Code suggestion for the batch :
Code: Select all
@echo off
set book=book1.xls
set tempFile=_tmpcsv_.tmp
set output=columnsAG.csv
prompt>%output%
for %%a in (%tempFile%,%output%) do if exist %%a del %%a
wscript save_as_csv.vbs %book% %tempFile%
for /f "tokens=* skip=1" %%a in (%tempFile%) do call :getFields %%a
del %tempFile%
goto end
:getFields
echo "%~1","%~7">>%output%
:end