Line Feed Character

[locator8]

Hi,

I tried this query in the Import/Export forum without success. Maybe someone here can help.

I have been unsuccessfully trying to upload a FM export (.csv doc) to a departmental web site. It would only upload the first line. I found that opening the export on an XP machine and re-saving with Line Feed character as well as carriage return fixed the problem. Can I get FM to insert this character into the export, or perhaps trigger an Apple script that will do it? It doesn't take long to do it manually but I am trying to make it a 1 buton process in FM because I don't always run the export.

Along the same lines, one of the import files always needs a header stripped out. I have had a go at getting Applescript to do this but without much luck although this would seem an Applescript Beginners Slope so I am giving away my inexperience, aren't I? Next step would be to have FM run the script before peforming the import, natch.

David

[Fenton]

I thought this was an interesting dilemma, so I made an example file to do it. It includes two entirely different approaches, AppleScript post-processing, and XML Export.

The problem comes from the fact that FileMaker (or practically any application without explicit cross-platform options) exports text in the native operating system format. It's rather annoying that each is different:

Mac: ASCII 13

Unix (and Mac OS 10): ASCII 10

Windows: ASCII 13 ASCII 10 (both)

To change Mac to Windows you just add ASCII 10. There are other encoding problems I imagine, but for most text this will do.

To do it with XSL you basically just build what you want. (My file is an adaptation of one of FileMaker's examples. Hence it has their various copyright comments.)

I built it first in FileMaker 7, then saw that you're using 6, so both versions are included. They can share the XSL and export text files. The only real difference is a slight change in the myFolder calculation.

I would imagine that the XSL is faster and more reliable with really large exports, but haven't tested.

CSV_Export_XSL.zip

Edited September 30, 2005 by Guest

[locator8]

Thanks Fenton. I have adapted it to my DB and it seems to work fine although it does generate an error which I can't trace back. I checked with your sample file and got the same error (attached) on the wAS script.

David

[Fenton]

Hmm? It works for me. But it sounds as if your version of AppleScript is not happy with setting the FileMaker field directly to a file specification; it was probably too hasty. So let's move that outside the FileMaker tell block. Try this, Perform AppleScript [

tell database 1

set theFile to cell "myFile"

end tell

tell application "Finder" -- needed inside FileMaker

set outputFile to theFile as file specification

set theText to read outputFile

set AppleScript's text item delimiters to {ASCII character 13}

set textList to text items of theText

set AppleScript's text item delimiters to {(ASCII character 13) & (ASCII character 10)}

set newText to (textList as text)

set AppleScript's text item delimiters to {""}

set eof of outputFile to 0

try

write newText to outputFile starting at eof

end try

end tell

[locator8]

Thanks for the help Fenton. Again, this seems to create a perfectly usable .csv file called MISuploadnew.csv in the directory TR on my USB drive UNTITLED. It replaces the file if it already exists but generates the message in the attachment.

David

[Luke44]

I am working on a similar problem: I would like to export dates from a Filemaker database (appointments) to Apple's iCal to see them in a nice calendar view.

In FM, I created a global field with the necessary iCal data structure and saved it to a text file from a FM script (the iCalendar file structure can be found here). However, It's not possible to directly subscribe to this calendar file from iCal as it is not a "Windows-Latin-1" encoded text file. If I open and save the file with the appropriate text encoding (e.g. in "TextEdit") everthing is fine. I thus wrote an Applescript to convert the file.

I wonder if I could adopt Fenton's script in FM to export a file with CR + LF line endings (Windows style) which would be ready for iCal to subscribe to. The Applescript step would be unnecessary then.

So far, the script does not produce a file accessible for iCal although the data structure is ok. Has anyone experienced similar problems?

Luke

[Fenton]

Yes, that should work. But my script Exports, then runs a Perform AppleScript step. So it is still AppleScript. It would be pretty much the same, except you'd just be exporting the 1 global field, for 1 record.

You could also do this conversion cross-platform, using an XSL stylesheet. But you'd have to recreate the iCal format (which is a little ugly, IMHO). You would do it from the individual fields themselves, not a "put together" global field. So you could put the correct line endings after each field (which would be an element). In XSL you tell it explicitly what to put between things; there is no "default" line ending (or separator).