Merge Multiple CSV's

kirby14 · 02-17-2007

I know how to do it in VBScript, but I'm wondering if it can be done better in PS.

I want to search through a directory for all files named "blah_blah_<date>*"

Then I want to take all of thse files and place the 2nd column into a new CSV with 3 columns.

IE.

blah_blah_20050217_test.csv (lots of these)
"foo","name of someone","bar"

New_CSV.csv
"name of someone","name of someone else","name of someone else"
"name of someone else","name of someone else","name of someone else"

If anyone can get me going in the right direction or if I haven't explained myself well, please tell me.

Can I do an LS using a wildcard and then process each of the files it returns 1 by 1?

kirby14 · 02-17-2007

So far I have something along these lines:

Code:

$a = get-content "c:\PS\letter_env_20070217*"
foreach ($i in $a)
{Add-Content C:\PS\newoldtest.csv ($i.Split(",")[1] + ",")}

Main problem is that it sends a CrLf after each one, is there any way to suppress that?

02-17-2007

I had similiar problem, I was able to merge two CSV file atleast results.
But formattting would require more work.
You can check my blog at http://techstarts.blogspot.com/search/label/HotFix

Hope you find useful.

Thanks,
Techstarts

"kirby14" <kirby14.2m69pf@no-mx.forums.net> wrote in message
news:kirby14.2m69pf@no-mx.forums.net...
>
> So far I have something along these lines:
>
>
> Code:
> --------------------
> $a = get-content "c:\PS\letter_env_20070217*"
> foreach ($i in $a)
> {Add-Content C:\PS\newoldtest.csv ($i.Split(",")[1] + ",")}
> --------------------
>
>
> --
> kirby14

kirby14 · 02-17-2007

In case it helps any, here is the main piece of VBScript code I'm using for this:

Code:

