easier import

[jsaltzman]

If you looked at my last post in here called "brain fart" you will see that i had problems with the layout of a file i am importing. i finally got it to the right layout.

UR|100000 AA|Grisham, John TI|The Rainmaker CN|Fine MT|Fiction

now my problem is importing the fields into the correct fields in FM. The fields in my file are titled fortunatley

UR = sku AA= Author TI = Title etc...

The problem i have is sometimes it is missing the author or, one field is for keywords, and sometimes it has like 10 keywords so it will look like this...

UR|sku AA|author TI|title KE|cars KE|racing KE|tires PP|Publisher

i thought maybe i can make like 30 fields numbered 1-30,do an import, and run a script that will move the fields to the right spots based on title.

ie:

go to first field

loop

if [postion= {field1, "UR|"}]

set field [sku] [field1]

else

go to next field

end loop

then i would do that for every type of field that i have. any better ideas?

all06042003.zip

[Lee Smith]

Hi jsaltzman.

This probably should have been posted under your "Brain Fart" as a continuation of your question, however, I like this subject title better.

I have opened your file via BBEdit and then Excel and my first question is have you done any manipulation of the information to this point?

Lee

[jsaltzman]

yeah. first it was listed like this

UR|100038

AA|Hay, Louise L.

TI|You Can Heal Your Life

CN|Fine

MT|Non-Fiction

PU|Hay House, Incorporated

PP|Carlsbad, CA, U.S.A.

DP|1987

ED|Trade Paperback

BD|Minimal Wear Present

NT|0937611018 Like New - Trade Paperback - Minimal Wear Present -

KE|HEALING

KE|RELIGIOUS

KE|ASPECTS

KE|BODY

KE|MIND

KE|SPIRIT

KE|PRAYER

KE|SPIRITUAL

PR|3.33

XA|4

XB|1

XC|BO

XD|S

Each item is seperated by an empty line. i used EditPad Pro to change it to that format. As you see the item i listed here it has 8 keywords. keywords are the KE| lines. do you understand my problem?

[mse]

Could you please explain me what "sku" is stand for?

[Ugo DI LUCA]

Hi,

Well, this is not straightforward, but if your data is only a sample of a huge db, you could try this way....

I noticed that each field def starts with xxI which is 2 characters + I

Import the Excel spreadsheet to your FM db.

Create 24 additional calcs (as many fields definition) with this calc

c_matchfieldDefs= Substitute(field, "I", "I"& "paragraph") - stored

Then create a value list with only your starting 24 "xxI"

Put a global field "g_match "on the layout and populate it with the value list.

Create 24 relationships from the global to the fields.

Rel1 = g_match::URField, Rel 2 =AuthorField,...

The script, triggered for any value of your global field could look like (obviously not tested)

Loop

