Output the batch result to CSV text after removing certain data and adding new data

Question

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...!

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
– Mogsdad, Commented Feb 1, 2013 at 3: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
– Mayu, Commented Feb 1, 2013 at 15:14

Community · Accepted Answer · 2017-05-23 12:12:52Z

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:

Read stdin stream in a batch file
What is the best way to do a substring in a batch file?
How to remove trailing and leading whitespace for user-provided input in a batch file?
DOS Batch - Function Tutorial provided a thorough overview of parameter passing in functions.

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.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Feb 1, 2013 at 16:42

Mogsdad

46k21 gold badges167 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mogsdad Over a year ago

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! ;^)

Mayu Over a year ago

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

Mogsdad Over a year ago

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.

Mayu Over a year ago

"@Mogsdad" I have created a new post Kindly have look stackoverflow.com/questions/15109121/…

Collectives™ on Stack Overflow

Output the batch result to CSV text after removing certain data and adding new data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related