2

I have never done any batch scripting before and so I need help in building one of them. We have a file say "GetHistory.bat" and it accepts a parameter ID. The output of the script is as shown below:

Activity Name      Status      Date
----------------------------------------
Act1              Created    1-Jan-2013
Act2              Submitted  2-Jan-2013 
Act3              Approved   2-Jan-2013

Now the problem is I need to export the output in txt as CSV without the header and parameter ID added to each line as below: 

1001,Act1,Created,1-Jan-2013
1001,Act2,Submitted,2-Jan-2013 
1001,Act3,Approved,2-Jan-2013

Any help to begin the script would be highly appreciated. Thanks...!

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Mayu
  • 27
  • 1
  • 6
  • Could you provide some more information? Is your intent to modify `GetHistory.bat`, or to write a new batch file that will call it and then manipulate the output? What is the nature of the Activity Name data... single words, multiple words? Is the output of `GetHistory.bat` in fixed columns, as you've shown it? – Mogsdad Feb 01 '13 at 03:17
  • Thanks for replying. I would like to create a new batch file that should modify the output from GetHistory.bat. The output columns are fixed. ACtivity Name data can be single or multiple words other columns are single word and Date as in datetime. – Mayu Feb 01 '13 at 15:14

1 Answers1

1

With the assumption that the output of GetHistory.bat has been redirected into a file called history.txt, we could feed that into our new batch file, ParamCSV.bat, like so, with this result:

C:\stackoverflow>ParamCSV.bat 1001 < history.txt
1001,Act1,Created,1-Jan-2013
1001,Act2,Submitted,2-Jan-2013
1001,Act3,Approved,2-Jan-2013

To put together a quick script for this, I've referenced info from:

I came up with this batch script, ParamCSV.bat:

@echo off
:: ParamCSV.bat
::
:: Usage: ParamCSV.bat <Parameter_ID> < history.txt
::
:: Thanks to:
:: https://stackoverflow.com/questions/6979747/read-stdin-stream-in-a-batch-file/6980605#6980605
:: https://stackoverflow.com/questions/636381/what-is-the-best-way-to-do-a-substring-in-a-batch-file
:: https://stackoverflow.com/questions/3001999/how-to-remove-trailing-and-leading-whitespace-for-user-provided-input-in-a-batch

:: Copy input parameter to 'id'
set id=%1

setlocal DisableDelayedExpansion

for /F "skip=2 tokens=*" %%a in ('findstr /n $') do (
  set "line=%%a"
  setlocal EnableDelayedExpansion
  set "line=!line:*:=!"
  set "activity=!line:~0,17!"
  call:trim !activity! activity
  set "status=!line:~17,11!"
  call:trim !status! status
  set "date=!line:~28,11!"
  call:trim !date! date
  echo(!id!,!activity!,!status!,!date!
  endlocal
)
goto:EOF

::function: trim
::synopsis: Removes leading and trailing whitespace from a sting. Two
::          parameters are expected. The first is the text string that
::          is to be trimmed. The second is the name of a variable in
::          the caller's space that will receive the result of the
::          trim operation.
::
::usage:    call:trim string_to_trim var_to_update
::             e.g.   call:trim %myvar1% myvar2

::trim left whitespace
setlocal
set input=%~1
for /f "tokens=* delims= " %%a in ("%input%") do set input=%%a
::trim right whitespace (up to 100 spaces at the end)
for /l %%a in (1,1,100) do if "!input:~-1!"==" " set input=!input:~0,-1! 
::return trimmed string in place
endlocal&set "%~2=%input%"

There are a number of assumptions that are made here, and if any of them change or are invalid, the script will break:

  • The output of GetHistory.bat has fixed-width columns, of width 17,11, and 11. You didn't provide an example of a two-digit day, so I've assumed the dates are right-aligned.
  • There are two header lines, which we skip in the for statement.
  • All output lines are for the same ID, so only one input parameter is expected, and it is the first element in all CSV output lines.
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • I know you asked for help to begin the script, but I had fun with that and just couldn't stop until it was done! Feel free to ignore the code, and do it yourself by reading the references! ;^) – Mogsdad Feb 01 '13 at 16:45
  • Thanks Mogsdad. I did the sample work using your code and have also gone through the references. but when i tried implementing the same in real world i got lost. To begin with I want to know 1)How can we remove the first four and last four lines from text file. 2) in some scenarios the Comments and Resource column value will be blank thus how to handle that inside the script? I dont find a way to attach the actual text file thus apologies if my questions are not self explanatory. - Mayu – Mayu Feb 26 '13 at 11:53
  • Removing lines at the top is easy, see the `skip=2` parameter in the `FOR..DO` loop. The end of the line would be trickier. If you first find out how many lines you have, you could `echo` the first (n-4). If there is a way to identify those lines by their text (e.g. the solid line in the example), you could trigger on that instead. You could add a second example of file contents to your original question with the trouble scenarios in it, or you could ask another question. Either way, add a comment with "@Mogsdad" in it, so I see it. – Mogsdad Feb 26 '13 at 13:19
  • "@Mogsdad" I have created a new post Kindly have look http://stackoverflow.com/questions/15109121/call-batch-file-multiple-command-lines-to-get-the-output-from-vbs – Mayu Feb 27 '13 at 10:03