Link to home
Start Free TrialLog in
Avatar of Steve Lowry
Steve LowryFlag for United States of America

asked on

Merging different sized csv files gives different results

I am using a simple batch file to merge multiple csv files located in a folder into a single csv file.  The batch file contains the command, copy *.csv importfile.csv.  The batch file is created by opening Notepad, entering the command, and then saving the file with a .bat extension.  The batch file is placed in the folder containing the csv files and run, resulting in a file named importfile.csv.

The csv files I want to merge have a single line with numerous variables.  Examples of the original csv files are named long1.csv, long2.csv and long3.csv.  The merged file created contains a single line of data with the contents of the three csv files strung together.  

If I truncate the amount of data in each csv file (marked short1.csv, etc.), the merged file contains three separate lines of data.  This is the desired format of the merged csv file.

Is there a line size limit on the csv file that is causing the different results, or is there something else in the original csv files that is causing the issue?

Thanks for your help.
Long1.csv
Long2.csv
Long3.csv
Short1.csv
Short2.csv
Short3.csv
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image


When you have edited and truncated you have saved with a newline/return at the end of the lines.

You long files just finish at the end of the data, no newline/return. Hence they join end to end
You could just create a textfile called newline.txt that has nothing but a single return in it and then use

 
COPY long1.csv + newline.txt + long2.csv + newline.txt + long3.csv +newline.txt NEWFILE.CSV

Open in new window

Avatar of Steve Lowry

ASKER

Okay.  The long csv files are created by a lab instrument at the end of each measurement.  The vendor's software would be responsible for putting that newline/return at the end of the csv file.  Of course, I have no control over that.

Is there a way to modify a batch of csv files to add that newline/return to each of the files before I do a merge?

My final purpose in merging these csv files is to put the data from the individual measurements in a spreadsheet where I can analyze the data.

Thanks.
Steve
Avatar of Bill Prew
Bill Prew

This approach should work.  Make sure you aren't putting the new CSV file in the same folder as the existing ones, or you could get stuck in a loop.

@echo off
set BaseDir=c:\source
set DestFile=c:\dest\importfile.csv
if exist "%DestFile%" del "%DestFile%"
for %%A in ("%BaseDir%\*.csv") do (
  for /F "tokens=* usebackq" %B in ("%%~A") do (
    echo %%B>>"%DestFile%"
  )
)

Open in new window

~bp
If you want to just add a linefeed at the end of the existing files, you could do:

for %%A in ("c:\source\*.csv") do echo.>>"%%~A"

Open in new window

~bp
Do I copy your code and put it in Notepad and save it as a .bat file?  Then change the BaseDir and DestFile to match my folders.  Save and run.   If so, your first code is not working for me.

Also where does the single line of code get added or what does it substitute?

Thanks.
Steve
==> Do I copy your code and put it in Notepad and save it as a .bat file?  Then change the BaseDir and
==> DestFile to match my folders.  Save and run.   If so, your first code is not working for me.

Yes, save as a BAT, adjust SET lines, and run.  I tested it here, seemed to work okay,what do you get there?

==> Also where does the single line of code get added or what does it substitute?

It was an alternative to your question about how to add a linefeed to the end of the existing CSV files.  Would also go in a BAT, likely before the COPY *.CSV you are already using.

~bp
The importfile.csv file is not created when I run the batch file.  I am running Windows 7 64 bit, and I ran the file as administrator.
Can you post up the script you are running please.

~bp
@echo off
set BaseDir=c:\source
set DestFile=c:\dest\importfile.csv
if exist "%DestFile%" del "%DestFile%"for %%A in ("%BaseDir%\*.csv") do (for /F "tokens=* usebackq" %B in ("%%~A") do (echo %%B>>"%DestFile%"))

I even made a "source" and a "dest" folder just to make sure I wasn't naming the Set correctly.
Looks like you made a cut and paste error, you are missing a line break before the FOR in "del "%DestFile%"for ", see my original post.  I would also suggest you don't try to squish those multiple commands onto a single line, any gain in performance is offset by a loss in readability and maintainability.

~bp
I recopied your original post and made a new .bat file.  I still do not get a file created in the destination folder.  I have attached the batch file with the file extension changed to txt, so I could post the exact file.
newmerge.txt
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works great now.  

Thanks for all of your help with this.

Steve
Great, glad we sorted that out, thanks.

~bp