A Windows script to merge three csv file columns into one single column

Question

I can do this within excel for example using the =CONCATENATE function to merge a number of columns into one single column. But i want to do is merge columns within 3 different csv files within the same folder into one single column. I want to run this via batch script so something like a VBScript the CMD copy command does not seem to work.

Here is the file structure:

File1.csv

• Column1: www.domain.com/
• Column2: www.nwdomain.com/
• Column3: www.stackdomain.com/
• Column4: www.example-domain.com/

File2.csv

• Column1: about
• Column2: contact
• Column3: index
• Column4: faq

File3.csv

• Column1: .html
• Column2: .html
• Column3: .html
• Column4: .html

Result in output file:

• Column1: www.domain.com/about.html

• Column2: www.nwdomain.com/contact.html

• Column3: www.stackdomain.com/index.html

• Column4: www.example-domain.com/faq.html

Thanks for your help.

Answer 1

@ECHO OFF
SETLOCAL
::
(
FOR /f "tokens=1*delims=:" %%a IN ('findstr /n /r "." ^<csv1.csv') DO (
 FOR /f "tokens=1*delims=:" %%c IN ('findstr /n /r "." ^<csv2.csv') DO ( 
  IF %%a==%%c FOR /f "tokens=1*delims=:" %%e IN ('findstr /n /r "." ^<csv3.csv') DO (
   IF %%a==%%e (
    FOR /f "tokens=1-4delims=," %%m IN ("%%b") DO (
     FOR /f "tokens=1-4delims=," %%r IN ("%%d") DO (
      FOR /f "tokens=1-4delims=," %%w IN ("%%f") DO (
       ECHO.%%m%%r%%w,%%n%%s%%x,%%o%%t%%y,%%p%%u%%z
      )
     )
    )
   )   
  )
 )
)
)>new.csv

should work.

What it does is,

1. For file1, FINDSTR "outputs" any line which contains any character (/r ".") preceded by the line number and a colon (/n). This "output" is read by the FOR /f and parsed into 2 tokens, delimited by the colon (tokens=1* means 'the first token;all of the rest of the line') and the effect is to put the line number in %%a and the rest of the line, which is the line from the original .csv into %%b
2. FOR EACH LINE of csv1Repeat for csv2, this time placing the line number in %%c, line in %%d
3. Only if the line numbers match, repeat for csv3 with the number in %%e and text in %%f
4. If the line number from this last file matches, parse the line text in each of %%b, %%d and %%f - this time selecting the four columns, separated by commas. This data appears in %%m..%%p, %%r..%%u, %%w..%%z All we have to do then is butt-up the appropriate parts and insert the commas.

DONE!

---

start:21:45:40.87
end  :21:45:41.09

csv1.csv =========
www.domain.com/,www.nwdomain.com/,www.stackdomain.com/,www.example-domain.com/
www.domain.com/,www.nwdomain.com/,www.stackdomain.com/,www.example-domain.com/
www.domain.com/,www.nwdomain.com/,www.stackdomain.com/,www.example-domain.com/
www.domain.com/,www.nwdomain.com/,www.stackdomain.com/,www.example-domain.com/
www.domain.com/,www.nwdomain.com/,www.stackdomain.com/,www.example-domain.com/
csv2.csv =========
about,contact,index,faq
about,contact,index,faq
about,contact,index,faq
about,contact,index,faq
about,contact,index,faq
csv3.csv =========
.html,.html,.html,.html
.html,.html,.html,.html
.html,.html,.html,.html
.html,.html,.html,.html
.html,.html,.html,.html
new.csv =========
www.domain.com/about.html,www.nwdomain.com/contact.html,www.stackdomain.com/index.html,www.example-domain.com/faq.html
www.domain.com/about.html,www.nwdomain.com/contact.html,www.stackdomain.com/index.html,www.example-domain.com/faq.html
www.domain.com/about.html,www.nwdomain.com/contact.html,www.stackdomain.com/index.html,www.example-domain.com/faq.html
www.domain.com/about.html,www.nwdomain.com/contact.html,www.stackdomain.com/index.html,www.example-domain.com/faq.html
www.domain.com/about.html,www.nwdomain.com/contact.html,www.stackdomain.com/index.html,www.example-domain.com/faq.html
=============

Answer 2

In VBScript:

Const delim = ","

Set fso = CreateObject("Scripting.FileSystemObject")

Set f1 = fso.OpenTextFile("File1.csv")
Set f2 = fso.OpenTextFile("File2.csv")
Set f3 = fso.OpenTextFile("File3.csv")

Do Until f1.AtEndOfStream Or f2.AtEndOfStream Or f3.AtEndOfStream
  a1 = Split(f1.ReadLine, delim)
  a2 = Split(f2.ReadLine, delim)
  a3 = Split(f3.ReadLine, delim)

  n = Min(UBound(a1), UBound(a2), UBound(a3))
  Dim aout(n)

  For i = 0 To n
    aout(i) = a1(i) & a2(i) & a3(i)
  Next

  WScript.StdOut.WriteLine Join(aout, delim)
Loop

f1.Close
f2.Close
f3.Close

Function Min(a, b, c)
  If a<=b Then
    If c<a Then
      Min = c
    Else
      Min = a
    End If
  Else
    If c<b Then
      Min = c
    Else
      Min = b
    End If
  End If
End Function

Answer 3

Although not really programming, a quick and dirty way is to open all files in Excel, create a new XLS or XLSX file, then use this formula in the first cell of the newly created file:

=[File1.csv]File1!A1&[File2.csv]File2!A1&[File3.csv]File3!A1

where File1.csv, File2.csv, and File3.csv are your CSV files. Then drag across the columns / rows to apply the formula.