Here is a question about the FOR command, is there a way to change the default parsing so it doesn't ignore blank LINES? The command I am using

FOR /F options ... blah by default ignores blank lines, what happens if I want to echo them to output as a blank line?

I can remove the /F but then delims is not expected as this time.Code: [Select]ECHO off
CLS
TITLE Converting TAB delimited to CSV


setlocal enabledelayedexpansion

SET STIME=%TIME%

SET inputF=input1.txt
if not exist !inputF! ECHO Input file missing? & Pause & EXIT
SET outputF=output.csv
if exist !outputF! Del !outputF!


For /F "delims=" %%A in (%inputF%) do (
REM SET count=0
SET string=%%A

ECHO !string! |find /I "INST:" >nul
IF "!errorlevel!"=="0" call :Header "!string!"
ECHO !string! |find /I "Program" >nul
IF "!errorlevel!"=="0" call :Header "!string!"
ECHO !string! |find /I "BATCH#" >nul
IF "!errorlevel!"=="0" call :Header1
ECHO !string! |find /I "......" >nul
IF "!errorlevel!"=="0" call :Header "!string!"


ECHO !string! |find /I "Original" >nul
IF "!errorlevel!"=="0" call :VarOrig
ECHO !string! |find /I "ChangedTo" >nul
IF "!errorlevel!"=="0" call :VarChg

<nul call set /p z= .

)
)
GOTO END

:Header
REM set /a count+=1
ECHO !string!>>%outputF%
goto :eof

:Header1
REM SET /a count+=1
ECHO Batch#, Trans#, Old/New, ISN, AUX, RT NUMBER, W.AUX, Account, TC, Description, Amount>>%outputF%
ECHO.>>%outputF%
goto :eof

:VAROrig
SET BATCH=0,6
SET Trans=6,8
SET OLDNEW=14,10
SET ISN=24,10
SET AUX=34,9
SET RT=43,10
SET WAUX=53,9
SET Acct=62,18
SET TC=79,8
SET Desc=86,13
SET Amt=98,14

SET VAR1=!string:~%BATCH%!
REM ECHO VAR1:!var1! & PAUSE
SET VAR2=!string:~%Trans%!
REM ECHO VAR2:!var2! & PAUSE
SET VAR3=!string:~%OLDNEW%!
REM ECHO VAR3:!var3! & PAUSE
SET VAR4=!string:~%ISN%!
REM ECHO VAR4:!var4! & PAUSE
SET VAR5=!string:~%AUX%!
REM ECHO VAR5:!var5! & PAUSE
SET VAR6=!string:~%RT%!
REM ECHO VAR6:!var6! & PAUSE
SET VAR7=!string:~%WAUX%!
REM ECHO VAR7:!var7! & PAUSE
SET VAR8=!string:~%Acct%!
REM ECHO VAR8:!var8! & PAUSE
SET VAR9=!string:~%TC%!
REM ECHO VAR9:!var9! & PAUSE
SET VAR10=!string:~%Desc%!
REM ECHO VAR10:!var10! & PAUSE
SET VAR11=!string:~%Amt%!

ECHO !VAR1!,!VAR2!,!VAR3!,!VAR4!,!VAR5!,!VAR6!,!VAR7!,!VAR8!,!VAR9!,!VAR10!,!VAR11!>>%outputF%
goto :eof

:VARChg
SET BATCH=0,6
SET Trans=6,8
SET OLDNEW=14,11
SET ISN=25,10
SET AUX=35,9
SET RT=44,10
SET WAUX=54,9
SET Acct=63,18
SET TC=80,8
SET Desc=87,13
SET Amt=99,14

SET VAR1=!string:~%BATCH%!
REM ECHO VAR1:!var1! & PAUSE
SET VAR2=!string:~%Trans%!
REM ECHO VAR2:!var2! & PAUSE
SET VAR3=!string:~%OLDNEW%!
REM ECHO VAR3:!var3! & PAUSE
SET VAR4=!string:~%ISN%!
REM ECHO VAR4:!var4! & PAUSE
SET VAR5=!string:~%AUX%!
REM ECHO VAR5:!var5! & PAUSE
SET VAR6=!string:~%RT%!
REM ECHO VAR6:!var6! & PAUSE
SET VAR7=!string:~%WAUX%!
REM ECHO VAR7:!var7! & PAUSE
SET VAR8=!string:~%Acct%!
REM ECHO VAR8:!var8! & PAUSE
SET VAR9=!string:~%TC%!
REM ECHO VAR9:!var9! & PAUSE
SET VAR10=!string:~%Desc%!
REM ECHO VAR10:!var10! & PAUSE
SET VAR11=!string:~%Amt%!

