Combining data from 2 csv files and create new one

Question

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.

What have you tried so far? SO is not a code-writing service. — alroc, Oct 24 '17 at 10:55

iRon · Answer 1 · 2017-10-24T19:41:03.510

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

score 0 · Answer 2 · answered Oct 24 '17 at 12:12

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?

score 0 · Answer 3 · answered Oct 24 '17 at 17:57

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

Combining data from 2 csv files and create new one

3 Answers3

Using Join-Object