Parse a field

[M Enfroy]

How can I parse data from a field where the data I want to extract is
separated by spaces? (See sample data below). I would like to have one
field that contains all the data (as in sample below) and then other fields
where I store the extacted data, for example, store zip code data in a
separate zipcode field, store city in a separate city field, etc. I haven't
been able to figure this out using substring text functions. Is there some
way I could specify this by counting the spaces (i.e, extract data for fieldX
from fieldY by grabbing all data in between the 6th space and the 7th space).

Keep in mind that I am not a scripter/programmer. Here's a sample of the
actual data:

DOE, JOHN (248) 555-1212 (517) 555-1212
8/9/2004 From 11:00:00 AM to 2:00:00 PM Eastern
MyCorp
1000 Republic Drive
Anywhere MI 48126 United States
Hal Jones
313-555-1212
(e-mail address removed)
PRODUCTA--Demo
PRODUCTB--Demo

 

[Andrew Watt [MVP - InfoPath]]

Hi,

Can you explain more about your situation?

For example, what is your data held in? Access? SQL Server? XML?
Something else?

My initial reaction to the notion of having all the data in one field
at any stage is that it's a bad idea. Can you explain your purpose
here?

To enable some more meaningful suggestions to be made it would be
useful to know the task(s) you are trying to perform.

Andrew Watt
MVP - InfoPath

 

[M Enfroy]

Hi Andrew:

The data is a bunch of text that will be copied from an e-mail message and
pasted into a text field in the Infopath form. Within the same form, I'd
like to extract data from that field into multiple fields that contain
smaller subsets.

This is a short-term fix (4 months) until we work out some internal issues
so that we'll be able to grab the data from an external data source. This
short-term intent is so that users of the form will have less data to enter
manually. They'd simply copy/paste data into one field (as in the sample I
provided) and then the other fields will get populated. In the long run, we
will need the individual fields broken out so that we'll be able to
report/query effectively.

 

[Andrew Watt [MVP - InfoPath]]

You say you are not a programmer/scripter. That tends to limit your
possibilities quite a bit.

What is your intended data store for long term
storage/querying/reporting?

Is there any possibility you could get the data sent to you as XML?

If not, and the structure of the data was sufficiently predictable
then someone could write a program in any of an assortment of
languages to split the data using regular expressions.

Depending on circumstances there are lots of options. I think you will
need somebody to program a solution for you or simply accept that the
mundane copying and pasting for the interim period. Which solution you
go for will partly be determined by the volume of data that has to be
handled, I guess.

Andrew Watt
MVP - InfoPath

 

[Josh Bertsch [MSFT]]

You could do _some_ data manipulation using XPath functions in calculations
or rules.

Check out the available functions at
http://www.w3schools.com/xpath/xpath_functions.asp

Using a combination of functions like substring-after(), substring-before(),
etc is an extreme hack but may help.

--josh bertsch