Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

merge csv files

Posted on 2014-02-20
7
Medium Priority
?
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 57

Accepted Solution

by:
Bill Prew earned 668 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 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 668 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 57

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 57

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

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

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

688 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