Create a .bat file based on common column and out put will be selective column

Question

I am completely new to .bat code. I want to merge two csv file based on common column and selective columns for the output file. For ex

file1.csv

id,name,roll
1,x,12
2,y,13

file2.csv

id,class,subject
1,V,english
2,III,Math

Output will be

result.csv

id,name,class,subject
1,x,V,english
2,y,III,Math

Below is the code I started. My problem is how to use the common column and select the column.

@echo off

set "tmpfile=%temp%\result.tmp"
set "csvfile=result.csv"
copy nul "%tmpfile%" >nul

echo.
echo Processing all CSV files...
set "header="
for %%a in (%1) do (
    if not "%%a"=="%csvfile%" (
        set /p =Processing %%a...<nul
        for /f "tokens=1* usebackq delims=," %%b in ("%%a") do (
            if /i "%%b"=="Keyword" (
                if not defined header (
                    set /p =Found header...<nul
                    set "header=%%b,%%c"
                )
            ) else (
                title [%%a] - %%b,%%c
                findstr /b /c:"%%b" /i "%tmpfile%">nul || echo %%b,%%c>>"%tmpfile%"
            )
        )
        echo OK
    )
)
echo Finished processing all CSV files

echo.
echo Creating %csvfile%
echo %header%>"%csvfile%"

set /p =Sorting data...<nul
sort "%tmpfile%">>"%csvfile%"
echo OK
del "%tmpfile%"

echo Finished!
title Command Prompt
exit /b

You're not new to this site, so should be aware that we do not provide code to your specifications. In order to receive assistance with a specific issue you need to, research, write, and test your own code. Then attempt to fix any issue it exhibits issue yourself first. If the code then fails to work as written and intended, you'd provide a [mcve] of it as an edit to your question, complete with sufficient supporting information for us to replicate your issue, _(remembering to format it correctly)_. Additionally, there's no such thing as a column in a CSV file, they are fields. — Compo, Apr 15 '20 at 22:19
Exists the same records of common column in both files? If not, what to do in such a case? Are both files sorted by the common column? No one can even start to write code if the problem specifications are not complete... — Aacini, Apr 15 '20 at 23:50
@Compo sorry for the direct question actually I started writing the code but I am unable to start. Because script is completely new. Question updated — madhurjya, Apr 16 '20 at 02:05
Just to clarify your last comment, @madhurjya, do both files have exactly the same number of records, with exactly the same `id` fields, in the same line numbers and order, in both `file1.csv` and `file2.csv`?. If not, do you want to use each record in `file2.csv` which has a matching `id` field in `file1.csv`, and insert `field 2` from all matching `id` records of `file1.csv` into those records. Or the other way round, use each record in `file1.csv` which has a matching `id` field in `file2.csv`, and replace `field 3` with fields `2` & `3` from matching `id` records in `file2.csv`. — Compo, Apr 16 '20 at 07:51
@Compo yes the files may not have the same lines of ids. Yes as per your last sentence "use each record in file1.csv which has a matching id field in file2.csv, and replace field 3 with fields 2 & 3 from matching id records in file2.csv" — madhurjya, Apr 16 '20 at 08:23
I would suggest you instead look a [tag:powershell], which has built-in [tag:csv] support. You could, if necessary, still run a powershell solution from a [tag:batch-file]. — Compo, Apr 16 '20 at 08:30
@madhurjya, I have rolled back your edit. You cannot just insert a powershell tag, based upon my recommendation above. This is not a free code writing site, so adding that tag, without providing your written and failing powershell code, is the same as asking for someone to do the powershell work for you. What you need to do is research doing this in powershell, then write some powershell code, then test it, and edit your question accordingly. — Compo, Apr 16 '20 at 08:39

score 0 · Accepted Answer · answered Apr 16 '20 at 15:08

The problem description lacks multiple details and the example code provided has no relation to the description. In the description you talk about two files but in the code you process "all CVS files" with no indication of which "two files" should be merged. The code has an undescribed "Keyword" value. The code sorts the output file, but this is not necessary because "both input files are sorted by common column" as you said...

Anyway I tried to help you writting a working code that achieve what you said in the problem description. I searched for "file merge" in the Batch-file tag and get multiple results, so I just used this method as base...

@echo off
setlocal EnableDelayedExpansion

set "Header1="
set "Header2="
set "common2=-999999999"

rem Read second file from redirected input
< file2.csv (
   rem Read first file via FOR /F
   for /F "tokens=1,2 delims=," %%a in (file1.csv) do (
      if not defined Header1 (
         set "Header1=%%a,%%b"
      ) else (
         if !common2! lss %%a call :ReadNextFile2RecordUntil %%a
         if !common2! equ %%a echo %%a,%%b,!rest2!
      )
   )
) > result.csv
goto :EOF


:ReadNextFile2RecordUntil commonCol
set "line2="
set /P "line2="
if not defined line2 set "common2=999999999" & exit /B
for /F "tokens=1* delims=," %%A in ("%line2%") do (
   if not defined Header2 (
      set "Header2=%%B"
      echo %Header1%,%%B
      goto ReadNextFile2RecordUntil %1
   ) else (
      set "common2=%%A"
      set "rest2=%%B"
   )
)
if !common2! lss %1 goto ReadNextFile2RecordUntil %1
exit /B

file1:

id,name,roll
1,x,12
2,y,13
4,z,record1 with no matching record2
5,t,15

file2.csv:

id,class,subject
1,V,english
2,III,Math
3,IV,record2 with no matching record1
5,V,OK

result.csv:

id,name,class,subject
1,x,V,english
2,y,III,Math
5,t,V,OK

Thanks for supporting. I modified little bit and finally I got my expected output. Really appreciate. — madhurjya, Apr 17 '20 at 08:33

Create a .bat file based on common column and out put will be selective column

1 Answers1