Potential use case for JREPL?

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Potential use case for JREPL?

#1 Post by SIMMS7400 » 05 Apr 2018 15:18

Hi Folks -

I have a need to be able to transpose columns into rows. I have done this in the past with Dave's JREPL utility, but there is an added piece of complexity with a new business requirement.

Attached are files for reference but let me post a snippet here for reference.

Here is the data from my source "FX_Rates.csv" file:

Code: Select all

	
,AED,AMD,ARS,AUD,AZN,BAM,BBD,BDT,BGN,BHD,BRL,BYR,CAD,CHF,CLP,CNY,COP,CZK,DKK,DZD,EGP,EUR,GBP,GEL,HKD,HRK,HUF,IDR,ILS,INR,IRR,ISK,JOD,JPY,KES,KGS,KRW,KWD,KZT,LBP,LKR,LTL,MAD,MDL,MKD,MNT,MOP,MUR,MXN,MYR,NAD,NOK,NZD,OMR,PEN,PHP,PKR,PLN,QAR,RON,RSD,RUB,SAR,SDG,SEK,SGD,THB,TJS,TMT,TND,TRY,TWD,UAH,USD,UZS,VEF,VND,ZAR
EUR,4.448719778,582.6199973,24.48755091,1.554930925,2.044819293,1.935955322,2.422389889,100.897209,1.936458828,0.456594109,3.936984028,23636.36364,1.54749009,1.13730012,717.2018096,7.656832545,3461.12886,25.17598985,7.371862847,138.4953018,21.3556922,1,0.870811731,3.004391033,9.480020856,7.370450164,311.019666,16581.63265,4.215996529,78.5902125,45138.88889,122.2746854,0.858746732,130,123.0594472,82.50196735,1309.559786,0.363580204,386.1921454,1833.051325,187.5874807,3.418502249,11.19787068,20.18649213,60.97789786,2899.197145,9.760140786,39.84393437,22.82724168,4.750333437,14.19888266,9.528710003,1.674135888,0.466368064,3.939465566,63.33708484,133.8812165,4.131061419,4.420895054,4.614635352,116.1097863,68.17919591,4.542303521,35.11615343,9.963060039,1.603893563,38.13882532,10.68223315,4.239222592,2.910760994,4.622098966,35.39823009,32.70374433,1.238095238,9893.455099,815.0379467,27542.37288,14.19888266
GBP,5.108704468,669.0539148,28.12037327,1.785610908,2.3481761,2.22316174,2.781760746,115.8656981,2.223739943,0.524331602,4.521050751,27142.90909,1.777066197,1.306022966,823.6014565,8.792753102,3974.600639,28.91094478,8.465507054,159.0416125,24.52389127,1.148353846,1,3.450103998,10.88641841,8.463884794,357.1606297,19041.58163,4.841455829,90.24937279,51835.41667,140.4146052,0.986145112,149.286,141.3157895,94.74145153,1503.838018,0.417518726,443.4852356,2104.99154,215.416805,3.925650206,12.85911787,23.18123588,70.02420353,3329.304193,11.20809521,45.75493528,26.21375078,5.455063673,16.30534151,10.94233078,1.922500386,0.53555556,4.523900435,72.73338498,153.7430099,4.743920269,5.076751839,5.299234255,133.3351196,78.29384185,5.216171719,40.32576985,11.44111832,1.841837342,43.79686675,12.26698352,4.868127568,3.342583583,5.307805125,40.64969367,37.55547058,1.421771429,11361.18721,935.9519608,31628.38983,16.30534151
JPY,0.034220921,4.481692287,0.188365776,0.011961007,0.015729379,0.014891964,0.018633768,0.776132377,0.014895837,0.003512262,0.030284493,181.8181818,0.01190377,0.008748462,5.516936997,0.058898712,26.62406816,0.19366146,0.056706637,1.065348475,0.164274555,0.007692308,0.006698552,0.0231107,0.072923237,0.05669577,2.392458969,127.5510204,0.032430743,0.604540096,347.2222222,0.940574503,0.006605744,1,0.946611132,0.634630518,10.07353682,0.002796771,2.970708811,14.10039481,1.442980621,0.026296171,0.086137467,0.155280709,0.469060753,22.3015165,0.075078006,0.306491803,0.175594167,0.036541026,0.109222174,0.073297769,0.012877968,0.003587447,0.030303581,0.487208345,1.029855511,0.031777396,0.034006885,0.035497195,0.893152202,0.524455353,0.034940796,0.270124257,0.076638923,0.012337643,0.293375579,0.082171024,0.032609405,0.022390469,0.035554607,0.272294078,0.251567264,0.00952381,76.10350076,6.269522667,211.8644068,0.109222174
SGD,2.773700127,363.2535293,15.26756605,0.969472639,1.274909595,1.207034785,1.510318356,62.90767129,1.207348712,0.28467856,2.454641704,14736.86545,0.964833407,0.709087028,447.1629703,4.773903159,2157.954207,15.69679587,4.596229462,86.3494343,13.31490611,0.623482769,0.542936109,1.873186041,5.910629656,4.595348679,193.9154027,10338.36224,2.628601191,48.99964332,28143.31944,76.23615945,0.53541379,81.05276,76.72544491,51.43855507,816.4879621,0.226685993,240.7841483,1142.875917,116.9575619,2.131377249,6.981679422,12.58593001,38.01866862,1807.599465,6.085279606,24.84200654,14.23239186,2.961751046,8.852758679,5.9409865,1.04379488,0.290772452,2.4561889,39.48958105,83.47263159,2.575645613,2.756351891,2.877145628,72.39245108,42.50855387,2.832047978,21.89431659,6.211796263,1,23.77890043,6.660188303,2.643082241,1.814809325,2.881799063,22.07018652,20.39022108,0.771931048,6168.398782,508.162116,17172.19492,8.852758679
USD,3.593196744,470.5776901,19.7784065,1.255905747,1.651584814,1.563656221,1.956545679,81.4938996,1.564062899,0.36878755,3.179871715,19090.90909,1.249895842,0.918588558,579.2783846,6.184364748,2795.527157,20.33445334,5.954196915,111.8615899,17.24882831,0.807692308,0.703347936,2.426623527,7.656939922,5.953055902,251.2081918,13392.85714,3.405227965,63.47671009,36458.33333,98.76032281,0.69360313,105,99.39416888,66.6362044,1057.721366,0.293660934,311.9244252,1480.541455,151.5129652,2.76109797,9.044434012,16.30447441,49.25137904,2341.659233,7.883190635,32.1816393,18.43738751,3.836807776,11.4683283,7.696265772,1.352186679,0.376681898,3.181876034,51.15687621,108.1348287,3.336626531,3.570722929,3.727205476,93.78098122,55.06781208,3.668783613,28.363047,8.047086955,1.295452493,30.80443584,8.627957541,3.423987478,2.350999264,3.73323378,28.59087815,26.41456273,1,7990.86758,658.29988,22245.76271,11.4683283
And it needs to be transposed into the following format:

