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!

---

Source and test results, including run time (5 rows)

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