export without carriage returns showing as squares

[FMWebschool]

I am finishing up a solution that needs to work with both PC and Mac. When I export from FileMaker (tab delimited) it inserts squares wherever there is a paragraph return. Do I need to substitute this with something? How can I export without this happening?

Thanks for your help!

[QuinTech]

Well, it depends -- what are you going to do with the data once it's exported? The export file will need some way to differentiate between "internal" newlines and "external" newlines. If it didn't, then any carriage return within a field would make it appear as if a new record was being started.

You could substitute "|" for "" before exporting, or just substitute something for the square symbol in whatever your destination program is.

HTH,

Jerry

[FMWebschool]

Thank you for your quick response. We are using the data as a PHP file and it cannot have special characters. Is there a way to do this without any characters at all without using a plug-in?

Thanks again.

[Fenton]

I thought FileMaker exported whatever "end of line" character your system used (though on a Mac it uses the older ASCII 13 instead of the newer Unix ASCII 10). But on Windows you should get both (ASCII 13 & ASCII 10). So, a Windows text editor should not see a "square," which is telling you there's an unsupported character.

Modern languages, XML and probably PHP, tend to use just ASCII 10, so that's likely the problem. A Unicode saavy text editor on Windows, however, can open such a file fine.

A way to get whatever you want, without a plug-in, is to use an XSL stylesheet to specify the line ending. Basically you put it in as <xsl:text>&#10;</xsl:text> after (and only after) the last element of the FileMaker record.

Or post-process the file using command line. I wouldn't know how to write that on Windows, but someone here might.

[FMWebschool]

Fenton,

Thank you so much! I will give that a run and let you know how it turns out. I was trying to get some applescript to work, but no luck so far. Thank you again for your help!

[FMWebschool]

OK, got the windows part working, I however cannot get the applescript part to work. The suggestion to add "|"'s to my code, did nothing but of course add "|"s when exported, so that's no good. I need to write the data using

applescript not rely on FileMaker's true export. I hope that I have explained this well enough.

[Fenton]

AppleScript? So, you'd want it to convert old-style Mac endings (ASCII 13), which is what FileMaker exports, to Unix (ASCII 10)?

"gText.txt" is the name of the file. It's on the user's desktop. The "delimiters switch" is the fastest "find and replace" technique in AppleScript:

set outputFile to (((path to desktop) as text) & "gText.txt") as file specification

tell application "Finder" -- needed inside FileMaker

set theText to read outputFile

set old_delimts to AppleScript's text item delimiters

try

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 10}

set newText to (textList as text)

on error errMess number errNum

set AppleScript's text item delimiters to old_delimts

end try

set AppleScript's text item delimiters to old_delimts

open for access outputFile with write permission

set eof of outputFile to 0

write newText to outputFile starting at eof

close access outputFile

end tell

[Wim Decorte]

Not quite true. FM always substitutes the returns in exported text for Vertical Tabs so as not to mess up the End-Of-Record delimiters. Same on Mac and Windows.

If PHP has Regular Expression support you can easily substitute the VT's for CR or CRLF depending on the platform you're deploying on.

[Fenton]

Oh yeah. I forgot about those "squares." Wim is right, it's probably easier to just do the fixes in PHP (though I don't know how, yet).

[FMWebschool]

Those squares will get you every time! It's the actual php that this is happening to...so replacing or trying to catch does not work. CNS has a beta plugin for the Mac, I just tried that and it exports without the craziness. Unfortunately, it is a beta version of the plug-in, and they are not when or if it will be completed.

Wim, Bruce, and Fenton, as always you guys are amazing! Thank you for your help.

[Fenton]

I don't know exactly what output you want for the Vertical Tabs. The following will convert them into comma space ", "

[Much of the code is to make sure that AppleScript's text item delimiters don't get left set to other than their default, which is "".]

-- Doesn't work passing variables for the endings to one subroutine. Strips all line endings. Don't know why.

set outputFile to (((path to desktop) as text) & "gText.txt") as file specification

global newText

tell application "Finder" -- needed inside FileMaker

set theText to read outputFile

set myText to my replaceEnding(theText)

set finalText to my replaceVT(myText)

open for access outputFile with write permission

set eof of outputFile to 0

write finalText to outputFile starting at eof

close access outputFile

end tell

on replaceEnding(someText)

try

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

set textList to text items of someText

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

set newText to (textList as text)

on error errMess number errNum

set AppleScript's text item delimiters to ""

set newText to someText

end try

set AppleScript's text item delimiters to ""

return newText

end replaceEnding

on replaceVT(someText)

try

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

set textList to text items of someText

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

-- change the Vertical Tab to comma space

set newText to (textList as text)

on error errMess number errNum

set AppleScript's text item delimiters to ""

set newText to someText

end try

set AppleScript's text item delimiters to ""

return newText

end replaceVT