----If (IsValid (rel1)

-------Goto Related records (Rel1 show-only)

-------Loop

-------EndLoop after last

-------Goto Record (first)

-------If (Paterncount(URField, global =1)

-----------Set Field (URField, URField)

-------End If

-------Goto Record (next, end after last)

-------End Loop

----If (IsValid (rel2)

-------Goto Related records (Rel2 show-only)

.....

....

End Loop

[Ugo DI LUCA]

So, I've made a brief test on your files.

Saved the excel file to SYLK and imported it in a new FM db.

There are some spaces before the codes, so

change the calcs to :

c_matchfieldDefs= Substitute(Trim(field), "I", "I"& "paragraph") - stored for the right side of relationship

and

c_fieldandparagraph = Substitute(field, "I", "I"& "paragraph") - intermediate calc.

and

c_newfiedtopaste = Middle(c_fieldandparagraph;

Position(c_fieldandparagraph; "

[Lee Smith]

Hi jsaltzman,

Yes, I see your problem, and I'm working on a couple of ideas that may help. Unfortunately, I have a couple of things on my plate this AM, so I will not be able to get to this until this afternoon.

Lee

[Lee Smith]

Hi jsaltzman,

I'm running into problems because of the consistency in the fields. In other words, not every record has every field. By chance are you removing any fields because they don't have data in them, such as the "AI|" field?

Maybe it would help me to see the raw data (before you do anything to it).

Lee

[Ugo DI LUCA]

Hi Lee,

My understanding of the case suggested by jsaltzman is that he wants a script to restablish the consistancey of the fields.

A field with starting "xxI" would go in the "XXl " field even if it was priorly listed in the Excel Sheet in the colomn with many "xyI"...

That is why he started with a loop script, and I'm also convinced it is the way to go, though my suggestion seems extra complicated.

I run the test over some records and it appears to work though, but I won't test it with 24 fields, 24 relationships and 24 "Perform script" !

[Lee Smith]

hey Ugo, and good day to you.

My reading of this and his "Brain Fart" post leads to a little different interpretation of his needs. Plus I'm having fun fooling around with this one, I guess that is one reasons I'm trying to clear things up in my little pea brain. I really would like to see the original raw data so I can put all of my limited skills to work

Lee

[BobWeaver]

I have set up imports scripts like this in the past. I would be inclined to leave the import file in the one item per line format. Then import it that way into a single field RawData. Have two calculated fields: fieldID and Data.

fieldID = Left(RawData,2)

Data=Middle(RawData,4,64000)

Have a third field available called ImportRecord. After you do the import, set The ImportRecord field to "1" in all records in the found set. This lets you find and delete these records later. What I do next is loop through each import record and use the value of fieldID (your 2 char code) to determine which one of a number of globals fields to put the Data into. Then omit the record. When you encounter a blank line, then you are at the end of a record. Create a new record and copy the data from the globals into the regular fields. Then omit this record too. So, as you progress through the imported data, you process and omit until there are no records left. when you are done, do a find on field ImportRecord=1 and then delete the found set.

Regarding the keywords, I would put them all in the same field separated with "

[Lee Smith]

Hi Bob,

Did UGO share my file that sent him with you tonight?

Actually, I been playing with a file for a couple of days now that surprising enough follows a lot of what you suggest. I was getting ready to post it for jsaltzman, but I was waiting to here back from UGO as to what he thought of it.

It's ugly as sin, but I wasn't developing it for anything but my own learning curve, and to share with jsaltzman if it would help him.

Anyway, here it is and I hope it's useful to someone other than me.

Lee

ParseBookDown.zip

[Ugo DI LUCA]

Hi Lee,

Very good job.

[jsaltzman]

here is the 2 types of exports i can get. the txt file will list the books, when it starts a new itemit has the field BOOS| when it finishes that item it ends it with BOOE| you will see that in the file. the second file is a .msg that one seperates the items by a empty line.

What i am trying to do is export the info out of homebase (another database) and import it into filemaker so that can put them up on the web and etc...

To clear one thing up with LEE, the inconsistency is how Homebase does the export. if the item does not have something in the field it will not export it. The inconsistency is what my major problem is too.

export.zip

[Lee Smith]

Hi jsaltzman,

Thank you, now we have a complete picture of what you were dealing with. It is always more helpful to know as much as possible in a situation such as this.

The good news is, the file I attached to my last message will do what you want.

The .msg file is the easiest to deal with because it is already partially cleaned up.

Follow these steps and it will import correctly.

Open .msg file in text editor (since I don't know how TextPad works, I'm assuming you can do these steps as I do them in BBEdit.

Step 1.

Replace all of the Pipe | character with colon (should be a simple find and replace).

Step 2.

Replace all of the double returns ( rr ) with return, a double special a character and a return ( r

[jsaltzman]

THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!!

I appreciate your guys' help SO MUCH!

Do you have a suggestion on a book for me to read to learn filemaker better?

[jsaltzman]

Lee, one problem. The filed titled "NT|" is my comments field. I do all my data entry in Homebase, if the comments in homebase are too long (IE: 60+ characters) it will make a second field in the MSG file. Basically it splits up the comments into 2 MT fields. Can you possibly modify the FP file to recognize that. Im not to savy with it as you can most likely tell. I would really appreciate it.

[Lee Smith]

Is there an example of this in what you have already sent?

If so, point me at the record. If not, send one to me.

Lee

[jsaltzman]

If you take a look at sku (UR) 100045 when it is in the parsing db it doesnt list all the info in the comments section (NT)

Take a look at the .msg and you will see it has two fields for "NT"

[Lee Smith]

Hi jsaltzman,

I was looking at this file yesterday and discovered that I had never updated it to include the extra NT: line. This one should do it for you.

Sorry, I guess I got distracted.

Lee

ParseBookDown.zip