Code: Select all

AED,EUR,Jan-00,4.448719778
AED,EUR,Feb-00,4.448719778
AED,EUR,Mar-00,4.448719778
AED,EUR,Apr-00,4.448719778
AED,EUR,May-00,4.448719778
AED,EUR,Jun-00,4.448719778
AED,EUR,Jul-00,4.448719778
AED,EUR,Aug-00,4.448719778
AED,EUR,Sep-00,4.448719778
AED,EUR,Oct-00,4.448719778
AED,EUR,Nov-00,4.448719778
AED,EUR,Dec-00,4.448719778
AED,EUR,Jan-01,4.448719778
AED,EUR,Feb-01,4.448719778
AED,EUR,Mar-01,4.448719778
AED,EUR,Apr-01,4.448719778
AED,EUR,May-01,4.448719778
AED,EUR,Jun-01,4.448719778
AED,EUR,Jul-01,4.448719778
AED,EUR,Aug-01,4.448719778
AED,EUR,Sep-01,4.448719778
AED,EUR,Oct-01,4.448719778
AED,EUR,Nov-01,4.448719778
AED,EUR,Dec-01,4.448719778
Except, I need to do this for every rate in each row X each column for every Mmm-YY up to 2033. Since the Mmm-YY detail doesn't exist in the source file, how do I code for that for the results file to reflect it accordingly?

