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
Steve LowryPrincipal ChemistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bill PrewConnect With a Mentor Commented:
Ah, I see a problem now, change:

  for /F "tokens=* usebackq" %B in ("%%~A") do (

to
  for /F "tokens=* usebackq" %%B in ("%%~A") do (

~bp
0
 
Neil RussellTechnical Development LeadCommented:

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
0
 
Neil RussellTechnical Development LeadCommented:
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

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Steve LowryPrincipal ChemistAuthor Commented:
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
0
 
Bill PrewCommented:
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
0
 
Bill PrewCommented:
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
0
 
Steve LowryPrincipal ChemistAuthor Commented:
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
0
 
Bill PrewCommented:
==> 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
0
 
Steve LowryPrincipal ChemistAuthor Commented:
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.
0
 
Bill PrewCommented:
Can you post up the script you are running please.

~bp
0
 
Steve LowryPrincipal ChemistAuthor Commented:
@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.
0
 
Bill PrewCommented:
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
0
 
Steve LowryPrincipal ChemistAuthor Commented:
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
0
 
Steve LowryPrincipal ChemistAuthor Commented:
It works great now.  

Thanks for all of your help with this.

Steve
0
 
Bill PrewCommented:
Great, glad we sorted that out, thanks.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.