Solved

merge csv files

Posted on 2014-02-20
7
280 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 53

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
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.

 
LVL 53

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 53

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

808 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