capturing column data from .txt file and comparing the values

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

capturing column data from .txt file and comparing the values

#1 Post by Mothra » 13 Dec 2017 12:13

Howdy!
At my work I am having a somewhat particular issue; we work with map data and some of the current map data is incorrect/inconsistent. I have a Mapfil.txt file that contains all of the information related to a particular segment. A segment is a line (representing a road) that goes from (x1, x2) and (y1, y2). A road going left -> to right.
segmentExample.JPG
segmentExample.JPG (23.25 KiB) Viewed 6915 times
The segments on the left are going in the right direction and the address #'ing is correct, the segments on the right are going in the wrong direction but the address #'ing is correct. What is incorrect are the 'RH,LH, RL, LL' field values on each node. These values are individual and separate from the address values; these 'RH, LH...' values are used in order to determine the direction of the segment. What I am trying to do is compare the values of LL to RH, and if RH is > LL, place the value of RH into LL variable... and do the same with the other fields, if LH < RL then RL = LH & LH = RL

Make the value in Left Low be Right High | Make the value in Left high be Right Low
LL ===> RH LH ===> RL
Make the value in Right low be Left High | Make the value in Right High be Left Low
RH ===> LH RH ===> LL

The text file that I am to work with is formatted like so... but the data I will be working with will have columns that extend into the thousands. The LH, LL, RH, LH columns have room for 9 characters and they will very so I have to account for that when going through and capturing the data
dataExample.JPG
dataExample.JPG (54.99 KiB) Viewed 6915 times
My plan of attack goes something like:
1. read through all of the data and capture each specific column into its own variable
- 1169(LL) = LL
- 1141(LH)= LH
- 1168(RL) = RL
- 1148(RH)= RH

2. Once I have all of the data inside of their own variables, then do an if / else statement that compares the value of the LL to RH and if LL is > RH then I want to assign LL the value of RH, essentially just switching the column values of Left Low to be the new values of Right High.

3. Do this if / else statement to every value and have it spit out the results into a text file.

Is this possible with a DOS BATCH file?
if this is possible, how best to separate each column into its own specific variable in order to run an if / else?

I did my best to describe this issue in full, and I know it is kind of a weird / obnoxious one, but any and all help/guidance is appreciated thoroughly :) thank you!

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: capturing column data from .txt file and comparing the values

#2 Post by aGerman » 13 Dec 2017 14:32

Please put the text file in a ZIP file and upload it. The picture doesn't say anything about how the values are separated. Could be tabs or fixed positioning using spaces, etc.

Steffen

Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

Re: capturing column data from .txt file and comparing the values

#3 Post by Mothra » 13 Dec 2017 15:53

Most Certainly!
So sorry for the oversight on my behalf!
I hope this helps :)
Thank you for saying please! That was so kind!

Attached is the Mapfil file
thank you so much for replying
Attachments
Mapfil.txt.zip
(386 Bytes) Downloaded 249 times

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: capturing column data from .txt file and comparing the values

#4 Post by aGerman » 13 Dec 2017 17:38

The data fields in your file have fixed lengths which isn't that bad in this case.

Code: Select all

@echo off &setlocal
set "infile=Mapfil.txt.asc"
set "outfile=Mapfile_corrected.txt.asc"

>"%outfile%" (
  for /f "delims=" %%i in ('type "%infile%"') do (
    set "line=%%i"
    setlocal EnableDelayedExpansion
    set "sBegin=!line:~,74!"
    set "sLL=!line:~74,10!"
    set "sLH=!line:~84,10!"
    set "sRL=!line:~94,10!"
    set "sRH=!line:~104,10!"
    set "sEnd=!line:~114!"
    set /a "nLL=sLL, nLH=sLH, nRL=sRL, nRH=sRH"
    if !nLL! gtr !nRH! (
      set "sTmp=!sLL!"
      set "sLL=!sRH!"
      set "sRH=!sTmp!"
    )
    if !nLH! lss !nRL! (
      set "sTmp=!sLH!"
      set "sLH=!sRL!"
      set "sRL=!sTmp!"
    )
    echo !sBegin!!sLL!!sLH!!sRL!!sRH!!sEnd!
    endlocal
  )
)
I removed the explanation line in your file. Also I assume that your real file doesn't have any head line. Just get back to this thread if the real data differ from your example.
Mapfil.txt.zip
(747 Bytes) Downloaded 265 times
Mothra wrote:
13 Dec 2017 15:53
Thank you for saying please! That was so kind!
Welcome! Germans are said to sound more military than pollite. Sometimes I remember that and try to make it better :lol:

