Importing from excel

[Rodriggo]

How can I import data from excel to filemaker 5.5, which is supposed to fit into repeating fields? The excell column has cells with data to be imported into repeating fields of one f.m. record.

Thanks.

[Wim Decorte]

you can't do it directly. You'll need to export from excel and pre-process the files so that the delimiter between what needs to go in the repeating fields is what FM expects.

But it's probably better to rethink your FM structure and avoid repeating fields in this case. What's the data?

[Rodriggo]

FM file is for law bulletin, gathering monthly data since 1994. Each record ranges around 40 repeating fields using data of the same kind. I'm afraid cannot modify the document. So I should export the data from FM to Excell, thus delimiting the spread sheet and reusing it with new data?

[Wim Decorte]

Exporting from FM to excel and then using that excel workbook to put new data in won't help.

FM expects to find the Ascii 29 character between field repetitions (Group Separator character) but Excel doesn't know about that. Excel will give you normal field separator between cells on the same row and a normal end-of-record delimiter between rows. So you need to manually (or automated through VBscript/applescript) insert that Ascii 29 character where it belongs, after you export from Excel but before you import into FM.

Or change the FM file and do away with the repetitions, which is probably the best way to go in the long run.

[Rodriggo]

Many thanks. I'd rather keep FM just as it is, otherwise, the 140 aprox. records will multiply by 40 at least. Is there any other option using word or other software for importing repeating fields?

[Wim Decorte]

Well yes you would have more records but the repeats would be in another file as line items. In the end: much more manageable.

Word: not a real solution, except that you probably can use the VBA in Word to insert the Ascii 29 characters in the right spot. You really are stuck with that ascii 29 problem if you want to import in repeating fields I'm afraid.

[Rodriggo]

I've tried to import repeting fields from excel and word without success. I used Ascii 29 (square and right parenthesis). When importing, FM does not discriminate repeating data and in some cases when importing from a word, memory isn't enough.

I would appreciate your help again.

[Brudderman]

I've tried to import repeting fields from excel and word without success. I used Ascii 29 (square and right parenthesis).

You don't give any details of exactly how you tried to get the ascii 29 in there, but that would help in trying to get the problem solved.

Here's how I would try it, though. Say you have repeating values in cells A1, B1 and C1 in an Excel sheet that all need to go into a single repeating field in FM. Go to an empty cell (say F1) and put in a formula like this: =A1&CHAR(29)&B1&CHAR(29)&C1. This will concatenate your repeating values with ascii 29 into a single cell (F1 in this example).

Then you will need to copy the formula down to all data rows on the import sheet. Then copy that column with the formulas and Paste Special (Values) to get rid of the formula and replace with just text.

Then try your import by mapping the newly created column into the repeating field.

James

[Rodriggo]

I used ascci 29 hex, which is right parenthesis )

I copied a square symbol, but it didn't work

I used the right parenthesis in excel and word. Indeed I would very much rather import from word or whatever easier. I just typed the symbol in between the data.

Hope this is enough detail. Many thanks

[comment]

That's 29 DECIMAL (group separator), not 29 hex (right parenthesis).

[Brudderman]

I just typed the symbol in between the data.

You can type the character in Windows, I believe, with ALT-0029, but the numbers must be from the numeric keypad on the right-hand side of the keyboard. Then you can copy and paste, but the formula approach in Excel is a lot more efficient.

Edited November 23, 2006 by Guest

[Rodriggo]

Sorry, but the symbol table says:

Ascii (hex) 29 for right parenthesis

Ascii (dec) 41 for right parenthesis

Please which key is supposed to introduce a group separator?

[Brudderman]

I don't believe that either of my suggestions will work as I've outlined them. What you might try is exporting some repeating data FROM FM into whatever kind of file you want to import from. Then see if you can import back INTO FM properly. If so, you should be able to copy and paste the delimiter into your actual data and then import it.

[Rodriggo]

Many thanks. It's working out that way and using word.

[Brudderman]

Good! Glad you got it going. I'll take another look and see why my Excel approach didn't work.