Remove leading quotation | Remove leading zeroes from a string of numeric characters | Automated Script

[Mothra]

Howdy! Brand spanking new to BATCH DOS programming and I have been trying to do this for 3 days now and I am fairly deep down a rabbit hole
I have been tasked with creating a batch file that will remove the leading zeroes from strings of data that are exported out of our software into a .txt file. I would like the script to run every day AT 6:30 pm.
An Example of the data that I would like the batch file to address: (The actual file is 98 KB of similar output)

"000000095955","LastName",0.00,"","","","","","","","","",""
"000000092627","LastName",5.43,"","","","","","","","","",""
"000000099218","LastName",1.98,"","","","","","","","","",""
"0000000091042","LastName",0.00,"","","","","","","","","",""
"000000099781","LastName",1.87,"","","","","","","","","",""
"000000082889","LastName",0.32,"","","","","","","","","",""

First: I have to remove / escape the leading quotation character in order to access the following string of zeroes (http://www.dostips.com/DtTipsStringManipulation.php#Snippets.RemoveBothEnds but only remove the first character)

Second: The string length of zeroes that precede the non-zero integers in the string vary from 6-8 zeroes so I would like to trim zeroes up to the non-zero integer (trim up to 'not zero' in the first string of 10 characters) and leave the following characters alone

Third: Concatenate the leading quotation back onto the beginning of the edited (no leading zeroes) string FOR every line

Fourth: Output the edited data into a new .txt file in the same directory as the executing script

Fifth: Ideally, I make use of the AT functionality in BATCH and have the script execute on the .txt file every day at 6:30 pm

Sixth: If the directory in question has multiple .txt files inside of it; would it be possible to have the BATCH file look through all the .txt files in the directory and locate the particular .txt file that begins with a string of six zeroes ("000000") and have the script only execute on this file?

Thank you for any and all help; it is extremely appreciated and I really value the input of this community
dostips has been a godsend these past few days

[Squashman]

Read the file with a FOR /F command. The FOR command modifiers take care of removing surrounding quotes. Also you will not use AT. Create a Windows Scheduled task.

[aGerman]

First: I have to remove / escape the leading quotation character in order to access the following string of zeroes (http://www.dostips.com/DtTipsStringManipulation.php#Snippets.RemoveBothEnds but only remove the first character)

Second: The string length of zeroes that precede the non-zero integers in the string vary from 6-8 zeroes so I would like to trim zeroes up to the non-zero integer (trim up to 'not zero' in the first string of 10 characters) and leave the following characters alone

Third: Concatenate the leading quotation back onto the beginning of the edited (no leading zeroes) string FOR every line

Fourth: Output the edited data into a new .txt file in the same directory as the executing script

...

Sixth: If the directory in question has multiple .txt files inside of it; would it be possible to have the BATCH file look through all the .txt files in the directory and locate the particular .txt file that begins with a string of six zeroes ("000000") and have the script only execute on this file?

Altering CSV data using Batch isn't safe. Try carefully

Code: Select all

@echo off &setlocal
for %%i in (*.txt) do (
  set "filename=%%~ni"
  set "file=%%~i"
  <"%%~i" set /p "firstline="
  call :proc_file
)
pause
exit /b

:proc_file
setlocal EnableDelayedExpansion
if "!firstline:~,7!" neq ^""000000" endlocal&exit /b
endlocal
>"%filename%_changed.txt" (
  for /f "usebackq tokens=1* delims=," %%i in ("%file%") do (
    for /f "tokens=* delims=0" %%k in ("%%~i") do (
      echo "%%k",%%j
    )
  )
)
exit /b

Fifth: Ideally, I make use of the AT functionality in BATCH and have the script execute on the .txt file every day at 6:30 pm

AT is obsolete. I agree with Squashman - either create a scheduled task by hand or use SCHTASKS.

Steffen

[Compo]

The following example assumes a known text file name and all data in the first field of every row matching the format shown:

Code: Select all

@Echo Off
If Exist "filein.txt" (For /F "UseBackQTokens=1*Delims=," %%A In ("filein.txt"
   ) Do Call :Sub %%A&Call Echo "%%ERRORLEVEL%%",%%B)>"fileout.txt"
Exit/B

:Sub
Exit/B %~1

It should take care of First:, Second:, Third: & Fourth:
Adjust your input file name, (twice on line 2), and output file name, (end of line 3), as necessary

[dbenham]

Not sure if this is a potential issue with your data, but aGerman's code will fail to print a line if the leading number is all zeros.

The following minor change to the FOR /F loops will properly handle all zeros. A FOR /F returns an error condition if it does not have any iterations.

Code: Select all

>"%filename%_changed.txt" (
  for /f "usebackq tokens=1* delims=," %%i in ("%file%") do (
    for /f "tokens=* delims=0" %%k in ("%%~i") do echo "%%k",%%j
  ) || echo "0",%%j
)

Dave Benham

[aGerman]

It doesn't harm to make an unsafe code a little more secure I didn't think much about it since Mothra wrote that only 6-8 leading zeros are possible (but also that the first string consists of 10 characters while the example has up to 13 ).

Steffen

[Mothra]

Read the file with a FOR /F command. The FOR command modifiers take care of removing surrounding quotes. Also you will not use AT. Create a Windows Scheduled task.

Bless you!
I was wondering if a scheduled task would be my best bet

[Mothra]

I could kiss you all!
Over the past week, in order to accomplish this task, I have written a C# script, a VB script, and a Powershell script that all did the same thing; only to learn that the server that the scripts were written for is operating on Windows 2003; making every one of my aforementioned scripts irrelevant. I have been frantically trying to get a basic understanding of DOS (primarily using DOSTIPS) but have been having trouble especially as the deadline for this script approaches.

BUT!

aGerman, you are an angel; you have saved me and I can't thank you enough for your DOS magic and dbenham, your robustness fix is wonderful! Compo thank you for your contribution, a wonderful solution as well.

Thank you for the advice with the windows task as well
Good lord there are no words to properly express my appreciation for all of you and your wisdom.
Thank you thank you thank you a million times thank you

[Mothra]

Altering CSV data using Batch isn't safe. Try carefully

Code: Select all

@echo off &setlocal
for %%i in (*.txt) do (
  set "filename=%%~ni"
  set "file=%%~i"
  <"%%~i" set /p "firstline="
  call :proc_file
)
pause
exit /b

:proc_file
setlocal EnableDelayedExpansion
if "!firstline:~,7!" neq ^""000000" endlocal&exit /b
endlocal
>"%filename%_changed.txt" (
  for /f "usebackq tokens=1* delims=," %%i in ("%file%") do (
    for /f "tokens=* delims=0" %%k in ("%%~i") do (
      echo "%%k",%%j
    )
  )
)
exit /b

Fifth: Ideally, I make use of the AT functionality in BATCH and have the script execute on the .txt file every day at 6:30 pm

AT is obsolete. I agree with Squashman - either create a scheduled task by hand or use SCHTASKS.

Steffen

Just set this up as a scheduled task on a Windows 2003 Server and it worked like a dream Bless your pea pickin heart!
I went through it and tried to understand it as best I could, adding some REMs for documentation purposes, I think I have a grasp on most of it, but I'm a little lost on the last FOR in the subroutine; would you mind if I asked for some clarification on that in regards to what is happening within the code?
Again, thank you so much for your time and expertise

Code: Select all

@echo off &setlocal

REM issuing a setlocal command so that all the variables in this file are available
REM using a for loop to conditionally perform a command on all lines in the specified file
for %%i in (QMF_SIS.txt) do (
  REM expanding a parameter of the BAT itself to a file name and assigning it to a variable
  set "filename=%%~ni"
  REM setting the saved lines from the for loop to a variable 'file'
  set "file=%%~i"
  REM setting the contents inside of %%i to the the firstline, essentially prompting the program to take input and feed itself the text in %%i
  <"%%~i" set /p "firstline="
  REM calling a subroutine (string manipulation) to be called on the text that is fed to the batch from %%i
  call :proc_file
)
exit /b

REM where the string manipulation subroutine occurs
:proc_file
REM setting EnableDelayedExpansion so that the value of a variable read in a for loop withholds its value when being used later in the script
setlocal EnableDelayedExpansion
REM thanks to EDE we can access the firstline variable by placing a ! in front of it
REM extracting part of a variable (substring) if the firstline of the string (7characters in) does not equal (neq) ""000000" exit the script and return control to the command processor or code immediately
if "!firstline:~,7!" neq ^""000000" endlocal&exit /b
endlocal
REM redirecting the output of the following string manipulations to a new txt file ("QMF_SIS_changed.txt)
>"QMF_SIS_changed.txt" (
    REM at the start of the first for loop, 'for every line in the file that starts with a "' in the %file%
    for /f "usebackq tokens=1* delims=," %%i in ("%file%") do (
    REM second for loop, goes through all the 'tokens' in the %%i(file) now without starting " in the line, and sets the delimiting character to 0 
    for /f "tokens=* delims=0" %%k in ("%%~i") do (
      echo "%%k",%%j
    )
  )
)
exit /b

[aGerman]

Code: Select all

@echo off &setlocal
REM issuing a setlocal command so that all the variables lose their scope after the script file ends

REM using a for loop to process either the named file or (if *.txt is used) process all text files in the directory one by one
for %%i in (QMF_SIS.txt) do (
  REM expanding the FOR variable %%i to the file name without extension and assigning it to a variable
  set "filename=%%~ni"
  REM setting the file name (including the file extension) to a variable 'file'
  set "file=%%~i"
  REM reading the first line of the file that was assigned to %%i
  <"%%~i" set /p "firstline="
  REM calling a subroutine that will process the content of the file
  call :proc_file
)
exit /b

REM where the string manipulation subroutine occurs
:proc_file
REM setting EnableDelayedExpansion to safely process the variable set
REM to apply this setting you have to replace surrounding percent signs of variables with surrounding exclamation marks
setlocal EnableDelayedExpansion
REM extracting part of a variable (substring) with the first 7 characters of the first line and if they do not equal (neq) ""000000" exit the suroutine
REM and return control to the main code where the subroutine was called
if "!firstline:~,7!" neq ^""000000" endlocal&exit /b
endlocal
REM redirecting the output of the following string manipulations to a new txt file ("QMF_SIS_changed.txt)
>"QMF_SIS_changed.txt" (
    REM at the start of the first for loop, 
    REM for every non-empty line in the file create two substrings delimited by the first found comma
    REM assign the first substring to %%i and the second substring (rest of the line) to %%j
    for /f "usebackq tokens=1* delims=," %%i in ("%file%") do (
    REM The tilde (~) in %%~i removes surrounding quotation marks
    REM The options "tokens=* delims=0" remove all leading zeros
    REM The resulting string will be assigned to %%k
    for /f "tokens=* delims=0" %%k in ("%%~i") do (
      REM Surround %%k with quotation marks and output it along with a comma and the rest of the line
      echo "%%k",%%j
    )
  )
)
exit /b

Steffen

[Mothra]

Thank you so much for the explanation; your .bat/explanation + how long I've been poring over DOS tips, HELP menu, documentation, and other examples have given me a much clearer understanding of this somewhat esoteric tool. You are an angel, a saint, a wizard! I am in your debt and if ever you need anything!!! please, hmu and I will come to the call of 'A German'.
Honestly though; I could not have done this without you (in the timeline I had) and I want you to know how appreciative I am for your (and everyone on this forum) time, experience, and knowledge.

Wish you all the best in every endeavor that you undertake for the rest of your lives.