CODEWITHSUNDEEP
windowsbatch-filecsv
nyehus
nyehus

Reputation: 35

windows batch file change blank field to value

I am writing a windows script to read in a txt file with 9 columns of data I want to write this data out to a csv file to be loaded into a database.

I have found that one column is sometimes blank, so I would like to enter code 'rdp' another column has mostly numbers, but for reason some values are a '.' (presumably indicating value less than 1. I would like to change these values to '0'

my code

  for /F %%b in (c:\ts_users\newfiles_list.txt) do (
      for /F "tokens=1,2,3,4,5,6,7,8,9" %%i in (%%b) do (
        echo %%i,%%j,%%k,%%l,%%m,!idle!,%%o %%p,%%q >>%%~nb.csv
      )
    )

this manages to read in the txt file, then write it out as a csv. column k is sometimes empty, column n sometimes has a '.'

I have tried variations of

    set var=rdp
    if %%k="" then set !k:=!var!

which doesnt work, so I a little stumped (after googling internet for days)

current input

201401241611 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME eu1ptsw002  
201401241611 julie.noble           rdp-tcp#9           3  Active         18  24/01/2014 14:24 eu1ptsw002  
201401241611 svc.perfmon18                             7  Disc            3  24/01/2014 14:15 eu1ptsw002  
201401241611 reto.hofstetter                          10  Disc           40  24/01/2014 10:57 eu1ptsw002  
201401241611 lester.valentin                          14  Disc         1:16  24/01/2014 11:53 eu1ptsw002  
201401241611 philippe.bachmann                        15  Disc            2  24/01/2014 12:45 eu1ptsw002  
201401241611 patrik.soderlund      rdp-tcp#2          21  Active         24  24/01/2014 07:42 eu1ptsw002

current output

201401241611,USERNAME,SESSIONNAME,ID,STATE,,TIME LOGON,TIME 
201401241611,julie.noble,rdp-tcp#9,3,Active,,24/01/2014 14:24,eu1ptsw002 
201401241611,svc.perfmon18,7,Disc,3,,14:15 eu1ptsw002, 
201401241611,reto.hofstetter,10,Disc,40,,10:57 eu1ptsw002, 
201401241611,lester.valentin,14,Disc,1:16,,11:53 eu1ptsw002, 
201401241611,philippe.bachmann,15,Disc,2,,12:45 eu1ptsw002, 
201401241611,patrik.soderlund,rdp-tcp#2,21,Active,,24/01/2014 07:42,eu1ptsw002

required output

201401241611,USERNAME,SESSIONNAME,ID,STATE,,TIME LOGON,TIME 
201401241611,julie.noble,rdp-tcp#9,3,Active,18,24/01/2014 14:24,eu1ptsw002 
201401241611,svc.perfmon18,rdp,7,Disc,3,24/01/2014 14:15,eu1ptsw002 
201401241611,reto.hofstetter,rdp,10,Disc,40,24/01/2014 10:57,eu1ptsw002, 
201401241611,lester.valentin,rdp,14,Disc,1:16,24/01/2014 11:53,eu1ptsw002 
201401241611,philippe.bachmann,rdp,15,Disc,2,24/01/2014 12:45,eu1ptsw002 
201401241611,patrik.soderlund,rdp-tcp#2,21,Active,24,24/01/2014 07:42,eu1ptsw002

Upvotes: 1

Views: 1386

Answers (1)

Endoro
Endoro

Reputation: 37589

@echo off &setlocal disableDelayedExpansion
for /f "delims=" %%a in (file) do (
    set "line=%%~a"
    setlocal enabledelayedexpansion
    set "line=!line:,.,=,0,!"
    set "line=!line:,,=,rdp,!"
    echo(!line!
    endlocal
)

Upvotes: 4

Related Questions