Extract data from a CSV file via batch script.

[foxidrive]

Is there no screen output at all before the pause command?

[foncesa]

Hi Foxidrive,

Is there no screen output at all before the pause command?

It echos [file "131044003" serial "310747"] but does nothing.

[foxidrive]

I can't test it without some of your files but test this and let me know what you see on the console.

Code: Select all

@echo off
set "database=c:\database"
for /f "tokens=1,2" %%a in ('type "file.txt" ^|repl ".{9}(.{9}).{21}(.{6}).*" "$1 $2" ') do (
echo file "%%a" serial "%%b"
   if exist "%database%\%%a.csv" (
      type "%database%\%%a.csv" |repl "(.*?)\|(.*?)\|(.*?)\|.*\|%%b\|.*" "copy $1 $&\t$2\t$3" x > "%temp%\%%a.csv.tmp"
fc /b "%database%\%%a.csv" "%temp%\%%a.csv.tmp"
echo abort the batch file and tell me what you see on the screen
pause
      type "%temp%\%%a.csv.tmp" |repl "copy (..) (.{55})(..)(.*)" "$2$1$3" > "%database%\%%a.csv"
      del  "%temp%\%%a.csv.tmp"
   )
)
pause

[foncesa]

Hi Foxidrive,

This output is shown on the window.
file "131044003" serial "310747"
Comparing files C:\DATABASE\131044003.csv and C:\USERS\FONCESA\APPDATA\LOCAL\TEMP\131044003.CSV.TMP
FC: no differences encountered

I have uploaded sample files in my google drive.
file https://drive.google.com/file/d/0B5HQxI41eRL-VWRidEh4Y1FLU0k/view?usp=sharing
131044003.csv https://drive.google.com/file/d/0B5HQxI41eRL-cUJPWVdWN3lTNlk/view?usp=sharing

Result: 222000322131044003161020140000000500000310747020100105244000000001 2000|325

Many Thanks in advance.

[foxidrive]

EDIT: I made extra changes so copy it again if you tried it earlier.

The basic error was that I had used $3 instead of $4 which stopped the text appending correctly.

I have changed several things to speed up the execution though
and made it search for a 6 digit string instead of a pipe delimited 6 digit string - so it searches from record 4 correctly instead of record 5 which it was doing.

Your records from record 4 all have 6 digit strings so this should be fine, and it will not match in records that only have 5 digits.

To increase the speed a little more you can REM the line with echo in it but you will see nothing on the console to indicate it is running.

Test this on sample files first!

Code: Select all

@echo off
set "database=c:\database"
type "file.txt" |repl ".{9}(.{9}).{21}(.{6}).*" "$1 $2" >file.txt.tmp
for /f "usebackq tokens=1,2" %%a in ("file.txt.tmp") do (
echo file "%%a.csv" serial "%%b"
   if exist "%database%\%%a.csv" (
      type "%database%\%%a.csv" |repl "(.*?)\|(.*?)\|(.*?)\|.*%%b.*" "copy $1 $&\t$2|$3" x |repl "copy (..) (.{55})(..)(.*)" "$2$1$4" > "%database%\%%a.csv.tmp"
      move /y "%database%\%%a.csv.tmp" "%database%\%%a.csv" >nul
   )
)
del file.txt.tmp
pause

[foxidrive]

I edited my post while you were testing it. Look above.