ECHO !VAR1!,!VAR2!,!VAR3!,!VAR4!,!VAR5!,!VAR6!,!VAR7!,!VAR8!,!VAR9!,!VAR10!,!VAR11!>>%outputF%
ECHO.>>%outputF%
goto :eof

:END
SET ETIME=%TIME%
ECHO.
ECHO ST:!STIME! ET:!ETIME!
PAUSE
rem EXIT
Anyone wanna share info on VBscript, I'm betting it would be quicker using that as a language. This works but takes a while.


Example data (input) Reason behind doing the output this way is I have to keep empty data cells.
Code: [Select]...

INST:012345678 INPUT TEST Transaction Audit Journal Page: 2
Program EOD UNIT: 0123456789-09991 WORKSTATION DEVICE ID: 999999999 Date: 12/04/09 03:28:39
Batch# Trans# Old/New ISN AUX RT Number W.AUX Account TC Description Amount

111111 222222 Original 0011111111 3333-3333 1234567 On-UsCredit 200.00
111111 222222 ChangedTo 0011111111 3333-3333 1234567 On-UsCredit 200.00

111111 222222 Original 0022222222 555555555 2222-2222 On-UsCredit 0.00
111111 222222 ChangedTo 0022222222 555555555 2222-2222 On-UsCredit 28.50

111111 222222 Original 0033333333 1111*1111 11111111111 7626 ransitDebit 8.00
111111 222222 ChangedTo 0033333333 111111111 11111111111 7626 ransitDebit 8.00
OUTPUT
Code: [Select]...
INST:012345678 INPUT TEST Transaction Audit Journal Page: 2
Program EOD UNIT: 0123456789-09991 WORKSTATION DEVICE ID: 999999999 Date: 12/04/09 03:28:39
Batch#, Trans#, Old/New, ISN, AUX, RT Number, W.AUX, Account, TC, Description, Amount

111111, 222222, Original ,0011111111, , 3333-3333, , 1234567 , , On-UsCredit , 200.00
111111, 222222, ChangedTo ,0011111111, , 3333-3333, , 1234567 , , On-UsCredit , 200.00

111111, 222222, Original ,0022222222, , 555555555, , 2222-2222 , , On-UsCredit , 0.00
111111, 222222, ChangedTo ,0022222222, , 555555555, , 2222-2222 , , On-UsCredit , 28.50

111111, 222222, Original ,0033333333, , 1111*1111, , 11111111111 , 7626 , ransitDebit , 8.00
111111, 222222, ChangedTo ,0033333333, , 111111111, , 11111111111 , 7626 , ransitDebit , 8.00

Code: [Select][quote author=gh0std0g74 link=topic=96085.msg650435#msg650435 date=1259917018]
since you have now put in some effort, here's an alternative vbscript, plus padding of zeroes. You can pad your data before importing to excel

[code]
Set objFS=CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
strFile = objArgs(0)
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
....[/quote]

So switching to VBS.... Seems to work much quicker, is there a way to get rid of the invalid characters when importing into excel either through excel or preferably through the script itself. By invalid character I mean it is displays as a ? in a box. I also get an error about input past end of file, not sure how to handle that error Code 800A003E. Any help is appreciated.
Code: [Select]Option Explicit

Dim objOutputFile, s, objFS, objArgs, strInputFile, objFile, strOutputfile
Dim objFSO, boolMatchCaseSensitive, objInputFile


Set objFS=CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
strInputFile = objArgs(0)
Set objFile = objFS.OpenTextFile(strInputFile)

strOutputfile = "outputVBS.csv"
If objFS.fileExists(strOutputfile) Then objFS.DeleteFile(StrOutputfile)



Set objFSO = CreateObject("Scripting.FilesystemObject")
Const intForReading = 1
Const intForWriting = 2
Const intForAppending = 8
Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)



Set objOutputFile = objFS.OpenTextFile(strOutputFile,intForAppending, True)

Do Until objFile.AtEndOfStream
s = objInputFile.ReadLine()
If InStr(s,"INST:") > 0 Then ObjOutputfile.WriteLine s
If InStr(s,"Program") > 0 Then ObjOutputfile.WriteLine s
If InStr(s,"Batch#") > 0 Then ObjOutputfile.WriteLine "Batch#, Trans#, Old/New, ISN, AUX, RT Number, W.AUX, Account, TC, Description, Amount"
If InStr(s,".....") > 0 Then ObjOutputfile.WriteLine s

