Solved

Merging different sized csv files gives different results

Posted on 2011-03-01
15
473 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:sxlowry
  • 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:sxlowry
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 53

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 53

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:sxlowry
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 53

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:sxlowry
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 53

Expert Comment

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

~bp
0
 

Author Comment

by:sxlowry
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 53

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:sxlowry
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 53

Accepted Solution

by:
Bill Prew earned 500 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:sxlowry
ID: 35017434
It works great now.  

Thanks for all of your help with this.

Steve
0
 
LVL 53

Expert Comment

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

~bp
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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