Exporting XML Mystery

[Tyra]

I export serveral hundered of these records a day, and I keep running into this problem. When my unix box tries to import and parse the records i get errors on some of the records. I believe they are due to some type of wierd imbeded character, but have been unable to find them. I am including a copy of good and bad version of the same record. You can see the issue if you try to open them in FireFox. IE seems to open them both fine. Any help would be greatly appreciated.

fmexport.zip

[comment]

You have a control character near the end of the document, at the end of PrimarySiteURL data. You can see it if you open the file in a text editor such as TextWrangler and select 'Show Invisibles'. How did you produce the "good" file?

[Tyra]

Comment, thank you. I have tried several text editors and have been unable to see anything. I simply re-saved it in Dreamweaver, and it got rid of what ever character that was causing the problem. I was just trying to figure out what character it was so that I could try to "scrub" that character out before exporting. I am going to see if I can download "TextWrangler" now.

[Tyra]

Shoot, I need to find one that works in the Windows enviroment.

[Tyra]

Or maybe you could solve my problem and tell me what character in FM I would do a subst on?

[comment]

It's a non-printing control character (0x00). I don't think you can paste it into a calculation, but you could paste it into a global field and use that as the searchString in Substitute(). However, it's very likely you have other control characters in your data as well.

[Tyra]

This is so frustrating found a windows word processor that allows me to see the character. But, in FileMaker i can't paste it because it wont pick it when I try to copy paste it. So, I thought cool, I will just script a copy and paste function to a temp variable, no dice. It keeps it then. There are other control characters, but that one seems to be the only one causeing an issue.

[comment]

But you have that character in the exported Filemaker field, no? If you carefully delete the preceding text, you will have it isolated.

[Tyra]

Yep, was working on isolating it, but then found a much easier way. The biggest problem, seems to be that Windows doesn't reconize it. But opened it remotely from the Mac Server and can see/copy it just fine. So, once the subst script is running on the server it works fine. Thank you for your help once again.

[Tyra]

Ok, so I thought it was working. Exported a new batch of records and it's there again. Went back and retested, and sure enough it's still not picking up that one control character. Trim seems to work if it's at the end of a line. But does'nt help me when it's inbetween two words. There has to be a way to scrub that guy out!!!

[comment]

If you are using Substitute(), it should get all occurrences of the character. Most likely, it's another character.

[Tyra]

It's the same character, When I try to paste it in I get this "".When I try to paste the field to a global field and delete all but that one space which just shows up as a space that I can move my cursor around, it still does not see it in the subst.

Edited February 28, 2008 by Guest

[comment]

Can you post a file with just the three fields: the field with the problematic text, the global, and the calc?

[Fenton]

Tyra, you might want to get a text editor like TextWrangler (free) from http://www.barebones.com

Because it has an ASCII palette window, which will let you see what you've got. I was able to paste the control character into a field, then use Substitute to remove it, as comment said. It was a little weird, because you cannot see it in FileMaker (at all). But after exporting it was gone.

[Tyra]

Ok, i created a test Db with two examples. See if you can convert them.

Null_Character_Test.zip

[Tyra]

Other question I have, is dont see how pasting it into a global field is going to help since it loses it value once closed anyway? I apprciate all the help, I'm just trying to solve it before the weekend, it was suppose to be a done deal already.

Thanks Fenton, problem is I'm working in a Windows enviroment, and it seems that the windows version does not match up. The clients are a mixed enviroment and it seems to be comming from the Mac users when they cut and paste from the web.

[comment]

I don't see any invisible characters in your Before2 field.

Regarding the global - if your solution is served, you'll need to use a regular field in a Preferences table table or similar.

[Tyra]

I have seen this type of field used in the HTMLtoText custom functon.

