CODEWITHSUNDEEP
batch-filecsvmergecmd
user3229528
user3229528

Reputation: 29

Merging CSV files without header

I searched extensively and found what I believe to be a solution to my problem, which is merging CSV files without duplicating headers each time. It looks like it works, except it's only copying the first file in the folder into the destination file. I think it's unable to open the files because they have a space in the name. I've been advised I probably just need to put quotes somewhere, but I'm not sure where they would go. Thanks in advance.

@ECHO OFF
SET first=y
SET newfile=new.csv
for %%F in (*.csv) do IF NOT %%F==%newfile% (
  if defined first (
    COPY /y "%%F" %newfile% >nul
    set "first="
  ) else (
    FOR /f "skip=1delims=" %%i IN (%%F) DO >> %newfile% ECHO %%i
  )
)

Upvotes: 2

Views: 10271

Answers (5)

MC ND
MC ND

Reputation: 70923

@echo off
    setlocal enableextensions disabledelayedexpansion

    rem configure paths
    set "source=*.csv"
    set "target=newfile.csv"

    rem remove output file if needed
    if exist "%target%" del "%target%" >nul 2>nul

    rem search for header row
    set "headerRow="
    for %%f in ("%source%") do (
        <"%%~ff" ( for /l %%a in (1 1 10) do if not defined headerRow set /p "headerRow=" )
        if defined headerRow goto haveHeader
    )
:haveHeader
    if not defined headerRow (
        echo ERROR: impossible to get header row. 
        goto endProcess
    )

    rem output header to header file to use as filter.
    rem header is cut to avoid findstr limitations on search strings
    set "headerFile=%temp%\%~nx0_headerFile.tmp"
    setlocal enableextensions enabledelayedexpansion
    > "%headerFile%" echo(!headerRow:~0,125!
    endlocal


    rem search for input files with matching headers to join to final file
    for /f "tokens=*" %%f in ('findstr /m /b /l /g:"%headerFile%" "%source%"') do (
        if not exist "%target%" (

                rem first file is directly copied
                copy "%%~f" "%target%" /y > nul 2>nul

            ) else (

                rem next files are filtered to exclude the header row
                findstr /v /b /l /g:"%headerFile%" "%%~f" >> "%target%"
        )
        echo ... [%%~ff] joined to %target%
    )

    rem remove the temporary header file
    del "%headerFile%" >nul 2>nul

:endProcess
    endlocal

Upvotes: 3

Endoro
Endoro

Reputation: 37569

@echo off &setlocal disableDelayedExpansion
set "NewFile=new.csv"

>"%NewFile%" cd .
for /f "tokens=1*delims=:" %%a in ('dir /b /a-d /od *.csv ^|findstr /nvx "%NewFile%"') do (
    if %%a equ 1 (
        copy /b "%%~b" "%NewFile%" >nul
    ) else (
        for /f "skip=1delims=" %%c in ('type "%%~b"') do >>"%NewFile%" echo(%%c
    )
)

sed for Windows

Upvotes: 1

foxidrive
foxidrive

Reputation: 41224

Here's another option. 

@echo off
set "newfile=new.txt"
del "%newfile%" 2>nul
for %%a in (*.csv) do (
  if not exist "%newfile%" (type "%%a" > "%newfile%") else (more +1 "%%a" >> "%newfile%")
)
ren "%newfile%" "new.csv"

Upvotes: 1

Magoo
Magoo

Reputation: 79982

@ECHO OFF
SET first=y
SET "newfile=new.txt"
del new.csv 2>nul >nul
for %%F in (*.csv) do (
  if defined first (
    COPY /y "%%F" %newfile% >nul
    set "first="
  ) else (
    FOR /f "usebackqskip=1delims=" %%i IN ("%%F") DO >> %newfile% ECHO %%i
  )
)
ren %newfile% new.csv

The set "var=value" syntax ensures that any trailing spaces on the batch line are not included in the value assigned to var.

First step is to delete the new.csv file - the 2>nul >nul redirects messages and error messages from del so that the command is totally silent - whether the file exists or not.

Next, you don't need to check whether the new.csv is selected as %%F because it's just been deleed if it did exist, and the output is now to new.txt (filename not critical - actually, I'd be tempted to call it new.vsc. The critical thing is that it isn't .csv so for doesn't need to check it)

Other than the first file (a copy is faster than reading and echoing), the name of the file (in %%F) being read into %%i, since it needs to be "quoted" (to tell CMD that the spaces are not separators) you need to add the usebackq to the for/f controls.

Finally, rename your file to the desired new name.

This should fix the problem.

Upvotes: 0

Mark Setchell
Mark Setchell

Reputation: 207385

I think the line near the end starting "FOR /f" is mixed up and it should be:

@ECHO OFF
SET first=y
SET newfile=new.csv
for %%F in (*.csv) do IF NOT %%F==%newfile% (
  if "%first%"=="y" (
    COPY /y "%%F" %newfile% >nul
    set "first="
  ) else (
    FOR /f "skip=1delims=" %%i IN ("%%F") DO ECHO %%i >> %newfile%
  )
)

Upvotes: 0

Related Questions