?
Solved

Merging different sized csv files gives different results

Posted on 2011-03-01
15
Medium Priority
?
481 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Steve Lowry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35012684

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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35012713
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
 

Author Comment

by:Steve Lowry
ID: 35012767
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:Bill Prew
ID: 35012833
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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 35012853
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
 

Author Comment

by:Steve Lowry
ID: 35013815
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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 35013935
==> 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
 

Author Comment

by:Steve Lowry
ID: 35014126
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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 35014155
Can you post up the script you are running please.

~bp
0
 

Author Comment

by:Steve Lowry
ID: 35014203
@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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 35016456
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
 

Author Comment

by:Steve Lowry
ID: 35016742
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
 
LVL 58

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 35017296
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
 

Author Closing Comment

by:Steve Lowry
ID: 35017434
It works great now.  

Thanks for all of your help with this.

Steve
0
 
LVL 58

Expert Comment

by:Bill Prew
ID: 35017469
Great, glad we sorted that out, thanks.

~bp
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question