Deconstructing Imported Data

[krdzine1]

BACKGROUND: I am trying to build a database whose primary function is to import an excel spreadsheet of items to be inlcuded in a catalog. Currently the list includes every individual item as a seperate record and item even though it may be listed as 1 catalog entry with 5 colors and 10 sizes available. I manually have to strip out the redundant information and compile them into a single entry before creating my text document for layout. WASTE OF TIME and opportunities for error. Because each item is listed with an individual part number (ie., X12345-123, X12345-124, etc) rather than a part prefix (X12345) and a subpart/identifier (-123, -124) it's difficult to do this automatically... at least yet.

QUESTION:

Part1: Is there a way for FM to read the field imported and auto extract the prefix and subparts to matching fields? The problem I have is there is no set pattern to how the full #s are composed. Some are #####-###, others are ######-#####... they are all over the place but seem to all have the hyphen as a seperator. I tried the LEFT and RIGHT but they don't work because of the inconsistent patterns. Can I use the hyphen?

Part2: Can FM find/read all things that all start the same and pull the first record in as the Primary Product and then pull the subsequent items into a SubPart table?

Edited October 14, 2005 by Guest

[Sanjai]

1. You can make use of hyphen. You can make use of text functions like middle, middlewords etc.

2. You can import all the records from excel. The get a foundset of the subparts, delete the sub parts from the primary product table after exporting the foundset into the subpart table.

[Lee Smith]

You could also change the data in a Text Editor doing a search for the pattern and replace with a tab.

From your example, there is a pattern, it just isn't consistent in how many digits.

If you don't have BBEdit, or TextWrangler, do yourself a favor and download TextWrangler from Click Here it's [color:red]FREE.

And you could use a Find and Replace for your pattern (i.e. X12345-123) , described as an Alpha Character follow by numerical digits, a hyphen, and then more numerical digits, would be:

Find:

([A-Z][d]+)-([d]+)

Replace:

1t2

HTH

Lee

p.s., if the pattern is different, such no Alpha character, let me know.

Edited October 14, 2005 by Guest
add p.s.

[krdzine1]

How would that calculation or script be written? What I have tried has not worked.

[krdzine1]

Here's an example of the data I am importing. "FormattedItemNumber" is the field I'd like to seperate into 2 fields in FM. The information preceding the hyphen and after. The field "F3" also contains data I want seperated into 2. Again... info before and after a hyphen (or space).

SAMPLE.xls.zip

Edited October 31, 2005 by Guest
forgot file.

[comment]

PartPrefix =

Left ( PartNumber ; Position ( PartNumber ; "-" ; 1 ; 1 ) - 1 )

SubPart =

Right ( PartNumber ; Length ( PartNumber ) - Position ( PartNumber ; "-" ; 1 ; 1 ) )

[LaRetta]

Well, you have the best help. But I wanted to point out that most of this format change can happen during your import.

The first field will import the entire Excel field FormattedItemNumbers. Two new fields (ItemNumber and SubPart) take the values from there. This saves having to apply a calculation against them once imported via script or Replace Contents.

BTW, I approached the calc a bit differently in this instance. I simply changed the dash to a space and used LeftWords(), RightWords() during the import. Why? It was simpler. I thought it might be easier for you to apply against your other similar fields (during the rest of your migration), since you indicated you're Beginner. Comment's calc is more flexible in that it shows how to use a character as an ANCHOR and split accordingly. My example seemed easier to grok but Michael's calc should be MEMORIZED by anyone text parsing at all.

Whichever calc you use, if you apply during import, remember to 'Perform Auto-Enter' during import.

LaRetta

Parts.zip

Edited October 31, 2005 by Guest

[krdzine1]

Very interesting... thanks very much for everyone's input. Both options are helpful and good education. I'll see how these work and let you know. Thanks.