Check csv file for number or fields in each record and filter out

I have been given a txt file with users names and addresses. My problem is I imported it into excel and  went into the first column with a good record having 4 lines followed  by a blank line I got around that by exporting it to a csv file and tnen using multiline to export it to a record with 4 fields that went into Columns a,b,c,d.
Now I found out that some of the records don't have 4 lines but 3 lines. Is there anyway to add a line or sort out the 3 line records? There is about 20000 lines in the spreadsheet so it is quite large and I want to automate this.
Name, Address1, Address2, City Zip.
some of the records have only 3 lines in them eg Name, Address1, City Zip.
What is the easiest way to either sort the 3 line records or fix the records with only 3 lines.
here is an example of the file
test         first
4230 HARDING PIKE
SUITE 523
NASHVILLE       TN 37205

test         second
300 BOMAR HEIGHTS
AARON MED CNTR
COLUMBIA        KY 42728

test      third
2105 EDWARD CURD LANE

FRANKLIN        TN 37067
remotetnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

William ElliottSr Tech GuruCommented:
how does this work for you?
Const ForReading = 1
Const ForWriting = 2
 
 
strFileName = "C:\list.txt"
strnewFileName = "C:\list.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)
Set objFile2 = objFSO.OpenTextFile(strnewFileName, ForWriting, true)
strnewText = ", "
Do Until objFile.AtEndOfStream
strText = objFile.Readline
	if RegExpTest("[0-9]{5}", right(strText,5)) = true then 
		strnewText = strnewText & ", " & strText & vbcrlf
		objFile2.WriteLine strnewText
		strnewText = ""
	else
		strnewText = strnewText & ", " & strText
	end if
strText = ""
loop
objFile.Close
objFile2.Close
wscript.echo "done"
 
 
 
Function RegExpTest(patrn, strng)
   Dim regEx, Match, Matches   ' Create variable.
   Set regEx = New RegExp   ' Create a regular expression.
   regEx.Pattern = patrn   ' Set pattern.
   regEx.IgnoreCase = True   ' Set case insensitivity.
   regEx.Global = True   ' Set global applicability.
   Set Matches = regEx.Execute(strng)   ' Execute search.
   For Each Match in Matches   ' Iterate Matches collection.
	  if match.value <> "" then RegExpTest = true
   Next
 
End Function

Open in new window

0
William ElliottSr Tech GuruCommented:
i gues instructions would help

change these lines to match your initial filename, then the output csv file.

strFileName = "C:\list.txt"
strnewFileName = "C:\list.csv"

then save the script as parseme.vbs and double click on it
0
William ElliottSr Tech GuruCommented:
one thing to note... the script is assuming that you are using only 5 digit zipcodes.

if you have the 9 digit codes it may screw up the script.
0
remotetnAuthor Commented:
Thanks I will give it a shot.  From what I have seen in the list it is just 5 digit zipcodes.

0
remotetnAuthor Commented:
That worked great the only thing was I had blank lines in the csv, I used ultraedit32 to remove the blank lines and setup the access database.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.