Here is a script I use for other application to "Unpivot" for reference:

Code: Select all

:: UNPIVOT.BAT  InputFile  OutputFile  ExtraIDColCount  SkipCount
@echo off
setlocal

CALL C:\Hyperion_Batch\Scripts\NTScripts\_env.cmd

set "input=%~1"
set "output=%~2"
set "idCnt=%~3"
set "skip=%~4"

set "beg=var id,c,col=new Array()"
set "begln=c=0"
set "field=(?:\q(\q\q|[^\q])*\q|[^,]*)"
set "find=^%field%(?:,%field%){%idCnt%}|,%field%"
set "repl=if(++c==1)id=$0;if(ln==1)col[c]=$0;$txt=ln>%skip%&&c>1?id+col[c]+$0:false"

CALL "%UTILPATH%JREPL\JREPL.bat" find repl /x /v /jmatchq /jbeg beg /jbegln begln /f "%input%"  /o "%output%"
Thank you!
Top
Aacini
Expert
Posts: 1914
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:
Contact Aacini

Re: Potential use case for JREPL?

#2 Post by Aacini » 05 Apr 2018 22:03

No comments... :( :cry:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "file=FX_Rates.csv"

rem Initialize "Mmm" month array
set "i=0"
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
   set /A i+=1
   set "Mmm[!i!]=%%a
)

rem Load "cols" from header (first row)
for /F "usebackq delims=" %%a in ("%file%") do (
   set "i=0"
   for %%b in (%%a) do (
      set /A i+=1
      set "col[!i!]=%%b"
   )
   goto continue
)
:continue

rem "I need to do this", where *this* is defined as:
rem - For each (data) row in the file (with currency+"rate[i]")
rem - - For each "rate[i]" in the data row
rem - - - List "col[i]" + currency + Mmm-YY + rate[i]

for /F "usebackq skip=1 delims=" %%a in ("%file%") do (
   set "i=0"
   for %%b in (%%a) do (
      if !i! equ 0 (
         set "currency=%%b"
      ) else (
         rem "Except, I need to do this for every Mmm-YY *for YY from 2000* up to 2033"
         for /L %%y in (2000,1,2033) do (
            set "YY=%%y"
            for /L %%m in (1,1,12) do (
               for %%i in (!i!) do echo !col[%%i]!,!currency!,!Mmm[%%m]!-!YY:~-2!,%%b
            )
         )
      )
      set /A i+=1
   )
)
Antonio
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Potential use case for JREPL?

#3 Post by SIMMS7400 » 06 Apr 2018 02:14

Antonio -

Wow - This is jsut about perfect! Much appreciated!

I am seeing some weird behavoir where the year is flip-flipped sometimes:

Code: Select all

