Importing from word into Mac FM5 into fields with value lists

[gosselin]

I almost have this working.

Here's the background. My wife designed a survey, and collected all the data and put it into Excel. Then, she wanted to import this data into FileMaker and develop reports (actually, have me develop reports).

So, I created a database, with several fields that had value lists. After all, this was what the survey looked like: a question, followed by several choices that could be checked, and more than one could be checked in each field. Then, I manipulated the data in the Excel spreadsheet to look like what I had exported, and then opened it in Word to create all the tabs, soft returns, and hard returns as needed. Then, I opened FM5, and went to import the file.

FM5 couldn't find it.

Not to be deterred, I went back and saved the file as text only, and then went back to FM5.

Seems that the text only converted all the soft returns to hard returns.

Back to a saved version, and then checking the FM5 help, I saw that when exporting, the records were separated by hard returns, and the entries in the field were separated by ASCII 29. So, I changed all the soft returns to ASCII 29, saved as text again, and tried the import.

It came up with the correct number of entries. But, it did not import more than one reponse per field. So, if I had field one, and the value list has value 1 through value 5, and I had values 2 and 3 listed, it would only show value 2 in the FM5 file.

See what I mean by almost?

Anyway, are there any bright ideas as to how to get multiple responses in a field into the field defined by value lists?

Thanks.

Michael Gosselin

[BobWeaver]

Go back and change your soft returns or ascii 29's into some other viewable character that doesn't appear anywhere else in the document. My favourite is "~". Then, import the file into Filemaker. Go to the field with the value list items and use the Replace command to replace the contents with the following calculated value:

ThisField=Substitute(ThisField,"~","

[Vaughan]

Bob's solution is a good one.

The problem is that "import" only imports data, not formatting. And the import file must be in a strick delimited format for the database to work out where the fields and records are.

Your insertion of tabs and paragraph breaks screwed it all up. The trick is to import the data, then do the formatting in the database (or more correctly, on the layout).

If Bob's suggestion doesn't work, you're going to have to scrap that file and import the (unmolested) data again.

[gosselin]

Thanks, Bob and Vaughan. I'll try it tonight, or over the weekend, and se how it works.

In the future, what is the best way to import a list of values? For example, using the change in the field so that we change "~" to paragraph marks, would it be possible to import a list or response numbers (e.g., "1,2,5") and convert them into formatted output? Also, in terms of ease of storage and retrieval, is it better to have text, or numeric values translated when formatted?

Thanks again.

------------------

Michael Gosselin

[Vaughan]

FMP 5 can import Excel spreadsheets and convert them into databases in one step! I'd start with that.

Even better, enter the data directly into the FMP database straight off.

[gosselin]

Okay, I tried the Replace command, using the Substitute calculated value.

Every field returned "0".

But wait!!

I was actually doing a COMPARE (because, well, I took the string literally).

When I Just calculate, it works!!

WOO HOO!

[This message has been edited by gosselin (edited February 15, 2001).]