[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

merge two csv files

Posted on 2014-08-01
2
Medium Priority
?
586 Views
Last Modified: 2014-08-01
I have two sample .csv files (attached).  I would like to see if there is a way I can merge them together.  The challenge is the data fields are not exactly the same in both files. The only data I want to include in the new merge file is State, Zip and Email address.  Any idea on how this can be done in either Windows Batch or VBScript?   Thanks for any info.
file1.csv
file2.csv
0
Comment
Question by:fjkaykr11
2 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 40235585
It depends on a few items on how we proceed.  I am assuming that you just want all the lines from the first file combined with all the lines from the second file with just the data from the appropriate columns.  If so, then give this script a try.  Copy the text into notepad and save as a .vbs file.  Update the correct fields and then you can run the script.

' ExpertExchange Question ID 28488990
' http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28488990.html
' Expert: ltlbearand3 [http://www.experts-exchange.com/M_2469312.html]
'
Dim objFSO, objReadFile, ObjNewFile, arrLine

' Create the File System Object for Reading and Writing Files.Count
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Create the New Merged File
' ********  UPDATED WITH THE CORRECT FILE NAME AND PATH *********
Set ObjNewFile = objFSO.CreateTextFile("W:\EE Test\Q_28488990 Merge two CSV Files\newfile.csv")


' Read from the first file
' ********  UPDATED WITH THE CORRECT FILE NAME AND PATH *********
Set objReadFile = objFSO.OpenTextFile ("W:\EE Test\Q_28488990 Merge two CSV Files\file1.csv", 1) ' 1 is for Reading

' Loop Through the file to find all the data
Do Until objReadFile.AtEndOfStream
	' Split this data into an array to read each column
    arrLine = Split(objReadFile.Readline, ",")
    
    ' Array is zero based.  Write out columns 4-6 into the new file
    objNewFile.WriteLine arrLine(3) & "," & arrLine(4) &  "," & arrLine(5)
Loop
objReadFile.Close

' Read from the first file
' ********  UPDATED WITH THE CORRECT FILE NAME AND PATH *********
Set objReadFile = objFSO.OpenTextFile ("W:\EE Test\Q_28488990 Merge two CSV Files\file2.csv", 1) ' 1 is for Reading

' We are skipping the first line as we don't want the header line again.
If not objReadFile.AtEndOfStream then objReadFile.Readline

' Loop Through the file to find all the data
Do Until objReadFile.AtEndOfStream
	' Split this data into an array to read each column
    arrLine = Split(objReadFile.Readline, ",")

    ' Array is zero based.  Write out columns 2-4 into the new file
    objNewFile.WriteLine arrLine(1) & "," & arrLine(2) &  "," & arrLine(3)
Loop

' Clean Up
objReadFile.Close
objNewFile.Close
Set objReadFile = nothing
Set ObjNewFile = nothing
Set objFSO = nothing

msgbox "Done"

wscript.quit

Open in new window

0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 40235602
@ltlbearand3, brilliant, it works perfect.  I have some other adjustments that I need to try to figure out. I will post another question if I get stuck.  Thanks so much.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question