CODEWITHSUNDEEP
powershellcsv
Charis
Charis

Reputation: 137

Combining data from 2 csv files and create new one

I have 2 csv files. The first has this column:

"www.test.com/"
"www.test2.com/"
"www.test3.com/"
"www.test4.com/"

and the second has this column:

"a"
"b"
"c"
"d"

Now i want to create a csv file with this form:

"www.test.com/a"
"www.test.com/b"
"www.test.com/c"
"www.test.com/d"
"www.test2.com/a"
"www.test2.com/b"
"www.test2.com/c"
"www.test2.com/d"
"www.test3.com/a"
"www.test3.com/b"
"www.test3.com/c"
"www.test3.com/d"
"www.test4.com/a"
"www.test4.com/b"
"www.test4.com/c"
"www.test4.com/d"

I want to create a new csv file compining data from both csv files in this way.

Upvotes: 0

Views: 103

Answers (3)

iRon
iRon

Reputation: 23830

Using Join-Object

(Performing a Join without -On (alias -Using) parameter will return a cross join, see: https://stackoverflow.com/a/45483110/1701026)

PS C:\> $Url | Join $Letter

Letter Url
------ ---
a      www.test.com/
b      www.test.com/
c      www.test.com/
d      www.test.com/
a      www.test2.com/
b      www.test2.com/
c      www.test2.com/
d      www.test2.com/
a      www.test3.com/
b      www.test3.com/
c      www.test3.com/
d      www.test3.com/
a      www.test4.com/
b      www.test4.com/
c      www.test4.com/
d      www.test4.com/

Note: Import-Csv requires headers in the .csv file or defined by the '-Header` Parameter (see: https://stackoverflow.com/a/20528112/1701026)

$Url = Import-Csv ".\URL.csv" -Header Url
$Letter  = Import-Csv ".\Letter.csv" -Header Letter

Upvotes: 1

Esperento57
Esperento57

Reputation: 17492

If you column into your respective csv file are for name ColumnURL and ColumnLetter, try this :

$ListURL=import-csv "C:\temp\URL.csv"
$ListLetter=import-csv "C:\temp\Letter.csv"

$ListURL | %{$CurrentURL=$_; $ListLetter | %{ "{0}{1}" -f $CurrentURL.ColumnURL, $_.ColumnLetter}}

Upvotes: 0

Ciaran McKenzie
Ciaran McKenzie

Reputation: 123

I think you need to write a VBA macro with nested for loops for this. The following would be the steps with no code, I may mock it up later if I have time.

In a macro on a blank spreadsheet.

  • put the contents of the first csv in a defined range "rng1"
  • put the contents of the second csv in a defined range "rng2"
  • start a counter for the rows in the active sheet at k=1.
  • for each value in the first range (test1.com)
  • for each value in the second range (a)
  • set the value of the k-th row in the active sheet to be rng1.value&rng2.value
  • increment k
  • end insideloop
  • end outsideloop

There's a solution hidden here somewhere. Have you tried anything yourself?

Upvotes: 0

Related Questions