Solved

merge csv files

Posted on 2014-02-20
7
269 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 52

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 52

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 52

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

895 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