[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-02-09
5
Medium Priority
?
413 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:remotetn
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:William Elliott
ID: 20859874
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
 
LVL 19

Expert Comment

by:William Elliott
ID: 20859879
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
 
LVL 19

Assisted Solution

by:William Elliott
William Elliott earned 2000 total points
ID: 20859887
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
 

Author Comment

by:remotetn
ID: 20861557
Thanks I will give it a shot.  From what I have seen in the list it is just 5 digit zipcodes.

0
 

Accepted Solution

by:
remotetn earned 0 total points
ID: 20863515
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

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

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

590 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