Convert labels file back to a table

[Scott Axton]

Hi everyone �

I�ve searched and searched and can�t find any similar threads so hopefully someone can either help me out or point me in the right direction.

I have had 3 different clients of that want me to convert their data to �something more usable�.

I�m thinking (and actually said) �no problem� � until I got into actually trying to get the job done. I need to convert each label in to a record and then parse the information in to fields

The common denominator here between all 3 clients is that all of their mailing lists are in a �labels� type format. Two are in MS Word and the other is MS Works. They actually just typed the address labels directly into the label. They are all in 3 across and 10 down � Avery 5160 format.
Most of the addresses are three lines but all the files have labels with up to 6 lines in the labels �mixed in�.

I�ve tried saving as plain text, trying to cut and paste to a spread sheet, and a bunch of other things and just can�t get it right.

I would really appreciate any ideas anyone might be able to pass my way.
There is about 12,000 labels total between all of the files from the best that I can tell and I could really use some help saving face.

Thanks!

Scott

[Al Buchholz]

Re: Convert labels file back to a table

Sounds like a job for some xbasic character string manipulation routines.

Probably not too pretty, but could be done..

[Stan Mathews]

Re: Convert labels file back to a table

Post a representative sample of the data for us to experiment?

[Scott Axton]

Re: Convert labels file back to a table

Post a representative sample of the data for us to experiment?

Hi Stan -

You beat me to it. I was actually experimenting here to figure out how to do that.

Attached is a Word doc like I have. If the names look familar they are -
I mereged the data from Alpha Sports to create labels like those in the files I have. The disadvantage I have in the real labels I have as I mentioned is that they typed the data directly into the labels.

Hope this helps - Thanks for looking.

Scott

Later: I should mention that the issue is more getting each label out as a record as much as anything. I don't have a problem with parsing out each line into fields.

[Stan Mathews]

Re: Convert labels file back to a table

Assuming you can create a word document from the client data like you did this.....

Open the merged document in word.
Select all and copy.
Open excel blank workbook.
Paste
Size columns as needed.
Add field titles.

You can then save as a dbf and parse the records there to another table or ole to the excel file and create records in another table from the file.

[martinwcole]

Re: Convert labels file back to a table

Is the data on paper? or in a file?

[Scott Axton]

Re: Convert labels file back to a table

Assuming you can create a word document from the client data like you did this.....

Open the merged document in word.
Select all and copy.
Open excel blank workbook.
Paste
Size columns as needed.
Add field titles.

You can then save as a dbf and parse the records there to another table or ole to the excel file and create records in another table from the file.

Stan -

I tried this before I posted this topic. The problem is determining where the records break due to the number of lines per label not being consistant. It is one thing to look at three pages of records to compare back and forth. It is a whole different thing when I'm looking at doing 400 pages worth of labels.

When you do it this way, as you can see in the example each line of the label is split in to a new cell going down. Even if I save it off as a .dbf how would you determine where the records start and stop (programatically)?

Is the data on paper? or in a file?

Martin -

I have the actual data files. Each file is just in a "table" like shown in the example I posted.

[Stan Mathews]

Re: Convert labels file back to a table

Empty records delimit records to be parsed.

I started on a script that still has minor issues, just working through the first field (some records were duplicated)>

dim org_tbl as P
dim rsl_tbl as P
rsl_tbl = table.open("reslt")
org_tbl = table.open("parse")
org_tbl.index_primary_put()
org_tbl.fetch_first()
strt_rec = org_tbl.recno()
while .not. org_tbl.fetch_eof()
	line_ctr = 1
	while (.not. (alltrim(alltext_initial("parse")) = "")) .and. (.not. org_tbl.fetch_eof())
		select
		   case line_ctr = 1
		   to_rslt1 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   		case line_ctr = 2
	   		to_rslt2 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   		case line_ctr = 3
	   		to_rslt3 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   		case line_ctr = 4
	   		to_rslt4 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   		case line_ctr = 5
	   		to_rslt5 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   		case line_ctr = 6
	   		to_rslt6 = org_tbl.Field1
	   		stop_rec = org_tbl.recno()
	   	end select
	  org_tbl.fetch_next()
	  line_ctr = line_ctr + 1
	end while
		rsl_tbl.enter_begin()
		if eval_valid("to_rslt1")
		rsl_tbl.Rslt1 = to_rslt1
		end if
		if eval_valid("to_rslt2")
		rsl_tbl.Rslt2 = to_rslt2
		end if
		if eval_valid("to_rslt3")
		rsl_tbl.Rslt3 = to_rslt3
		end if
		if eval_valid("to_rslt4")
		rsl_tbl.Rslt4 = to_rslt4
		end if
		if eval_valid("to_rslt5")
		rsl_tbl.Rslt5 = to_rslt5
		end if
		if eval_valid("to_rslt6")
		rsl_tbl.Rslt6 = to_rslt6
		end if
		rsl_tbl.enter_end(.T.)
		org_tbl.fetch_next()