Steffen

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: capturing column data from .txt file and comparing the values

#5 Post by Squashman » 13 Dec 2017 18:32

aGerman wrote:
13 Dec 2017 17:38
Welcome! Germans are said to sound more military than pollite. Sometimes I remember that and try to make it better :lol:
Steffen
What about Austrians over there? My family came from Austria to Wisconsin in the late 1800's. My Great Grandparents. Never met them. They were deceased by the time I was born but on my mother's side, My Great Grandparents were from Belgium and emigrated over here in the late 1800's.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: capturing column data from .txt file and comparing the values

#6 Post by aGerman » 13 Dec 2017 20:02

That reminds me of my conversations with foxidrive who had German roots.

The word 'please' is well-known in Germany :wink: Of course in Austria and Belgium, too. It's an element of the courtesy form. But in German language it's more common to say 'please do this or that' rather than terms like 'would you mind ...' or 'would you please ...' which contain an underlying 'pardon me'. If Germans say 'please' then it's mostly not a 'sorry for bothering you'. It still corroborates your will.

Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

Re: capturing column data from .txt file and comparing the values

#7 Post by Mothra » 14 Dec 2017 10:01

aGerman wrote:
13 Dec 2017 17:38
The data fields in your file have fixed lengths which isn't that bad in this case.

Code: Select all

@echo off &setlocal
set "infile=Mapfil.txt.asc"
set "outfile=Mapfile_corrected.txt.asc"

>"%outfile%" (
  for /f "delims=" %%i in ('type "%infile%"') do (
    set "line=%%i"
    setlocal EnableDelayedExpansion
    set "sBegin=!line:~,74!"
    set "sLL=!line:~74,10!"
    set "sLH=!line:~84,10!"
    set "sRL=!line:~94,10!"
    set "sRH=!line:~104,10!"
    set "sEnd=!line:~114!"
    set /a "nLL=sLL, nLH=sLH, nRL=sRL, nRH=sRH"
    if !nLL! gtr !nRH! (
      set "sTmp=!sLL!"
      set "sLL=!sRH!"
      set "sRH=!sTmp!"
    )
    if !nLH! lss !nRL! (
      set "sTmp=!sLH!"
      set "sLH=!sRL!"
      set "sRL=!sTmp!"
    )
    echo !sBegin!!sLL!!sLH!!sRL!!sRH!!sEnd!
    endlocal
  )
)
Welcome! Germans are said to sound more military than polite. Sometimes I remember that and try to make it better :lol:
Your solution is as impeccable as your manners :) and I am so very appreciative for both!
This works like a dream; I am always so blown away at how knowledgeable and prompt you are 'aGerman'. This has to be the best forum/community I have every had the pleasure of interacting with. Always makes my day to see a reply :)

I am currently going through the code you have provided and breaking it down line by line in order to better understand what is going on; again, thank you so much for the assistance. Words cannot express my gratitude.
But!
I have a bit of an addendum to my initial request! (if that is okay...)

I would also like to swap the values of X1 with the value of X2 as well as swap the values of Y2 with Y1, but the values in X2, X1, Y1, and Y2 will vary in length, but ultimately take up 9 characters at most. Is it possible to switch these values as well even though they could vary in length?

Happy Thursday! : )

Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

Re: capturing column data from .txt file and comparing the values

#8 Post by Mothra » 14 Dec 2017 10:19

Code: Select all