(String3" (decimal ASCII = "0"), as these can't be stored in a FileMaker text expression.)

used to subst, but not sure how to make it work in this situation.

[comment]

I' afraid I don't follow.

[Tyra]

ok, I could make a separate table just for this guy. If I can nail him down.

Thought it copied over on "Before2" too. There was one right after the IP address then two of them in the middle before the next sentence.

[Fenton]

The Before1 has the control character, which can be Substituted out, if you copy it from the text, using a text editor which lets you see it, then paste it into a global field (or a field in a Constants table).

Do not try and paste it into a calculation itself. I did, and had to Force Quit FileMaker.

The 2nd problem is kind of funny. Because the character is ASCII 11, which happens to be FileMaker's internal "return" when inside a field. It is totally normal to see that in a multi-line field if you export as Tab or comma-separated.

I do not see a problem with it when exported as XML, as it is translated to a return automatically. So you really only need to deal with the null character..

[Tyra]

Here is a copy of the custom function that I use to web viewer source to text. I was thinking I could also use it in this case if I had the right code.

"// Convert HTML text to text preserving bold and italic styles. Only HTML body text will be processed. Multiple spaces are removed.

// Parameters: "text" = HTML text to be converted. "returns" = [ "single" ; "double" ] if no empty lines - or one empty line - are allowed.

// NOTE 1: This custom function references three global fields "String1" (decimal ASCII = "10"); "String2" (decimal ASCII = "0 63")" and "String3" (decimal ASCII = "0"), as these can't be stored in a FileMaker text expression.

// NOTE 2: If you need to convert other HTML encodings like "æ" -> "æ", you may add the needed substitution pairs in the "//Transform" section.

// NOTE 3: Requires HTMLtoText_deleteTags ( text ) and HTMLtoText_convertStyle ( text )

Let (

[

//Setup

$Returns = If ( returns = "single" ; 1 ; 2 ) ;

$Return1 = Left ( "¶¶" ; $Returns ) ;

$Return2 = Left ( "¶¶¶" ; $Returns + 1 ) ;

$Return3 = Left ( "¶¶¶¶" ; $Returns + 2 ) ;

$Return4 = Left ( "¶¶¶¶¶¶" ; $Returns + 4 ) ;

$Return5 = Left ( "¶¶¶¶¶¶¶¶¶¶" ; $Returns + 8 ) ;

//Isolate body, remove comment lines (may contain characters given reason to unbalanced tags) and script and noscript lines

$SearchStart = "

$SearchEnd = "

" ;

$Text = HTMLtoText_deleteTags ( text ) ;

$SearchStart = "" ;

$Text = HTMLtoText_deleteTags ( $Text ) ;

$SearchStart = "

$SearchEnd = "

" ;

$Text = HTMLtoText_deleteTags ( $Text ) ;

$SearchStart = "

$SearchEnd = "

" ;

$Text = HTMLtoText_deleteTags ( $Text ) ;

$SearchStart = "

$SearchEnd = " />" ;

$Text = HTMLtoText_deleteTags ( $Text ) ;

$StartBody = Position ( $Text ; "

$SearchStart = "¶//" ;

$SearchEnd = "¶" ;

$Text = HTMLtoText_deleteTags ( Right ( $Text ; Length ( $Text ) - $StartBody + 1 ) ) ;

//Convert bold style

$SearchStart = "" ;

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

$SearchStart = "

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

$SearchStart = "

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

$SearchStart = "

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

$SearchStart = "" ;

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

//Convert italic style

$SearchStart = "" ;

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

$SearchStart = "" ;

$SearchEnd = "" ;

$Text = HTMLtoText_convertStyle ( $Text ) ;

//Delete HTML tags and convert special characters

$SearchStart = "<" ;

$SearchEnd = ">" ;

$Text = HTMLtoText_deleteTags ( Substitute ( $Text ;

//Blank

// [ "¶" ; "" ] ;

[ "" ; "" ] ;

[ "

[ "

" ; "" ] ;

[ "" ; "" ] ;

[ "" ; "" ] ;

[ "" ; "" ] ;

[ "" ; "" ] ;

[ "" ; "" ] ;

[ "

" ; "" ] ;

[ "" ; "'" ] ;

//NOTE: Reference to three global fields containing decimal ASCII "0" ; "0 63"; "0"

[ IncidentMetaData::String1 ; "" ] ;

[ IncidentMetaData::String2 ; "" ] ;

[ IncidentMetaData::String3 ; "" ] ;

//Space

[ "" ; " " ] ;

[ "" ; " " ] ;

[ "

[ "" ; " " ] ;

[ "

" ; " " ] ;

[ "|" ; " | " ] ;

// Transform

[ "

[*]" ; "¶• " ] ;

[ "

[ "

" ; "¶¶" ] ;

[ "

" ; "¶¶" ] ;

[ "

[ "

" ; "¶¶" ] ;

[ "

" ; "¶¶" ] ;

[ "

[ "

• " ; "¶¶" ] ;
  [ "
  

" ; "¶¶" ] ;

[ "" ; "¶¶" ] ;

[ "&" ; "&" ] ;

[ "…" ; "…" ] ;

[ """ ; """ ] ;

[ "&8220;" ; "“" ] ;

[ "&8221;" ; "”" ] ;

[ "" ; "®" ] ;

[ "" ; "®" ] ;

[ "“" ; "“" ] ;

[ "”" ; "”" ] ;

[ "" ; "»" ] ;

[ "" ; "«" ] ;

[ ">" ; ">" ] ;

[ "<" ; "<" ] ;

[ "’" ; "'" ] ;

[ "™" ; "™" ] ;

[ "" ; "Å" ] ;

[ "" ; "Ä" ] ;

[ "" ; "Æ" ] ;

[ "" ; "É" ] ;

[ "" ; "Ö" ] ;

[ "" ; "Ø" ] ;

[ "" ; "Ü" ] ;

[ "" ; "ä" ] ;

[ "" ; "å" ] ;

[ "" ; "æ" ] ;

[ "" ; "é" ] ;

[ "" ; "ö" ] ;

[ "" ; "ø" ] ;

[ "" ; "ü" ] ;

//Clean space & tab & return

[ " " ; " " ] ;

[ " " ; " " ] ;

[ " " ; " " ] ;

[ " " ; " " ] ;

[ " " ; " " ] ;

[ "> <" ; "" ] ;

[ "><" ; "" ] ;

[ " ¶" ; "¶" ] ;

[ "¶ " ; "¶" ] ) )

;

$SearchStart = "" ;

$SearchEnd = ";"

] ;

Substitute ( "Ÿ" & HTMLtoText_deleteTags ( $Text ) & "Ÿ" ;

//Clean successive returns

[ $Return5 ; $Return1 ] ;

[ $Return4 ; $Return1 ] ;

[ $Return3 ; $Return1 ] ;

[ $Return2 ; $Return1 ] ;

//Trim for returns at start and end

[ "Ÿ¶¶" ; "" ] ;

[ "٦" ; "" ] ;

[ "¶¶Ÿ" ; "" ] ;

[ "¶Ÿ" ; "" ] ;

[ "Ÿ" ; "" ]

)

)"

Edited February 28, 2008 by Guest

[Tyra]

Ok, logged into mac server, downloaded TextWrangler, it shows up as an upside down "?". So your saying if I paste that into a field it will work?

[Tyra]

When I do a Copy from TextWrangler to the FM field it looks like nothing happens.

[Fenton]

Yes, I think so. I just opened the same FileMaker file, with my Substitute, on Windows (via Parallels Desktop). The null character shows as a square box, inside the FileMaker field, whereas it did not show at all on the Mac. But the Substitute worked, and the export was clean. Apparently null is cross-platform.

I would put it into a Constant 1-record table, in either a regular field, mirrored by a calculation with global storage, or in a global field. Either would work, since you're inserting the value (if you can call null a value :-) while you have the file in a single user environment; globals stick in that case, which is what you want.

I would not try and paste it into a calculation. It caused my copy of FileMaker to go into an endless loop when I tried to close the calculation box. Maybe that was a glitch, who knows. But it does not seem to like it.

[Fenton]

I said that earlier. On a Mac you will not see it. But it still works.

[Tyra]

Ok, looks like it is working. Que'ed up a new export to test. Will let you know.

[Tyra]

Comment, Fenton, Thank you so very much for your help. The blank looking field seems to be working!!

Would still like to add it into the HTMLtoText converter though to have a one stop solution for

this type of issue.

At least I can go Snowboading this weekend now!

[cjaeger]

Hi Fenton, you can have it in calcs by converting a version 6 FileMaker file. But never touch that calc again or FM will crash.

I use Field comments to store those gremlins, and insert them into Variables with GetFieldComment(). ASCII Null.

Best way to access Ascii control chars is by using the sample plugin from the FMDev7.0 Disc or use free plugin http://www.jazzmedia.com.au/jazzutils.html CharToNum()

Christian