end while

rsl_tbl.close()
org_tbl.close()

I'll keep working on it as I have time. You might use it as a starting point.

[Eric Johnson]

Re: Convert labels file back to a table

Since he has the actual files, would it help any if he loaded them into MS Word and moved coulmns 2 & 3 so that instead of a 3x10 page they were all in a single column?

Eric

[Stan Mathews]

Re: Convert labels file back to a table

Or move them in excel but that would be too easy.

Improved script process all three fields. No duplicates but does create some blank records. Still under development. (assumes max 6 lines per label)

dim org_tbl as P
dim rsl_tbl as P
dim to_rslt1 as C
dim to_rslt2 as C
dim to_rslt3 as C
dim to_rslt4 as C
dim to_rslt5 as C
dim to_rslt6 as C
rsl_tbl = table.open("reslt",FILE_RW_EXCLUSIVE)
org_tbl = table.open("parse",FILE_RW_EXCLUSIVE)
org_tbl.index_primary_put()
'process field1
org_tbl.fetch_first()
WHILE .not. org_tbl.fetch_eof()
	line_ctr = 1
	WHILE (.not. (alltrim(alltext_initial("parse")) = "")) .and. (.not. org_tbl.fetch_eof())
		SELECT
			CASE line_ctr = 1
				to_rslt1 = org_tbl.Field1
				
			CASE line_ctr = 2
				to_rslt2 = org_tbl.Field1
				
			CASE line_ctr = 3
				to_rslt3 = org_tbl.Field1
				
			CASE line_ctr = 4
				to_rslt4 = org_tbl.Field1
				
			CASE line_ctr = 5
				to_rslt5 = org_tbl.Field1
				
			CASE line_ctr = 6
				to_rslt6 = org_tbl.Field1
				
		END SELECT
		org_tbl.fetch_next()
		line_ctr = line_ctr + 1
	END WHILE
	rsl_tbl.enter_begin()
	rsl_tbl.Rslt1 = to_rslt1
	rsl_tbl.Rslt2 = to_rslt2
	rsl_tbl.Rslt3 = to_rslt3
	rsl_tbl.Rslt4 = to_rslt4
	rsl_tbl.Rslt5 = to_rslt5
	rsl_tbl.Rslt6 = to_rslt6
	rsl_tbl.enter_end(.T.)
	WHILE (alltrim(alltext_initial("parse")) = "") .and. (.not. org_tbl.fetch_eof())
		org_tbl.fetch_next()
	END WHILE
	to_rslt1 = ""
	to_rslt2 = ""
	to_rslt3 = ""
	to_rslt4 = ""
	to_rslt5 = ""
	to_rslt6 = ""
