How to freely select one column based on another column data?

[goodywp]

Hi All,

I have a following code to do the job as select from any other column data based upon first column data via a criteria (profile as which column):

Code: Select all

call C:\auto_pkg_build\Scripts\scheme_replace\get_src.cmd
set prof=%prof: =%

if "%prof%"=="QA_Infra"   goto QA_Infra
if "%prof%"=="NAR_Infra"  goto NAR_Infra
if "%prof%"=="US_Prod"    goto US_Prod
if "%prof%"=="MY_Prof"    goto MY_Prof

:QA_Infra
(for /F "tokens=2 delims=, " %%I IN ('findstr /B /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt
goto end

:NAR_Infra
(for /F "tokens=3 delims=, " %%I IN ('findstr /B /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt
goto end

:MY_Prof
(for /F "tokens=4 delims=, " %%I IN ('findstr /B /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt
goto end

:US_Prod
(for /F "tokens=5 delims=, " %%I IN ('findstr /B /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt
goto end

:end

Here is the sample of echosch_1.txt
T501-08680-0101
T501-08699-0101
O501-08690-0101

And here is the profile_scheme.txt

Mockup , QA_Infra , NAR_Infra , MY_Prof , US_Prod
T501-08680-0100, T501-08815-0100, T501-08665-0100, T501-01110-0100, T501-04440-0100
T501-08680-0101, T501-08815-0101, T501-08665-0101, T501-01110-0101, T501-04440-0101
T501-08680-0102, T501-08815-0102, T501-08665-0102, T501-01110-0102, T501-04440-0102
T501-08680-0103, T501-08815-0103, T501-08665-0103, T501-01110-0103, T501-04440-0103
no_gen, no_gen, no_gen, no_gen, no_gen

T501-08699-0100, T501-08700-0100, T501-08681-0100, T501-02220-0100, T501-05550-0100
T501-08699-0101, T501-08700-0101, T501-08681-0101, T501-02220-0101, T501-05550-0101
no_sl, no_sl, no_sl, no_sl, no_sl

O501-08690-0100, T501-08788-0100, T501-08658-0100, T501-03330-0100, T501-06660-0100
O501-08690-0101, T501-08788-0101, T501-08658-0101, T501-03330-0101, T501-06660-0101
O501-08690-0102, T501-08788-0102, T501-08658-0102, T501-03330-0102, T501-06660-0102
no_fk, no_fk, no_fk, no_fk. no_fk

So if "%prof%"=="QA_Infra", then it will select
T501-08815-0101
T501-08700-0101
T501-08788-0101
as output to sch_pack_1.txt

And it works fine for different profile. But there is a limitation for this. I only based upon the first column then select the rest column.

Now the new requirement is asking more flexible. It is not only based upon the first column. It can be any column (for the source data) then select the relative column based upon the profile as output...

Say my new echosch_1.txt is as below
T501-04440-0103
T501-05550-0101
T501-06660-0102

if "%prof%"=="QA_Infra", then it will select
T501-08815-0103
T501-08700-0101
T501-08788-0102

Any thoughts?

Thanks

[Aacini]

If the new requirement is that the source data can be at any column, then it is equivalent to say that not matters in which column is it, right? So you just need to eliminate the /B switch from FINDSTR command... This should work as long as there is not duplicate data in more than one column.

The Batch file below is equivalent to your original code (with FINDSTR /B switch removed), but simpler....

Code: Select all

@echo off
setlocal EnableDelayedExpansion

REM call C:\auto_pkg_build\Scripts\scheme_replace\get_src.cmd
REM set prof=%prof: =%

REM For test purposes:
SET "prof=QA_Infra"

set /P "header=" < profile_scheme.txt
set "i=0"
for %%a in (%header%) do (
   set /A i+=1
   if "%%a" equ "%prof%" set /A token=i
)

(for /F "tokens=%token% delims=, " %%I IN ('findstr /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt

Antonio

[goodywp]

Hi Aacini,

Thanks for the code. I tried but complain this:
delims=, " was unexpected at this time.

Any reasons?

Thanks

[goodywp]

OK figure it out

Just remove the double-quoted around

set prof=QA_Infra

and it works fine now.

Thanks again!

goodywp

[Squashman]

OK figure it out

Just remove the double-quoted around

set prof=QA_Infra

and it works fine now.

Thanks again!

goodywp

Doubtful. Quotes around the set statement should never have any implications on that string.

[goodywp]

Ohh.... but that was how it works for now. And I have no further idea...

[Squashman]

Ohh.... but that was how it works for now. And I have no further idea...

This error

Code: Select all

delims=, " was unexpected at this time.

Has nothing to do with this.

Code: Select all

set "prof=QA_Infra"

[goodywp]

I had some issue for this code. It was OK when the column is not that much. But now after adding more column and getting some weird issue, for some column I got the error back again

Here is my config file

profile_scheme.txt file as below, originally I had , between two column, but after remove it still work..
MOCKUP_DL QA_DL US_PROD_DL TD_DL GPC_DL ELAVON_DL DESJAR_DL CA_PROD_DL
T501-08680-0100 T501-08815-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0101
T501-08680-0101 T501-08815-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0102
T501-08680-0102 T501-08815-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0103
T501-08680-0103 T501-08815-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0104

0501-08690-0100 0501-08783-0100 0501-08658-0100 0501-08658-0100 0501-08658-0100 0501-08658-0100 0501-08658-0100 0501-08658-0101
0501-08690-0102 0501-08783-0102 0501-08658-0102 0501-08658-0102 0501-08658-0102 0501-08658-0102 0501-08658-0102 0501-08658-0103

my source column is QA_DL then target TD_DL, it works fine but
when I select source column is QA_DL then target CA_PROD_DL I got an error as

The code I used for as

Code: Select all

@ECHO OFF

cd C:\auto_pkg_build\Scripts\scheme_replace\pkg_data\temp
echo 00
if exist profile_scheme.txt (del profile_scheme.txt)
echo 11
copy C:\auto_pkg_build\Sources\Source_schemes\profile_scheme.txt .
echo 22
if exist sch_pack_1.txt (del sch_pack_1.txt)
echo 33
call C:\auto_pkg_build\Scripts\scheme_replace\get_src.cmd
echo 44
set "profname=%profname: =%"
echo 55
set /P "header=" < profile_scheme.txt
echo 66
set "i=0"
echo 77
for %%a in (%header%) do (
echo 88
   set /A i+=1
echo 99
   if "%%a" equ "%profname%" set /A token=i
echo aa
)
echo bb
(for /F "tokens=%token% delims= " %%I IN ('findstr /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt

after line bb above, it shows error
delims=, " was unexpected at this time.
or
delims= " was unexpected at this time. depend on the code and the above config file

But when I switch two column name between TD_DL and CAN_PROD_DL then try the above code again,
got the same result that means TD_DL works but CAN_PROD_DL not work and also got that error message after line bb

what is the issue here??? It looks like not making sense to me it is name of head dependent...
Thanks

[goodywp]

If the new requirement is that the source data can be at any column, then it is equivalent to say that not matters in which column is it, right? So you just need to eliminate the /B switch from FINDSTR command... This should work as long as there is not duplicate data in more than one column.

The Batch file below is equivalent to your original code (with FINDSTR /B switch removed), but simpler....

Code: Select all

@echo off
setlocal EnableDelayedExpansion

REM call C:\auto_pkg_build\Scripts\scheme_replace\get_src.cmd
REM set prof=%prof: =%

REM For test purposes:
SET "prof=QA_Infra"

set /P "header=" < profile_scheme.txt
set "i=0"
for %%a in (%header%) do (
   set /A i+=1
   if "%%a" equ "%prof%" set /A token=i
)

(for /F "tokens=%token% delims=, " %%I IN ('findstr /g:echosch_1.txt profile_scheme.txt') do echo %%I)>sch_pack_1.txt

Antonio

Hi Antonio,

Do you have any idea why this happen as the latest post showing some issue

Thanks

[Squashman]

after line bb above, it shows error
delims=, " was unexpected at this time.
or
delims= " was unexpected at this time. depend on the code and the above config file

You are absolutely 100% positive that is the exact error, character for character, you are receiving?

The only way you would get that error is if the token variable was set to a special character or delim character like a comma. The code you are showing us would not do that. So I assume it is obfuscated.

I can run through all iterations of your profile scheme input file without a single error using your same logic for increasing the value of the token variable.

Code: Select all

@ECHO OFF
type profile_scheme.txt
set /P "header="<profile_scheme.txt

FOR %%G IN (%header%) DO CALL :DOME %%G

GOTO :EOF
:DOME
set "i=0"
set "profname=%1"
for %%a in (%header%) do (
   set /A i+=1
   if "%%a" equ "%profname%" set /A token=i
)
for /F "tokens=%token% delims= " %%I IN (profile_scheme.txt) do echo %%I

Output

Code: Select all

MOCKUP_DL QA_DL US_PROD_DL TD_DL GPC_DL ELAVON_DL DESJAR_DL CA_PROD_DL
T501-08680-0100 T501-08815-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0100 T501-08665-0101
T501-08680-0101 T501-08815-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0101 T501-08665-0102
T501-08680-0102 T501-08815-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0102 T501-08665-0103
T501-08680-0103 T501-08815-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0103 T501-08665-0104
MOCKUP_DL
T501-08680-0100
T501-08680-0101
T501-08680-0102
T501-08680-0103
QA_DL
T501-08815-0100
T501-08815-0101
T501-08815-0102
T501-08815-0103
US_PROD_DL
T501-08665-0100
T501-08665-0101
T501-08665-0102
T501-08665-0103
TD_DL
T501-08665-0100
T501-08665-0101
T501-08665-0102
T501-08665-0103
GPC_DL
T501-08665-0100
T501-08665-0101
T501-08665-0102
T501-08665-0103
ELAVON_DL
T501-08665-0100
T501-08665-0101
T501-08665-0102
T501-08665-0103
DESJAR_DL
T501-08665-0100
T501-08665-0101
T501-08665-0102
T501-08665-0103
CA_PROD_DL
T501-08665-0101
T501-08665-0102
T501-08665-0103
T501-08665-0104

[goodywp]

Really appreciated your effort!!
My original requirement was this as below

if I have two scheme pack under QA_DL,
T501-08815-0103
0501-08783-0102
if my target is MOCKUP_DL, then it will find
T501-08680-0103
0501-08690-0102

if my target is TD_DL, then it will pop up
T501-08665-0103
0501-08658-0102

and so on...
Now the issue I raised here if my target is CA_PROD_DL, it got error as above....
Thanks again

[Squashman]

Now the issue I raised here if my target is CA_PROD_DL, it got error as above....
Thanks again

The input example and code example you have given does not produce that error. PERIOD!

[goodywp]

Now the issue I raised here if my target is CA_PROD_DL, it got error as above....
Thanks again

The input example and code example you have given does not produce that error. PERIOD!

strange on my PC keep giving me error but if I switch to TD_DL or US_PROD_DL they both OK....

[Squashman]

Now the issue I raised here if my target is CA_PROD_DL, it got error as above....
Thanks again

The input example and code example you have given does not produce that error. PERIOD!

strange on my PC keep giving me error but if I switch to TD_DL or US_PROD_DL they both OK....

Create a zip file with your batch file and all your input files and attach the zip file to a post.

[goodywp]

The input example and code example you have given does not produce that error. PERIOD!

strange on my PC keep giving me error but if I switch to TD_DL or US_PROD_DL they both OK....

Create a zip file with your batch file and all your input files and attach the zip file to a post.

Tried another input file and works and could be the input file issue....really strange... nothing related to code...