UZS	USD	30-Oct	7990.86758
UZS	USD	30-Nov	7990.86758
UZS	USD	30-Dec	7990.86758
UZS	USD	31-Jan	7990.86758
UZS	USD	Feb-31	7990.86758
UZS	USD	31-Mar	7990.86758
UZS	USD	Apr-31	7990.86758
UZS	USD	31-May	7990.86758
UZS	USD	Jun-31	7990.86758
UZS	USD	31-Jul	7990.86758
UZS	USD	31-Aug	7990.86758
UZS	USD	Sep-31	7990.86758
UZS	USD	31-Oct	7990.86758
UZS	USD	Nov-31	7990.86758
UZS	USD	31-Dec	7990.86758
UZS	USD	Jan-32	7990.86758
UZS	USD	Feb-32	7990.86758
UZS	USD	Mar-32	7990.86758
UZS	USD	Apr-32	7990.86758
UZS	USD	May-32	7990.86758
UZS	USD	Jun-32	7990.86758
UZS	USD	Jul-32	7990.86758
UZS	USD	Aug-32	7990.86758
UZS	USD	Sep-32	7990.86758
UZS	USD	Oct-32	7990.86758
UZS	USD	Nov-32	7990.86758
UZS	USD	Dec-32	7990.86758
UZS	USD	Jan-33	7990.86758
UZS	USD	Feb-33	7990.86758
UZS	USD	Mar-33	7990.86758
UZS	USD	Apr-33	7990.86758
UZS	USD	May-33	7990.86758
UZS	USD	Jun-33	7990.86758
UZS	USD	Jul-33	7990.86758
UZS	USD	Aug-33	7990.86758
UZS	USD	Sep-33	7990.86758
UZS	USD	Oct-33	7990.86758
UZS	USD	Nov-33	7990.86758
Could that just be when I open in excel, formatting issue?
Top
SIMMS7400
Posts: 546
Joined: 07 Jan 2016 07:47

Re: Potential use case for JREPL?

#4 Post by SIMMS7400 » 06 Apr 2018 02:15

Yes, seems to be just excel. When I oen in CSV it's fine.

Thank you so much!! Have a great weekend!
Top
dbenham
Expert
Posts: 2461
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

Re: Potential use case for JREPL?

#5 Post by dbenham » 06 Apr 2018 08:33

It is not hard to adapt the existing JREPL code.

I use batch and a bit of JREPL to create a months variable with one line for each date, in the format of "@mon-yy#". I use JREPL to add the new lines, except the last date does not get a new line.

Then in the repl definition, the final value is the months value with the ids and values substituted for the @ and # characters.

I'm not sure about the idCnt and skip business. But the following works for me if the empty line at the beginning of the source file is removed, I add some value before the first column of the first line, and then I use idCnt=0 and skip=1.

Code: Select all

:: UNPIVOT.BAT  InputFile  OutputFile  ExtraIDColCount  SkipCount
@echo off
setlocal

CALL C:\Hyperion_Batch\Scripts\NTScripts\_env.cmd

set "input=%~1"
set "output=%~2"
set "idCnt=%~3"
set "skip=%~4"

setlocal enableDelayedExpansion

set "months="
for /l %%y in (2000 1 2033) do for %%m in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do set "months=!months!@%%m-%%y#"
set "months=!months:-20=-!"
call jrepl "#@" "#\r\n@" /m /xseq /s months /rtn months

set "beg=var id,c,col=new Array()"
set "begln=c=0"
set "field=(?:\q(\q\q|[^\q])*\q|[^,]*)"
set "find=^%field%(?=,%field%)(?:,%field%){%idCnt%}|,%field%"
set "repl=if(++c==1)id=$0;if(ln==1)col[c]=$0;$txt=ln>%skip%&&c>1?env('months').replace(/@([^#]*)#/g,id+col[c]+',$1'+$0):false"

CALL "%UTILPATH%JREPL\JREPL.bat" find repl /x /v /jmatchq /jbeg beg /jbegln begln /f "%input%" /o "%output%"
Not sure how to get it to work with the initial blank line, and the missing 1st value in the header row, but I think you can figure that out. Or modify the source as I have done (seems simpler).

Note - It seems mighty strange to apply a static point in time conversion rate to 33 years of dates :? :!:

Dave Benham
Top
Post Reply

Return to “DOS Batch Forum”