Page 1 of 1

Merging CSVs with batch script but starting each on a NEW row

Posted: 10 Feb 2019 12:47
by batchhelp1
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:

Code: Select all

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 file looks like:
Image

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):
Image

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 :/

Re: Merging CSVs with batch script but starting each on a NEW row

Posted: 10 Feb 2019 13:17
by Squashman
Essentially this is happening because your files do not end with a carriage return and line feed. Uncommon with most data processing applications these days. What program created these files? We have a thread on the forum that discusses this issue already. Just need to search to find it.

Re: Merging CSVs with batch script but starting each on a NEW row

Posted: 10 Feb 2019 15:46
by batchhelp1
OK, that makes sense. (I'm downloading the files from a site that provides end-of-day stock price data files...so i don't have any say in what format they provide them in...)
Is there some simple way I can amend the batch file I'm using to insert a carriage return or line break after each CSV file it merges?

Re: Merging CSVs with batch script but starting each on a NEW row

Posted: 10 Feb 2019 16:03
by Squashman
batchhelp1 wrote:
10 Feb 2019 15:46
OK, that makes sense. (I'm downloading the files from a site that provides end-of-day stock price data files...so i don't have any say in what format they provide them in...)
Never hurts to mention the problem to them.
batchhelp1 wrote:
10 Feb 2019 15:46
Is there some simple way I can amend the batch file I'm using to insert a carriage return or line break after each CSV file it merges?
Pretty sure there is. I can't find the thread where we discussed this issue already. Being a weekend someone will probably come along tomorrow and post the link to the thread.

Re: Merging CSVs with batch script but starting each on a NEW row

Posted: 12 Feb 2019 04:49
by penpen
In case no CSV file ends on a newline, the easiest option i see were to just preprocess the files after downloading (using different folders); untested ("toTest.bat"):

Code: Select all

@echo off
setlocal enableExtensions disableDelayedExpansion
set "downloaded=D:\ownloaded"
set "preprocessed=P:\reprocessed"
set "merged=M:\erged"

:: preprocess:
:: appending windows line ending ("\r\n") to each file and move to preprocessed directory
:: needs to be changed if your files only uses linux style line endings ("\n").
pushd "%downloaded%"
for %%a in ("*.csv") do (
	>"%%~a" echo(
	move "%%~a" "%preprocessed%"
)
popd

:: merge
pushd "%preprocessed%"
copy /a "*.csv" "%merged%\concat.csv"
popd
You might need to change the directories. Also that script might contain errors, so test it on test data first.

penpen