// Set options to ontrol the visiblity of environment variables in a bat file
@echo off &setlocal
// Set the file that the script is to use 
set "infile=Mapfil.asc"
// Set the file that the script will output to
set "outfile=Mapfile_corrected.asc"
// Routing all of the calculations the .bat performs to the "%outfile%" variable
>"%outfile%" (
// A loop command: against a set of files: conditionally perform a command against them
// "delims=" are the delimiting characters (default is a space)
// FOR /F processing of a text file consists of reading the file, one line of text at a time and then breaking the line up into individual items of data called 'tokens'. 
// The DO command is then executed with the parameter(s) set to the token(s) found.
  for /f "delims=" %%i in ('type "%infile%"') do (
// Setting the value/tokens of the line to the variable %%i
    set "line=%%i"
// This will cause variables within the file to be expanded at execution time rather than at parse time
// Expanding a variable mens you are replacing the %%i variable with its value
    setlocal EnableDelayedExpansion
// Telling the file where to start executing
    set "sBegin=!line:~,74!"
// Setting the Left Low at 74 'tokens' and reading for 10 more 'tokens'
    set "sLL=!line:~74,10!"
// Setting the Left High at 84 'tokens' and reading for 10 more 'tokens'
    set "sLH=!line:~84,10!"
// Setting the Right Low at 94 'tokens' and reading for 10 more 'tokens'
    set "sRL=!line:~94,10!"
// Setting the Right High at 104 'tokens' and reading for 10 more 'tokens' 
    set "sRH=!line:~104,10!"
// Telling the .bat when to stop reading the file line length
    set "sEnd=!line:~114!"
// Setting a variable to equal an expression
// Set notLeftLow to setLeftLow, set notLeftHigh to setLeftHigh...etc
    set /a "nLL=sLL, nLH=sLH, nRL=sRL, nRH=sRH"
// if notLeftLow is greater than notRightHigh
    if !nLL! gtr !nRH! (
// set "setTemporary to setLeftLow"
      set "sTmp=!sLL!"
// set "setLeftLow to setRightHigh
      set "sLL=!sRH!"
// set "setRightHigh to setTemporary"
      set "sRH=!sTmp!"
    )
// if notLeftHigh is less than notRightLow
    if !nLH! lss !nRL! (
// set "setTemporary to setLeftHigh"
      set "sTmp=!sLH!"
// set "setLowHigh to setRightLow"
      set "sLH=!sRL!"
set "setRightLow to setTemporary"
      set "sRL=!sTmp!"
    )
// Display the datato the screen
    echo !sBegin!!sLL!!sLH!!sRL!!sRH!!sEnd!
    endlocal
  )
)
Am I correct in my rundown of your gorgeous work aGerman? Am I understanding it correctly?

Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

Re: capturing column data from .txt file and comparing the values

#9 Post by Mothra » 14 Dec 2017 11:06

Yeehaw!
I believe I have accomplished:
Swap X1 with X2
Swap Y2 with Y1

Here is the code I have written by taking inspiration from your code!

Code: Select all

@echo off &setlocal
set "infile=Mapfil.asc"
set "outfile=Mapfile_corrected.asc"

>"%outfile%" (
  for /f "delims=" %%i in ('type "%infile%"') do (
    set "line=%%i"
    setlocal EnableDelayedExpansion
    set "sBegin=!line:~,30!"
    set "sX1=!line:~30, 11!"
    set "sY1=!line:~41, 11!"
    set "sX2=!line:~52, 11!"
    set "sY2=!line:~63, 11!"
    set "sLL=!line:~74,10!"
    set "sLH=!line:~84,10!"
    set "sRL=!line:~94,10!"
    set "sRH=!line:~104,10!"
    set "sEnd=!line:~114!"
    set /a "nLL=sLL, nLH=sLH, nRL=sRL, nRH=sRH, nX1=sX1, nY1=sY1, nX2=sX2, nY2=sY2"
    if !nLL! gtr !nLH! (
      set "sTmp=!sX2!"
      set "sX2=!sX1!"
      set "sX1=!sTmp!"
      set "sTmp=!sY1!"
      set "sY1=!sY2!"
      set "sY2=!sTmp!"
    )
    if !nLL! gtr !nRH! (
      set "sTmp=!sLL!"
      set "sLL=!sRH!"
      set "sRH=!sTmp!"
    )
    if !nLH! lss !nRL! (
      set "sTmp=!sLH!"
      set "sLH=!sRL!"
      set "sRL=!sTmp!"
    )
    echo !sBegin!!sX1!!sY1!!sX2!!sY2!!sLL!!sLH!!sRL!!sRH!!sEnd!
    endlocal
  )
)

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: capturing column data from .txt file and comparing the values

