Database Text File Problem - Macro?

[Joe_Toz]

I am hoping that someone out there might be able to help or point me in the right direction. I currently have a very large text file (generated via Omnipage 15 Pro) of information that must be used to create a customer database. Ideally, the database would have the following fields:
Column1: Indicator (a plus or minus sign, numeric value, etc.)
Column2: Last Name
Column3: First Name
Column4: Year
Column5: Street Address
Column6: City
Column7: State
Column8: Zip
Column9: Phone #
The problem is that the text file is rather choppy and disjointed. The customer records are not uniform in length. Some do not contain enough information for all 9 fields. Most records have more than 1 line of information when opened in Excel. There is, however, some uniformity: the indicator (if any) is always first, followed by last name and then first name. Also, an r: should always precede the address information. The following is a sample of the file:

+WALDON, Mrs. Diana (Diana A. Davies); 1977; r: 555 Hazel Dr.,
Warner Robins, PA 31088, 555 542-7811
+WALDRON, Edwin W.; 1985; Flight Attnd., United Airlines; r: 556
NW 24th St., Miami Shrs., FL 33150;
johndoe@aol.com
WALDRON, Heidi L. (Heidi L. Scheeringa); 1980; r: POB 1234,
Helen, PA 30545
WALDRON, Helen; 1935 (See Ruth, Mrs. Helen)
4-WALDRON, Minerva (Min); 1945; Retired Transcriber/Operator,
State of Florida; r: 557 S. K St., Lake Worth, FL 33460, 55712-
5183
WALENIUS, Mrs. Laura J. (Laura Jayne Doyle); 1961; r: 555
Ridge St., Lake Worth, FL 33460, 555 555-5873
WALENIUS, Richard S.; 1977; r: 5567 S. 9th Ave., Lake Worth,
FL 33461, 555 555-2726
WALKDEN, Arthur B.; 1965; r: 5568 Lake Ave., Lake Worth, FL
33460, 555 555-1889
WALKER, Cassondra; 2000; r: 5569 S. C St., Lake Worth, FL
33460, 555 555-6154
4-WALKER, Cecil; 1954; Retired; r 1234 Jog Rd, Lake Worth, FL
33467, 555 565-6660; Lois; Dale, Keith, Cynthia
WALKER, Charles L.; 1979; r: 123 S B St., Lake Worth, FL 33460
WALKER, David M.; 1969; r: 5570 Dunford Rd., Westlake, OH
44145
4-WALKER, Jeffrey; 1970; Admin., Univ. of Notre Dame; r: 55701
Ray Dr., Granger, IN 46530, 555 555-7541; Shari; Erin, Nathan,
Tim;
johndoe@nd.edu
+WALKER, Jeremy H.; 1991; Maintenance Worker II, Palm Beach
Cnty.; r: 55072 24th St. N, Royal Palm Bch., FL 33412, 555 555-
7311;
johndoe@juno.com
4-WALKER, Lillie (Lillie H. Robinson); 1979; Min ister/Ofc. Mgr.,
Christian Come Alive, 561 369-0950; r: 1234 Fairgreen Rd, W.
Palm Bch., FL 33417, 555 555-8337; Timonthy; Latoya, Laquin-
ton, Timonthy Jr



Please let me know if there are any Macros (or other options) that might help.
Any assistance will be greatly appreciated.
Thanks!

 

[Joe_Toz]

Don,

I will give this a try. Many thanks for your time - I truly appreciate it! I will let you know how it goes.

Thanks again!
JOE

 

[Joe_Toz]

Don,

I tried running the Macro, but I am getting an error (Run time Error 5) on this command line:

tmpStr = Left(Range("C" & cnt), pntr - 1)

Do you have any suggestions?

Thanks,
JOE

 

[stdz]

Don,

I tried running the Macro, but I am getting an error (Run time Error 5) on this command line:

tmpStr = Left(Range("C" & cnt), pntr - 1)

Do you have any suggestions?

Thanks,
JOE

Hi Joe,

Yes, I forgot to tell you that you need to setup

numberOfRows = 19

at the beginning of the macro to the number of rows that you have ! Test snippet was 19.

Sorry about that !

Don't forget to replace the CSV before running the macro again !

Let me know how it goes !

Ciao,

Don.

 

[Joe_Toz]

Thanks again, Don. I will make the modification and try again. I will keep you posted.

JOE

 

[Joe_Toz]

Don,

I cleaned up my test sample to only have 19 rows (to match the macro value) but I am still getting the same error message at the same command point. For some reason, I can’t seem to get past this point of the code:

'copy string upto pointer-1 to sheet dbClean C ==> name
tmpStr = Left(Range("C" & cnt), pntr - 1)

The macro stops at the above point and I get the run time error message (invalid argument) message. Do you know what may be happening here?
Thanks again for helping!

JOE

 

[jindon]

Joe:

+WALDON, Mrs. Diana (Diana A. Davies); 1977; r: 555 Hazel Dr., Warner Robins, GA 31088, 555 555-7811
+WALDRON, Edwin W.; 1985; Flight Attnd., United Airlines; r: 555 NW 94th St., Miami Shrs., FL 33150;

Column1: Indicator (a plus or minus sign, numeric value, etc.)
Column2: Last Name
Column3: First Name
Column4: Year
Column5: Street Address
Column6: City
Column7: State
Column8: Zip
Column9: Phone #

I don't think the second line of data is following the field headings.
i.e. "Flight Attnd., United Airlines"

What do you want to do with it?

Don:
see 4th line

WALDRON, Helen; 1935 (See Ruth, Mrs. Helen)

hasn't got ":", this is giving the error on your code.

 

[Joe_Toz]

Hi,

Thanks for taking a look at this. I don't need to capture the employment/employer data. This can be ignored if it makes things easier.

 

[jindon]

I think it is not possible to find out the unneeded field(s),
unless is has certain logic.....

 

[jindon]

Can you check your real data if each line has ": r:" just before street address?

just enter

=FIND("r:",A1)

 

[Joe_Toz]

Each line of the real data has a semicolon space r: space preceding the address information as follows:

"; r: "