Batch file that combines CSVs but start each on new row

February 10, 2019 at 10:24:23
Specs: Windows 10
I have 100 separate CSV files in a folder that I'm merging into a single CSV file with a simple .Bat batch file that I run from that folder with this simple line of code:

copy /a *.csv concat.csv

Each separate CSV file is in identical format with 25 columns (A:Y), though some have more rows than others. Here's what the first few rows of every CSV file looks like:

I expected the merged CSV to have those same 25 columns, with each all of the data simply stacked on top of each other. HOWEVER, when I run the above batch file to merge them into one, the merged file looks like this (look at cell Y238 in yellow):

So what's happening is that my batch script is indeed merging all the CSV files into 1, but for some reason combines the LAST cell of one file ("28.209" in the image above) with the FIRST cell ("Date") of the next file. The result is that the header row of each successive file stretches way out to column AW as you can see in the pic above.

So just how do I tweak my Batch code that is combining the CSV's so that it puts the data from each individual CSV file on a new row in the merged file? I've read and tried 10 different scripts I've found online and can't get this to work :/

message edited by batchhelp1

See More: Batch file that combines CSVs but start each on new row

Reply ↓  Report •

February 10, 2019 at 16:19:14
It looks like the CSV files are not terminated with a final crlf line-break. Although one would expect the EOF character (ctrl-Z or ascii 26) would trigger a line-break between files, it does not (in my test). An easy way to see if your files are lacking that crlf termination: EDIT /70 file.csv
This will show line-breaks. Go to the end and see if it ends with the crlf chars (a 'musical note' followed by a 'target'). (Or: copy file.csv con and see if the last output line includes the "files copied" message.)

If it does not, then you will have to append it to each csv file:
for %%a in (*.csv) do >>%%a echo[
If that fixes the problem, then just put that line before the copy operation in your batch.

message edited by nbrane

Reply ↓  Report •

February 10, 2019 at 16:26:21
Here's what I do for a vaguely-similar task:

for /r %%a in (*.txt) do (
  type "%%a">>"%dest%"

Maybe it'll help you.

Reply ↓  Report •
Related Solutions

Ask Question