'BATCH 'TRANS 'OLDNEW 'ISN 'AUX 'RT 'WAUX 'Acct 'TC 'Desc 'Amt
If InStr (s,"Original") > 0 Then ObjOutputfile.WriteLine vbcrlf & Mid(s,1,7) & "," & Mid(s,7,8) & "," & Mid(s,15,9) & "," & Mid(s,25,10) & "," & Mid(s,35,9) & "," & Mid(s,44,11) & "," & Mid(s,54,9) & "," & Mid(s,63,18) & "," & Mid(s,80,8) & "," & Mid(s,87,13) & "," & Mid(s,99,14)
If InStr (s,"ChangedTo") > 0 Then ObjOutputfile.WriteLine Mid(s,1,7) & "," & Mid(s,7,8) & "," & Mid(s,15,10) & "," & Mid(s,25,11) & "," & Mid(s,36,10) & "," & Mid(s,45,10) & "," & Mid(s,55,9) & "," & Mid(s,63,18) & "," & Mid(s,80,8) & "," & Mid(s,88,13) & "," & Mid(s,100,14)


Loop
objOutputFile.Close
Set objOutputFile = Nothing
Set objFS = Nothing
WScript.Quit()[/code]how does your output file look like after you run the script Pretty much the same as above ... spacing is different but still works as a Comma delimited file when importing into excel
Code: [Select]INST:012345678 INPUT TEST Transaction Audit Journal Page: 2
Program EOD UNIT: 0123456789-09991 WORKSTATION DEVICE ID: 999999999 Date: 12/04/09 03:28:39
Batch#, Trans#, Old/New, ISN, AUX, RT Number, W.AUX, Account, TC, Description, Amount

111111 , 222222, Original,0011111111, , 3333-3333 , , 1234567 , , On-UsCredit , 200.00
111111 , 222222, ChangedTo, 0011111111, , 3333-3333, , 1234567 , , On-UsCredit , 200.00

111111 , 222222, Original,0022222222, , 555555555 , , 2222-2222 , , On-UsCredit , 0.00
111111 , 222222, ChangedTo, 0022222222, , 555555555, , 2222-2222 , , On-UsCredit , 28.50

111111 , 222222, Original,0033333333, , 1111*1111 , , 11111111111 , 7626 , ransitDebit , 8.00
111111 , 222222, ChangedTo, 0033333333, , 111111111, , 11111111111 , 7626, ransitDebit , 8.00

imported into excel
Code: [Select]"INST:012345678 INPUT TEST Transaction Audit Journal Page: 2"
Program EOD UNIT: 0123456789-09991 WORKSTATION DEVICE ID: 999999999 Date: 12/04/09 03:28:39
Batch# " Trans#" Old/New " ISN" " AUX" " RT Number" " W.AUX" " Account" " TC" " Description" " Amount"

"111111 " " 222222" " Original" 11111111 " " " 3333-3333 " " " " 1234567 " " " " On-UsCredit " " 200.00"
"111111 " " 222222" " ChangedTo" " 0011111111" " " " 3333-3333" " " " 1234567 " " " " On-UsCredit " " 200.00"

"111111 " " 222222" " Original" 22222222 " " " 555555555 " " " " 2222-2222 " " " " On-UsCredit " " 0.00"
"111111 " " 222222" " ChangedTo" " 0022222222" " " " 555555555" " " " 2222-2222 " " " " On-UsCredit " " 28.50"

"111111 " " 222222" " Original" 33333333 " " " 1111*1111 " " " " 11111111111 " " 7626 " " ransitDebit " " 8.00"
"111111 " " 222222" " ChangedTo" " 0033333333" " " " 111111111" " " " 11111111111 " " 7626" " ransitDebit " " 8.00"

I also attached the outputfile if you care to look at the invalid characters, now that I see the copy paste I'm assuming it is the " symbol, is that the tabs... extra spaces? Is there an operation with the way I'm outputting the data to remove extra white SPACE?



[Saving space, attachment deleted by admin]After lots of research I found the Trim command the replace command. I am using the replace command in a for loop to look for the TAB and replacing with "" (nothing) and doing the same for space thus removing the extra unneeded spaces.

Thanks always for the direction and assistance.