Text import nightmare

[wintergreen]

I'm setting up a script to prompt the user for a text file and then import the data. That's all simple and good, except there's a problem: the text is return-delimited, but contains commas. For the sake of argument, assume it's a chat room log file.

So the text file contents will look like so:

AH: really good, but maybe later...

BP: Yes, I know what you mean.

etc...

What is set up to happen is for the import to take each line and create a new record (which has a single field for now) with all the text. This works fine, except FMP for some reason insists on trying to separate the text by commas. So far I've been unable to find any way around this behaviour.

Some caveats:

-this is on a windows system, so i can't use applescript

-it's for use in a commercial environment, so plugins are out of the question

-the files will always be named with a .txt extension

Any suggestions?

[RodSierra]

One way I've done these type files is to import the entire file into one global text field, then parse if from there using either set field or calculated results, but having said that, it is important to note that FM is notoriously slow at dealing with large files this way. If the file is small you can use this method, if large, then I'd rework it in a text editor prior to import.

Rod

[wintergreen]

import the entire file into one global text field

This is actually feasible, but how do i set the script to do this? do i just make the import field into a global field?

if large, then I'd rework it in a text editor prior to import.

Not possible unfortunately, that would require the end-users to do it, and that ain't gonna happen...

[RodSierra]

Go to Import select import folder, insure the text file is in an independent or unique folder, select in file type import text file, and match the global to the file, this wil dump the entire txt file into the global field.

Rod

[wintergreen]

The problem with that is that it once again places the onus on the end-user to do things correctly...

[wintergreen]

Although, thinking about it some more, I wonder if it might be possible to use the webviewer to get the contents of the file...

Edited February 21, 2007 by Guest
typo

[sbg2]

FileMaker defaults to .csv for text record imports. Is it possible for the original file to be saved with a .tab extension instead of .txt?

[Lee Smith]

I believe that FileMaker defaults to the last File Format used for "That File", otherwise it will default to "All Available". Once you have use the Tab Format, it should default to it until changed to a different Format. You could use a script for this and then it will default to that format, but it is a little clunky if you don't also choose the file.

HTH

Lee

[wintergreen]

the problem is that this also changes the file filter (at least on windows), to only show files with a .tab extension. Even worse, some of the text in question to be imported may contain erroneous tab characters, so this would cause the same problem. So far I'm having good results with the web viewer, i'll try to post more about this when i can

[sbg2]

Importing as a .csv/.txt and then Concatenating the fields with a comma inserted probably wouldn't even work. You would have to have more fields than possible commas per line of text. The .csv import would also strip quotes from the text (at least it did on my machine).

You might be able to do this with an XML import but thats out of my league. Unfortunately, I think the best way might be to edit the text outside of filemaker before importing (VBScript may allow you to automate this).

Its a shame that Filemaker does not yet support text imports or exports.

[Lee Smith]

Are you saying that if the file is a Tab file with a .csv extension on it, that it will be grayed if you select the Tab Format in the import window, and vice versa (.tab and comma format)?

[wintergreen]

After playing with using the web viewer to import text some more, it does seem to work as I want it to, except that each line of text renders 3 line break characters after every line. I've no idea what causes it, and worse, because these are not "standard" filemaker line breaks, they can't be filtered out very easily. the only option i have at this point is to try and filter out all empty lines i suppose

[wintergreen]

OK I'm talking nonsense... Actually the example file I was using was saved in unix formatted text and doesn't even display properly in notepad...