#10 Post by aGerman » 14 Dec 2017 11:13

Code: Select all

:: Turn off the prompt and make sur all variable lose their scope after the script ends
@echo off &setlocal
:: Set the file that the script is to use 
set "infile=Mapfil.asc"
:: Set the file that the script will output to
set "outfile=Mapfile_corrected.asc"
:: Redirect all outputs (of the ECHO command in this case) to the file specified by variable %outfile%
>"%outfile%" (
:: A loop command: process all lines in the file specified by variable %infile%
:: "delims=" are the delimiting characters, because nothing is following to the equal sign the default delimiters are neglected
:: Because of that the entire line is assigned to %%i
:: After the DO you find the commands that are applied for every line in %%i
  for /f "delims=" %%i in ('type "%infile%"') do (
::  assign the text of one line in %%i to variable line
    set "line=%%i"
::  This will cause variables within the file to be expanded at execution time rather than at parse time
::  Expanding a variable mens you are replacing the variable with its value
    setlocal EnableDelayedExpansion
::  Assign the first 74 characters of the line to variable sBegin
    set "sBegin=!line:~,74!"
::  Assign the following 10 characters after the 74th character of the line to variable sLL (same for the next variables)
    set "sLL=!line:~74,10!"
    set "sLH=!line:~84,10!"
    set "sRL=!line:~94,10!" 
    set "sRH=!line:~104,10!"
::  Assign the remaining characters after the 114th to variable sEnd
    set "sEnd=!line:~114!"
::  While I've chosen prefix s for string I now use prefix n for number
::  SET /A ignores leading spaces in the s... Variables and assigns only the numbers to the corresponding n... variables
    set /a "nLL=sLL, nLH=sLH, nRL=sRL, nRH=sRH"
::  if LeftLow is greater than RightHigh then swap the values
    if !nLL! gtr !nRH! (
::    assign LeftLow a temporary variable
      set "sTmp=!sLL!"
::    assign RightHigh to LeftLow
      set "sLL=!sRH!"
::    assign the temporary value to RightHigh
      set "sRH=!sTmp!"
    )
::  if LeftHigh is greater than RightLow then swap the values (like above)
    if !nLH! lss !nRL! (
      set "sTmp=!sLH!"
      set "sLH=!sRL!"
      set "sRL=!sTmp!"
    )
::  ECHO the processed variable contents (because of >"%outfile%" it will be redirected to the file)
    echo !sBegin!!sLL!!sLH!!sRL!!sRH!!sEnd!
    endlocal
  )
)
I would also like to swap the values of X1 with the value of X2 as well as swap the values of Y2 with Y1
No problem. But you should've specified the rules when to swap :wink:

Steffen

//EDIT Oops I didn't see you already answered you own question.

Mothra
Posts: 11
Joined: 03 Oct 2017 08:13

Re: capturing column data from .txt file and comparing the values

#11 Post by Mothra » 15 Dec 2017 12:45

Happy Friday aGerman! Hope your day has been really really good!
Again, I appreciate all your experience, knowledge, time, and expertise :)
I was so happy to go through such a comprehensive rundown of your code and be able to use what you provided to make a few additions of my own!
I have learned so much from you and I hope you know how much I appreciate it :)

Post Reply