Solved

Merging different sized csv files gives different results

Posted on 2011-03-01
15
471 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
==> 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:sxlowry
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
Can you post up the script you are running please.

~bp
0
 

Author Comment

by:sxlowry
Comment Utility
@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 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
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 51

Accepted Solution

by:
Bill Prew earned 500 total points
Comment Utility
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
Comment Utility
It works great now.  

Thanks for all of your help with this.

Steve
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Great, glad we sorted that out, thanks.

~bp
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now