1

I know this is a common question but I run into some bugs and hope for some help.

I want to merge over a 1000 csv files in multiple subfolders into one file. The Script is in the MainFolder and should run through the subfolder e.g. 01_2015 to 05_2015 and merge the csv files into one file in the MainFolder.

I've got the following folder structure:

-MainFolder
    -01_2015
    -02_2015
    -03_2015
    -04_2015
    -05_2015

The script I'm using (got it from here ):

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET SUMMARY_FILE=sumfile.csv
IF EXIST "%SUMMARY_FILE%" (DEL "%SUMMARY_FILE%")


SET /A LINE_COUNT=1

FOR /F "usebackq tokens=*" %%f IN (`DIR /S /B *.csv`) DO (
    FOR /F "usebackq tokens=*" %%s IN ("%%~f") DO (
        ECHO !LINE_COUNT!,%%s >>"%SUMMARY_FILE%"
        SET /A LINE_COUNT=!LINE_COUNT! + 1
    )
)
EXIT /B 0

It is actually running through the over 1000 files. But the files don't get merged. What to do?

Community
  • 1
  • 1
mafraqs
  • 173
  • 2
  • 3
  • 15

1 Answers1

2

Try this slightly modified code:

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
PUSHD "%~dp0"

SET "SUMMARY_FILE=sumfile.csv"
DEL /F "%SUMMARY_FILE%" 2>nul

SET "LINE_COUNT=1"

FOR /F "tokens=*" %%f IN ('DIR /S /B *.csv 2^>nul') DO (
    FOR /F "usebackq tokens=* eol=ÿ" %%s IN ("%%~f") DO (
        >>"%SUMMARY_FILE%" ECHO !LINE_COUNT!%%s
        SET /A LINE_COUNT+=1
    )
)

POPD
ENDLOCAL

The redirection >>"%SUMMARY_FILE%" is now at beginning of the line which outputs the line from current CSV file with line number into the summary file. This avoids appending a space at end of every line in summary file.

Do you have write permissions in directory which is set as current directory on running the batch file?

I added the line PUSHD "%~dp0" to make sure the directory of the batch file is the current directory before starting processing and restore previous working directory with POPD before exiting batch processing.

eol=ÿ is used to define a character most likely not existing in the CSV files as end of line character instead of ; which is the default. German CSV files contain ; as separator.

Character ÿ has decimal value 255 in code page Windows-1252, i.e. is the last character in code page Windows-1252. This byte is a non breaking space in OEM code page 850. So with batch file being displayed or edited with code page 850 or OEM code page 437, eol=  is displayed in viewer/editor.

No separator should be used between !LINE_COUNT! and %%s if all lines start already with a semicolon in the CSV files which is also the separator between the field values. Otherwise the separator (comma, semicolon, pipe (escaped), tab) should be inserted left to %%s.

Another problem would be if the CSV files are Unicode files encoded with UTF-16. In this case no summary file would be created as command FOR reads any line from the CSV files containing lots of null bytes.

Mofi
  • 46,139
  • 17
  • 80
  • 143
  • Thanks for your answer. I checked the csv files for their encoding (it's ANSI). Your script is running well but again... Nothing is merged. I tested it also with some random csv files from the internet. It turns out that all the csv files start with a `;` (semicolon). Viewed in excel, the first whole column is empty. I removed it from a few of my files and now the script works and they get merged. What do I need to do, to get the script to work for files with a `;` in front (first column empty when viewed in excel)? – mafraqs Dec 08 '15 at 12:24
  • I edited the answer, added `eol=ÿ` to second __FOR__ loop and removed comma between `!LINE_COUNT!` and `%%s` as it looks like your CSV files use the semicolon as separator being already present at beginning of each line. – Mofi Dec 08 '15 at 12:44
  • NICE! Thanks @Mofi . I wish I knew how to do it myself! Working like a charm! :D – mafraqs Dec 08 '15 at 13:08