Solved

merge csv files

Posted on 2014-02-20
7
288 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 54

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 35

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 54

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 54

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

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

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 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