Page 1 of 1
JREPL - Combine Data From Two Files By Matching Strings
Posted: 25 Jun 2020 10:48
by houstontech
Hi,
I've used JREPL for a few different formatting tasks but haven't been able to figure out how to do the following:
File 1:
3498=ABC Company
File 2:
3498-0112=General Expenses
FinalOutput:
3498-0112=General Expenses=ABC Company
Can JREPL be used to check data from both files or do I need to get them in the same file first and then try pattern matching?
Thanks!
Re: JREPL - Combine Data From Two Files By Matching Strings
Posted: 26 Jun 2020 14:01
by Aacini
Your question is pretty incomplete; there are a lot of details that are not well defined. However, you don't need JREPL to perform this simple sustitution:
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Load first-token values from File 1:
for /F "tokens=1* delims==" %%a in (File1.txt) do set "company[%%a]=%%b"
rem Show File 2 with value replacements
for /F "tokens=1* delims=-" %%a in (File2.txt) do echo %%a-%%b=!company[%%a]!
Antonio
PS - As far as I know, JREPL can not be used to solve this problem...
Re: JREPL - Combine Data From Two Files By Matching Strings
Posted: 26 Jun 2020 15:32
by houstontech
Thanks, Antonio. I'll provide more information. The files will actually contain thousands of lines each:
File 1:
3498=ABC Company
73=First Hospital
78=Best Organization
File 2:
3498-0112=General Expenses
73-0001=Bills
73-0292=Documents
78-0003=Human Resources
FinalOutput:
3498-0112=General Expenses=ABC Company
73-0001=Bills=First Hospital
73-0292=Documents=First Hospital
78-0003=Human Resources=Best Organization
I've come up with the following script:
Code: Select all
@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
(
for /f "tokens=1-2 delims==" %%A in (File2.txt) do (
set clientraw=%%~A
set client=!clientraw:~0,-5!
for /f "tokens=1-2 delims==" %%i in (File1.txt) do (
findstr ^!client! File1.txt >nul 2>&1 && (
if !client!==%%i (Echo:%%~A=%%~B=%%~j)
)
)
)
) >FinalOutput.txt
The code above works but takes FOREVER to run. Any help would be appreciated.
Thanks,
James
Re: JREPL - Combine Data From Two Files By Matching Strings
Posted: 26 Jun 2020 15:49
by houstontech
Another update. I did just speed it up by removing the findstr line as this was serving no real purpose ;P:
Code: Select all
@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
(
for /f "tokens=1-2 delims==" %%A in (File2.txt) do (
set clientraw=%%~A
set client=!clientraw:~0,-5!
for /f "tokens=1-2 delims==" %%i in (File1.txt) do (
if !client!==%%i (Echo:%%~A=%%~B=%%~j)
)
)
) >FinalOutput.txt
Do you see any other areas where I can improve the speed of execution?
Thanks,
James
Re: JREPL - Combine Data From Two Files By Matching Strings
Posted: 27 Jun 2020 08:30
by siberia-man
Let's consider
FILE1 is as follows:
Code: Select all
3498=ABC Company
73=First Hospital
78=Best Organization
and FILE2 is:
Code: Select all
3498-0112=General Expenses
73-0001=Bills
73-0292=Documents
78-0003=Human Resources
and the js-script combining both files in the mode you want is called FILECOMBINER.js:
Code: Select all
var STDIN = WScript.StdIn;
var STDOUT = WScript.StdOut;
var line;
var m;
var match_n;
var match_s;
while ( ! STDIN.AtEndOfStream ) {
line = STDIN.ReadLine();
m = line.match(/^(\d+)=(.*)/);
if ( m ) {
match_n = m[1];
match_s = m[2];
continue;
}
m = line.match(/^(\d+)-\d+=.*/);
if ( m && m[1] == match_n ) {
STDOUT.WriteLine(line + '=' + match_s);
}
}
Now we can produce the expected result as follows:
Code: Select all
( type FILE1 & type FILE2 ) | sort /r | cscript //nologo FILECOMBINER.js
No performance testing on my side. I leave it for the topic starter
Re: JREPL - Combine Data From Two Files By Matching Strings
Posted: 27 Jun 2020 08:46
by Aacini
houstontech wrote: ↑26 Jun 2020 15:32
Thanks, Antonio. I'll provide more information. The files will actually contain thousands of lines each:
File 1:
3498=ABC Company
73=First Hospital
78=Best Organization
File 2:
3498-0112=General Expenses
73-0001=Bills
73-0292=Documents
78-0003=Human Resources
FinalOutput:
3498-0112=General Expenses=ABC Company
73-0001=Bills=First Hospital
73-0292=Documents=First Hospital
78-0003=Human Resources=Best Organization
I've come up with the following script:
Code: Select all
@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
(
for /f "tokens=1-2 delims==" %%A in (File2.txt) do (
set clientraw=%%~A
set client=!clientraw:~0,-5!
for /f "tokens=1-2 delims==" %%i in (File1.txt) do (
findstr ^!client! File1.txt >nul 2>&1 && (
if !client!==%%i (Echo:%%~A=%%~B=%%~j)
)
)
)
) >FinalOutput.txt
The code above works but takes FOREVER to run. Any help would be appreciated.
Thanks,
James
How many lines are "thousands of lines"? What is the size of File 1 in bytes?
Did you tested my code? I am pretty sure that it will run
much faster than yours!!! (Unless File 1 is several megabytes size). Tip to speed up my code: change "company[%%a]" by "c%%a"
Can both files be sorted? If so, the
file merge solution below could be faster, unless that the time involved in the sort process be very large:
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Sort both files
sort File1.txt File1Sorted.txt
sort File2.txt File2Sorted.txt
set "companyKey="
rem File 1 will be read from redirected input
< File1Sorted.txt (
rem File 2 will be read via FOR /F
for /F "tokens=1* delims=-" %%a in (File2Sorted.txt) do (
if "%%a" neq "!companyKey!" call :seekCompany "%%a"
echo %%a-%%b=!companyName!
)
) > output.txt
goto :EOF
:seekCompany key
rem Read next line from File 1
set /P "line1="
for /F "tokens=1* delims==" %%a in ("%line1%") do (
if "%%a" neq "%~1" goto seekCompany
set "companyKey=%%a"
set "companyName=%%b"
)
exit /B
This method will fail if there is not a matching company in File1 for a record in File2, but you may insert additional code for this case. Of course, if the files are sorted already (you did NOT
specified this point), just remove the two sort commands.
Please, report the timing of these two methods.
Antonio