END WHILE
'process field2
org_tbl.fetch_first()
WHILE .not. org_tbl.fetch_eof()
	line_ctr = 1
	WHILE (.not. (alltrim(alltext_initial("parse")) = "")) .and. (.not. org_tbl.fetch_eof())
		SELECT
			CASE line_ctr = 1
				to_rslt1 = org_tbl.Field2
				
			CASE line_ctr = 2
				to_rslt2 = org_tbl.Field2
				
			CASE line_ctr = 3
				to_rslt3 = org_tbl.Field2
				
			CASE line_ctr = 4
				to_rslt4 = org_tbl.Field2
				
			CASE line_ctr = 5
				to_rslt5 = org_tbl.Field2
				
			CASE line_ctr = 6
				to_rslt6 = org_tbl.Field2
				
		END SELECT
		org_tbl.fetch_next()
		line_ctr = line_ctr + 1
	END WHILE
	rsl_tbl.enter_begin()
	rsl_tbl.Rslt1 = to_rslt1
	rsl_tbl.Rslt2 = to_rslt2
	rsl_tbl.Rslt3 = to_rslt3
	rsl_tbl.Rslt4 = to_rslt4
	rsl_tbl.Rslt5 = to_rslt5
	rsl_tbl.Rslt6 = to_rslt6
	rsl_tbl.enter_end(.T.)
	WHILE (alltrim(alltext_initial("parse")) = "") .and. (.not. org_tbl.fetch_eof())
		org_tbl.fetch_next()
	END WHILE
	to_rslt1 = ""
	to_rslt2 = ""
	to_rslt3 = ""
	to_rslt4 = ""
	to_rslt5 = ""
	to_rslt6 = ""
END WHILE
'process field3
org_tbl.fetch_first()

WHILE .not. org_tbl.fetch_eof()
	line_ctr = 1
	WHILE (.not. (alltrim(alltext_initial("parse")) = "")) .and. (.not. org_tbl.fetch_eof())
		SELECT
			CASE line_ctr = 1
				to_rslt1 = org_tbl.Field3
				
			CASE line_ctr = 2
				to_rslt2 = org_tbl.Field3
				
			CASE line_ctr = 3
				to_rslt3 = org_tbl.Field3
				
			CASE line_ctr = 4
				to_rslt4 = org_tbl.Field3
				
			CASE line_ctr = 5
				to_rslt5 = org_tbl.Field3
				
			CASE line_ctr = 6
				to_rslt6 = org_tbl.Field3
				
		END SELECT
		org_tbl.fetch_next()
		line_ctr = line_ctr + 1
	END WHILE
	rsl_tbl.enter_begin()
	rsl_tbl.Rslt1 = to_rslt1
	rsl_tbl.Rslt2 = to_rslt2
	rsl_tbl.Rslt3 = to_rslt3
	rsl_tbl.Rslt4 = to_rslt4
	rsl_tbl.Rslt5 = to_rslt5
	rsl_tbl.Rslt6 = to_rslt6
	rsl_tbl.enter_end(.T.)
	WHILE (alltrim(alltext_initial("parse")) = "") .and. (.not. org_tbl.fetch_eof())
		org_tbl.fetch_next()
	END WHILE
	to_rslt1 = ""
	to_rslt2 = ""
	to_rslt3 = ""
	to_rslt4 = ""
	to_rslt5 = ""
	to_rslt6 = ""
END WHILE

rsl_tbl.close()
org_tbl.close()

The blank records are the two "unbalanced" blank entries in the final row of the original file. Not worth working out how to not create those. They can be filtered out of the result table.

[Scott Axton]

Re: Convert labels file back to a table

Or move them in excel but that would be too easy.

Now why didn't I think of that?

Improved script process all three fields. No duplicates but does create some blank records. Still under development. (assumes max 6 lines per label)

Goodness! I didn't expect you to do it for me! I'll look at this closer in a bit. Work has me burried at the moment. I'd rather play with this but unfortunately I have grown accustomed to eating so I guess I have to play that game first.

Back soon -

Scott

[Ton Spies]

Re: Convert labels file back to a table

Sorry for having some remarks.

Because of the privacy of your clienst names and addresses - I guess - you have taken an Alpha clienttable.
Making lables from a table results in a regular labelformat.
I doubt or this is the reality for the labels you have to convert, as you wrote that it is just typed into labels.
So without any check of format before.

I have some experience with this matter and I know how supprising the irregularities appear.
It's a big job that must be done step by step with trial and error before you have formatted all the data to the right fields.
The 80-20 rule is apply to this.
80% of the data takes 20% of your time and 20% of the data takes 80% of the time.
The most complicated adresses must be changed by hand.

I admire Stan's effort and I will follow this threat with much interest.
Good luck

Ton