Importing a concatenated CSV file into Excel, but headers not on new rows(?)

[d0rian]

This is probably a problem with the Batch file command I'm using to combine the separate CSV files, but here is problem:

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

 

[mole999]

did you read this guide > https://stackoverflow.com/questions/17899897/import-csv-files-into-excel

 

[d0rian]

Actually, I used to use VBA code very similar to that, which looped through 100+ CSV files, and imported them one after another into the same worksheet, but it took around 30 seconds (during which Excel was unusable), and so I thought I'd hit the jackpot when I discovered that I could use a batch file like in my OP to instantaneously merge all the CSV files into 1...it did indeed cut my import time by like 90%, but now I have the problem I started this thread hoping to solve with the mis-aligned headers. I figured there'd be a minor tweak to the batch file, which would simply avoid the header problem altogether, so that I could continue to merge them all with a click prior to import...spent a lot of time googling 'merge several CSV files with batch file' and variations of it, but can't get anything to work...

 

[mole999]

I find if a process is known to be long I use a message box to forewarn. The other things are how close to excel is the data you are drawing down, over a network there will be some latency

 

[d0rian]

Bumping to see if anyone with some batch-file know-how can give me any ideas here...(i guess it's more of a batch file help request than strictly excel, but they're related enough that I thought I'd give it a go...)