We help IT Professionals succeed at work.

Script merge CSV files

fjkaykr11
fjkaykr11 asked
on
I have a few .CSV files that I want to merge together (sample files attached).
The 3 .CSV files have different fields (in a different order).
I only want to merge 3 common fields (email_address, state, zip) from the files into a 4th output .CSV file.
Any ideas on how to do this with either VBScript or Windows Batch?  
Thanks for any help.
File1.csv
File2.csv
File3.csv
Comment
Watch Question

NVITEnd-user support
Commented:
Make a .bat file of this:
set newcsv=merged.csv
echo email_address,State,zip>>%newcsv%
for /f "skip=1 tokens=6-11 delims=," %%a in (File1.csv) do echo %%a,%%e,%%f>>%newcsv%
for /f "skip=1 tokens=7-12 delims=," %%a in (File2.csv) do echo %%a,%%e,%%f>>%newcsv%
for /f "skip=1 tokens=4-9 delims=," %%a in (File3.csv) do echo %%a,%%e,%%f>>%newcsv%

Open in new window

Author

Commented:
@NewVillageIT thanks so much, it worked great!
I might have to alter the batch file a bit based on the other CSV files I need to work with.   So I am trying to interpret how the batch file determines the 3 fields to output
Could you please explain that part.   Thanks again
End-user support
Commented:
The first line makes the header:
echo email_address,State,zip>>%newcsv%

The next 3 are similar. The difference is where it starts to make the variables. e.g. for the first line...

skip=1 means to skip the first line, i.e. skip the header. We already have that covered.

tokens=6-11 means start our variable declarations at the 6th field, i.e. email_address. Then, make room for 6 variables 6 to 11. This means 6 becomes %%a, 7=%%b, 8=%%c, etc. As you can see, the numbers of each line per file1, file2, and file3 differ because the field location in each file differs.

delims=, means use the commas are field delimiters in file1.csv

Does that help?

for /f "skip=1 tokens=6-11 delims=," %%a in (File1.csv) do echo %%a,%%e,%%f>>%newcsv%
for /f "skip=1 tokens=7-12 delims=," %%a in (File2.csv) do echo %%a,%%e,%%f>>%newcsv%
for /f "skip=1 tokens=4-9 delims=," %%a in (File3.csv) do echo %%a,%%e,%%f>>%newcsv%

Author

Commented:
thank you very much for the great explanation.  I really appreciate it.
NVITEnd-user support

Commented:
You're very welcome. I enjoyed it. Have a great weekend...