darn repeating fields in text exports/imports

[stefangs]

hi all,

i'm trying to come up with a way to update .fp5 files to .fp7 and some of those old repeating fields are coming back to haunt me.

i have written scripts in the old .fp5 files that export all data to merge files. the new .fp7 file will hopefully read all data in automatically.

now i can't figure out what is happening to data that was in repeating fields. i need to have these fields again in the new file. after import is done, i'll eventually massage that data so the repeaters are gone forever.

one file has two repeating fields with three repetitions each and those came over fine. however, another file has 7 repeating fields with 14 repetitions and importing into that file pretty much hung my app for several minutes - i thought it had crashed but eventually it came back. i'm having a hard time looking at the text file, because there are just too many fields and commas.

do you know what happens to repeating fields in these export/import scenarios?

oh, and before you ask why i don't use standard FM files instead of text files - it's because someone else needs to do it, and they can't import a .fp5 file on a machine that only reads .fp7 files when they don't have developer access.

thanks,

stefan

[comment]

do you know what happens to repeating fields in these export/import scenarios?

In the exported file, the values of a repeating field are separated by the group separator character (ASCII 29).

[stefangs]

In the exported file, the values of a repeating field are separated by the group separator character (ASCII 29).

wow, never heard of that one. i looked it up and it is supposed to look like this: ^] - however, i can't find this in my exported text file anywhere. would the importing file know how to populate the repeating fields? anything to watch for? right now it doesn't work. of course, something else could be wrong - just trying to zero in on the problem.

thanks much,

stefan

[IdealData]

AFAIK you cannot import the repeating fields once they have been separated into 3rd party file formats - at least not without scripting the data to be rebuilt.

Why not convert your users fp5 file for them?

[comment]

i looked it up and it is supposed to look like this: ^]

It's not supposed to look like anything - it's an invisible character.

would the importing file know how to populate the repeating fields?

Yes. Didn't you say it worked with another file?

[stefangs]

AFAIK you cannot import the repeating fields once they have been separated into 3rd party file formats - at least not without scripting the data to be rebuilt.

Why not convert your users fp5 file for them?

too many users with too may files ;-)

stefan

[stefangs]

It's not supposed to look like anything - it's an invisible character.

Yes. Didn't you say it worked with another file?

ok, the invisible character explains that.

it worked with the same file, but only repeating fields with 3 repetitions were populated, the fields with 14 repetitions were not. perhaps i'll chicken out and come up with some 'not compatible blabla', but it would be nice if it worked.

btw, is there a way to prevent FMD6 renaming my field definitions in the merge file? it replaces spaces in the field name with underscores and so the importing file can not be field matched 'by name', unless i rename them all first.

thanks,

stefan

[comment]

only repeating fields with 3 repetitions were populated, the fields with 14 repetitions were not.

Well, that's something to investigate. I am not aware of any limit that would explain the difference.

is there a way to prevent FMD6 renaming my field definitions in the merge file?

You could build your own .mer file using XML/XSLT.

[stefangs]

Well, that's something to investigate. I am not aware of any limit that would explain the difference.

you're right, that is a little bizarre. i'll try it again and make sure everything else is correct and see what happens.

You could build your own .mer file using XML/XSLT.

beyond me, i'm afraid, but while you mention .mer file. is it ok to have a merge file with .txt extension? does it matter?

thanks,

stefan

[comment]

is it ok to have a merge file with .txt extension? does it matter?

Does it matter to whom?

[stefangs]

Does it matter to whom?

:-)

to the file that tries to import.

what i mean is this: afaik, a merge file is really just a csv textfile that has field names in the first line instead of data. so when i point to a merge file for importing, does it matter if that file is called filename.mer or filename.txt? or filename.csv, for that matter.

stefan

[comment]

I don't think it matters, but I suggest you test this yourself in the exact situation you describe by "point to a merge file for importing".

[IdealData]

A different approach...

Rather than try to export the repeating fields along with the rest of the fields from the same file how about another fp5 file to hold the repeating fields from all the other files, but each repetition to be held in a single field. So;

RepeatingField[14]

would become

RepeatingField1

RepeatingField2

..

RepeatingField14

Script the copy of the data in the reps to the new fp5

When you export from the existing fp5 files IGNORE the repeating fields

Provided your new fp7 suite is actually a converted version of the fp5 suite then you should be able to import from most file formats, although XLS is a likely favourite as you can match names more easily, and then reverse the copying of the repeating fields back to their original places.

[stefangs]

I don't think it matters, but I suggest you test this yourself in the exact situation you describe by "point to a merge file for importing".

i tried this already and it doesn't seem to matter at all. which makes me wonder why we have .txt, .tab, and .mer files to begin with. perhaps these are endings that are understood by some other software i don't know about, like in the windows world.

stefan

A different approach...

Rather than try to export the repeating fields along with the rest of the fields from the same file how about another fp5 file to hold the repeating fields from all the other files, but each repetition to be held in a single field. [...]

that's a very interesting approach. if i can't get the repeating thing to work properly, i will certainly give this a try, even though it will probably make my users wish me a perpetual soft-on.

thanks,

stefan

[stefangs]

so just for the record, i wanted to let you know that indeed all repeating fields were imported correctly. it must have something to do with my importing script, because when i import manually, then it work. when i use the import script, it doesn't. so that's something to work on. at least i know where to look now!

thanks again,

stefan