CODEWITHSUNDEEP
csvbatch-filewindows-7mergecommand-prompt
mikebah
mikebah

Reputation: 25

Merge CSV Files (Without Duplicates) in Batch

I want to merge two similar CSV files using batch. I have found a file that was working perfectly and now is not. This may have been due to renaming the CSV files that were used.

I want what is demonstrated below:

File 1:

name1,group1,data1
name2,group2,data2
name3,group3,data3

File 2:

name1,group1,data1,time1
name2,group2,data2,time2

Merged file:

name1,group1,data1,time1
name2,group2,data2,time2
name3,group3,data3

(Note that the fourth column was not filled in by name3 and was subsequently not on file 2.)

The following code was modified from: http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/Q_27694997.html.

@echo off

set "tmpfile=%temp%\importlist.tmp"
set "csvfile=importlist.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

The problem is that when executed it just creates a sorted CSV with all the data from the first file and not the second.

I have attempted to get it working by putting quotation marks around the parameter (%1 - "directory*.csv") to no avail.

Upvotes: 0

Views: 1780

Answers (1)

Endoro
Endoro

Reputation: 37569

you might try this

@echo off &setlocal disabledelayedexpansion
for /f "delims=" %%a in (file1.csv) do set "@%%~a=7"
for /f "tokens=1-4delims=," %%a in (file2.csv) do (
    set "lx1=@%%~a,%%~b,%%~c"
    setlocal enabledelayedexpansion
    if defined !lx1! (
        endlocal
        set "@%%~a,%%~b,%%~c="
    ) else (
        endlocal
    )
    set "@%%~a,%%~b,%%~c,%%~d=4"
)
(for /f "delims==@" %%a in ('set @') do echo %%~a)>merge.csv
type merge.csv

It doesn't work, if you have = or @ in your data.

Upvotes: 1

Related Questions