.bat for comparing spreadsheets.

[Turcel]

We're updating our stock database and we have the new stock sheet from the manufacturer with updated prices and our own spreadsheet with our current stock's prices and S/Ns. So we're having to manually search each S/N one by one, check if exists in the new stock sheet (we have a lot of outdated stock in our database) and if it does update the price. Someone mentioned this might be possible using .bat but I'm not confident, hopefully someone can correct me if so.

Thankyou

[penpen]

If the old database, the update database, and the new database have this format:

Code: Select all

126761876286kasdflk 12.30 currency units
asakllk128937918978 8.50  currency units
...

Or in other words tupels of this form (S/Ns with no space in it, describing string without problematic characters) per line with the line no longer than 1023 (including \r\n).

Then the following may help you:

Code: Select all

@echo off
setlocal enableDelayedExpansion
(
   for /F "tokens=1* usebackq delims= " %%a in ("db_update.txt") do echo(%%a a %%b
   for /F "tokens=1* usebackq delims= " %%a in ("db_old.txt") do echo(%%a b %%b
) > "db_attributed_both.txt"
sort "db_attributed_both.txt" /O "db_ab_sorted.txt"
(
   set "id= "
   for /F "tokens=1-2* usebackq delims= " %%a in ("db_ab_sorted.txt") do (
      if not "!id!" == " %%a" (
         echo(%%a %%c
      )
      set "id= %%a"
   )
) > "db_new.txt"
endlocal
goto :eof

Then the file db_new.txt should hold the new pricelist database.
(Currently untested as i'm currently online using linux.)

penpen

Edit: Added spaces to the front of the variable content of id, as an empty id would be problematic if the first (sorted) S/N is !id!.

[Aacini]

You may use my FindRepl.bat program to do that, you may download it from this site. In particular, this part in that site may be useful for you:

The next example load a series of values from a replacements file with "old:new" format, and process a data file to replace all the strings. The multi-string replacement selection method is based on a direct access to an array element, so its speed is not affected by the number of elements. The only limit is the 8KB total size of the Batch variables that store the sets of replacements.

Code: Select all

@echo off
setlocal EnableDelayedExpansion
set search=
set replace=
for /F "tokens=1,2 delims=:" %%a in (replacements.txt) do (
   set "search=!search!|%%a"
   set "replace=!replace!|%%b"
)
set "search=!search:~1!"
set "replace=!replace:~1!"
< theFile FindRepl =search /A =replace

You may modify previous Batch program in order to correctly read your "old" and "new" data files.

Antonio