Set objReadFile = objFSO.OpenTextFile (strReadDirectory & "\" & ObjFSO.GetFileName(File), ForReading) 

    Do Until objReadFile.AtEndOfStream
        strNextLine = objReadFile.Readline
        arLine = Split(strNextLine , chr(34))

        If (i < 2) Then
            objWriteFile.Write(", " & Trim(arLine(3)))
            i = i + 1
        Else
            objWriteFile.WriteLine(", " & Trim(arLine(3)))
            i = 0
        End If
    Loop

kirby14 · 02-18-2007

Code:

If (Test-Path($strFileNameSearch))
    {
    $a = get-content $strFileNameSearch

    $y = 0
    for ($x = 0; $x -lt $a.length; $x++){
        if ($y -lt 3){
            $names += "," + $a[$x].split("`"")[3]
            $y +=1}
        else{
            $names += "`n"
            $y = 0}
    }
    add-content $strWritePath $names
}

This code works for me

(note - this is just out of testing stages, I realize variable names are awful and such)

kirby14 · 02-18-2007

Code:

If (Test-Path($strFileNameSearch))
    {
    $a = get-content $strFileNameSearch

    $y = 0
    for ($x = 0; $x -lt $a.length; $x++){
        if ($y -lt 2){
            $names += "," + $a[$x].split("`"")[3].Trim()
            $y +=1}
        else{
            $names += += "," + $a[$x].split("`"")[3].Trim() + "`n"
            $y = 0}
    }
    add-content $strWritePath $names}

Small Fix -

02-18-2007

You might also be interested in this series on my blog about using ADO and
dataTables for this :

http://mow001.blogspot.com/2006/03/w...-part-one.html
http://mow001.blogspot.com/2006/03/w...-part-two.html
http://mow001.blogspot.com/2006/04/m...-more-csv.html

Greetings /\/\o\/\/
http://thePowerShellGuy.com

"kirby14" <kirby14.2m6uqn@no-mx.forums.net> wrote in message
news:kirby14.2m6uqn@no-mx.forums.net...
>
> Code:
> --------------------
> $a = get-content "c:\PS\letter_env_20070217*"
>
> $y = 0
> for ($x = 0; $x -lt $a.length; $x++){
> if ($y -lt 3){
> $names += "," + $a[$x].split("`"")[3]
> $y +=1}
> else{
> $names += "`n"
> $y = 0}
> }
> add-content "c:\PS\test123.csv" $names
> }
> --------------------
> This code works for me

>
> (note - this is just out of testing stages, I realize variable names
> are awful and such)
>
>
> --
> kirby14

kirby14 · 02-19-2007

	Quote:	/\\/\\o\\/\\/ [MVP]
	You might also be interested in this series on my blog about using ADO and dataTables for this : http://mow001.blogspot.com/2006/03/w...-part-one.html http://mow001.blogspot.com/2006/03/w...-part-two.html http://mow001.blogspot.com/2006/04/m...-more-csv.html Greetings /\/\o\/\/ http://thePowerShellGuy.com

Cool, very interesting read!

My final code:

Code:

If (Test-Path($strWritePath))
 {Output ("File " + $strWritePath + " already exists."); }
Else{
 $content = Get-Content $strFileNameSearch
 If($content.length -lt 1){Output ("No Files Found")}
 Else{
  $col = 0
  for ($i = 0; $i -lt $content.length; $i++)
  {
   If ($col -lt 2){$names += "," + $content[$i].split("`"")[3].Trim(); $col++}
   Else{$names += "," + $content[$i].split("`"")[3].Trim() + "`n"; $col = 0}
  }
 Add-Content $strWritePath ",,Place Text for Header"
 Add-Content $strWritePath $names
 
 }
}

02-17-2007	#1 (permalink)
kirby14 Newbie Join Date: Feb 2007 7 posts Rep Power: 13	Merge Multiple CSV's I know how to do it in VBScript, but I'm wondering if it can be done better in PS. I want to search through a directory for all files named "blah_blah_<date>*" Then I want to take all of thse files and place the 2nd column into a new CSV with 3 columns. IE. blah_blah_20050217_test.csv (lots of these) "foo","name of someone","bar" New_CSV.csv "name of someone","name of someone else","name of someone else" "name of someone else","name of someone else","name of someone else" If anyone can get me going in the right direction or if I haven't explained myself well, please tell me. Can I do an LS using a wildcard and then process each of the files it returns 1 by 1?
My System Specs

02-17-2007	#2 (permalink)
kirby14 Newbie Join Date: Feb 2007 7 posts Rep Power: 13	So far I have something along these lines: Code: $a = get-content "c:\PS\letter_env_20070217*" foreach ($i in $a) {Add-Content C:\PS\newoldtest.csv ($i.Split(",")[1] + ",")} Main problem is that it sends a CrLf after each one, is there any way to suppress that?
My System Specs

02-17-2007	#3 (permalink)
Techstarts Guest n/a posts	Re: Merge Multiple CSV's I had similiar problem, I was able to merge two CSV file atleast results. But formattting would require more work. You can check my blog at http://techstarts.blogspot.com/search/label/HotFix Hope you find useful. Thanks, Techstarts "kirby14" <kirby14.2m69pf@no-mx.forums.net> wrote in message news:kirby14.2m69pf@no-mx.forums.net... > > So far I have something along these lines: > > > Code: > -------------------- > $a = get-content "c:\PS\letter_env_20070217*" > foreach ($i in $a) > {Add-Content C:\PS\newoldtest.csv ($i.Split(",")[1] + ",")} > -------------------- > > > -- > kirby14
My System Specs

02-18-2007	#5 (permalink)
kirby14 Newbie Join Date: Feb 2007 7 posts Rep Power: 13	Code: If (Test-Path($strFileNameSearch)) { $a = get-content $strFileNameSearch $y = 0 for ($x = 0; $x -lt $a.length; $x++){ if ($y -lt 3){ $names += "," + $a[$x].split("`"")[3] $y +=1} else{ $names += "`n" $y = 0} } add-content $strWritePath $names } This code works for me (note - this is just out of testing stages, I realize variable names are awful and such) Last edited by kirby14; 02-18-2007 at 01:02 AM.
My System Specs

02-18-2007	#7 (permalink)
/\\/\\o\\/\\/ [MVP] Guest n/a posts	Re: Merge Multiple CSV's You might also be interested in this series on my blog about using ADO and dataTables for this : http://mow001.blogspot.com/2006/03/w...-part-one.html http://mow001.blogspot.com/2006/03/w...-part-two.html http://mow001.blogspot.com/2006/04/m...-more-csv.html Greetings /\/\o\/\/ http://thePowerShellGuy.com "kirby14" <kirby14.2m6uqn@no-mx.forums.net> wrote in message news:kirby14.2m6uqn@no-mx.forums.net... > > Code: > -------------------- > $a = get-content "c:\PS\letter_env_20070217*" > > $y = 0 > for ($x = 0; $x -lt $a.length; $x++){ > if ($y -lt 3){ > $names += "," + $a[$x].split("`"")[3] > $y +=1} > else{ > $names += "`n" > $y = 0} > } > add-content "c:\PS\test123.csv" $names > } > -------------------- > This code works for me > > (note - this is just out of testing stages, I realize variable names > are awful and such) > > > -- > kirby14
My System Specs

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail merge	weenis	Live Mail	1	05-30-2008 03:00 PM
Merge one bitmap onto another in vb	Jon Jacobs	.NET General	0	05-27-2008 11:41 AM
merge directories	marty	Vista General	2	10-06-2007 11:28 AM
merge directories	marty	Vista General	0	10-05-2007 01:09 PM
Merge Images	Raghavendra	Avalon	3	10-27-2006 01:10 AM