Solved

merge csv files

Posted on 2014-02-20
7
264 Views
Last Modified: 2014-03-10
I have a folderr of 200 csv files (all different in terms of numbers of rows and columns), I need a way to import all the data into one csv file, seperated, and ideally split by some row indicating file name i..e

123.csv:

1   2   3
---------

a  b  c

345.csv:

1   2  3  4
------------------
a   b   c   d
0
Comment
Question by:pma111
7 Comments
 
LVL 51

Accepted Solution

by:
Bill Prew earned 167 total points
ID: 39873304
You might be able to do this a simple way, but just concatenating all the CSV files together, inserting a filename between, and then just open the result in Excel.

Here's a small BAT script that you could try, adjusting the file paths near the top.

@echo off
set BaseDir=c:\temp
set Merge=d:\temp\all.csv
(
  for %%A in ("%BaseDir%\*.csv") do (
    echo FILENAME:%%~nA
    type "%%~A"
  )
) > "%Merge%"

Open in new window

~bp
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 167 total points
ID: 39873313
Here's a powershell solution:
$target = "x:\path\target.csv"
$inputFolder = "x:\another\path"

if (!(Test-Path $target)) {New-Item $target -ItemType "File"}

gci $inputFolder -Recurse -filter "*.csv" -Exclude $target |
  % { 
    echo $_.Name >> $target
    gc $_.FullName >> $target
    echo "`n" >> $target
  }

Open in new window

HTH,
Dan
0
 
LVL 3

Author Comment

by:pma111
ID: 39873319
thanks both

bill, for reference, would be interested in the concatenate solution if you could provide an example
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Expert Comment

by:Bill Prew
ID: 39873570
@pma111
bill, for reference, would be interested in the concatenate solution if you could provide an example
Hmmm, I did provide a starting BAT script in my earlier post, could you not see that?

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39873622
I just did a quick test and the approach I suggested seemed to work.  I created two test files as follows:

file1.csv
Heading1,Heading2,Heading3
1,2,3
4,5,6
7,8,9

Open in new window

file2.csv
Heading10,Heading20,Heading30,Heading40,Heading50,Heading60
10,20,30,40,50,60
70,80,90,100,110,120

Open in new window

I then ran the BAT script, which produced the following file:

all.txt
FILENAME:file1
Heading1,Heading2,Heading3
1,2,3
4,5,6
7,8,9
FILENAME:file2
Heading10,Heading20,Heading30,Heading40,Heading50,Heading60
10,20,30,40,50,60
70,80,90,100,110,120

Open in new window

I then opened the all.csv file generated in Excel.  Here was the result of that.

Test results~bp
0
 
LVL 3

Author Comment

by:pma111
ID: 39873721
thanks... will have a go shortly..
0
 
LVL 22

Assisted Solution

by:Dreamboat
Dreamboat earned 166 total points
ID: 39875770
Ron deBruin has posts on Microsoft's site and is a long-time MVP. Looks like he created an addin that'll do it for you.

http://www.rondebruin.nl/win/addins/rdbmerge.htm
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

12 Experts available now in Live!

Get 1:1 Help Now