Exporting records with linebreaks

[ulna]

Hi all

I've been trying to search for the answer, but can´t find it.

The problem is this:

From a FMP8 system I need to export some data to be imported to an accounting software. After a lot of diskussion with that software vendor I have confirmed that the exported textfile MUST look like this example:

01216

#11305;12

#11311;060926

#11302;060926

#11351;369000

...

The 01 on the first row is the trigger for the importing system that this is a new record. The #11305; is the definition for the field that the following data will be imported into. Every new field definition must be on a new row.

Everything fine so far... but heres the tricky bit (at least for me...

When I export from filemaker in a tabdelimited file, each record end up in one row. I must insert a linebreak after the data in each row for the import to work.

The field I'm exporting is a single field with the definition inserted and the respected field inserted after it. It looks like this:

"01" & fieldA &¶&

"#11305;" & fieldB &¶&

"#11311;" & fieldC &¶&

"#11302;" & fieldD &¶&

"#11351;" & fieldE &¶&

"#11352;" & fieldF &¶&

"#11335;" & fieldG &¶&

"#11383;" & fieldH &¶&

"#11387;" & fieldI

Needless to say, the "¶" doesn't work...

Any solution anyone?

Regards

/U

[beverly]

This is the way that return-in-fields work (also adding the pilcrow "¶" in a calculated field)

On _export_ the character is converted to a Vertical Tab, because the end-of-row/end-of-record characters is the return.

If you want to export and have the pilcrow become a return, then you have a few options:

1. push all the data to another file with one field, each place there is a pilcrow in a field or real end-of-record, will be one RECORD in the temporary file. An export as tab-delimited will be correct.

2. push the export normally and use an AppleScript, VB script, fm plug-in or other means to replace the Vertical Tab with a Return (probably carriage return). This can be done by _writing_ the data to a file or converting the characters after export.

3. export as XML and use an XSLT to transform the data to a text file. XML will export the return-in-field as a return character.

[BobWeaver]

A fourth option is to compile all the data into one global field, and then do an "export field contents." This command will preserve carriage returns on export.

[Wim Decorte]

but they're Mac line endings only (Line Feeds). You have no control over it so you can't choose to have Unix or Windows line endings.

[beverly]

but they're Mac line endings only (Line Feeds). You have no control over it so you can't choose to have Unix or Windows line endings.

Aye, that's why I made the suggestions I did... :D

Edited September 28, 2006 by Guest

[BobWeaver]

but they're Mac line endings only (Line Feeds).

To be precise, they are Filemaker line endings (carriage return only ASCII 13). But, I have found many external applications on both Mac and Windows correctly interpret the carriage return as a line ending. But, I do agree that it would be nice to have the option of selecting character encoding on export like you can when you choose the regular export.

In any event, I made the suggestion because I recently needed to do the a similar thing with about 2 Meg of data in the field, and after trying several methods, found Export Field Contents to be the simplest and fastest, for me anyway. :D

[ulna]

Thanks for all of your suggestions.

I've solved the problem in a different way. Since I'm gonna do this on a Mac, I fiddled a bit with Automator and found a Automator script step that extracts the content of a selected field in the found set in FileMaker.

I was testing a bit and was happy to find that when I create an automator workflow that creates a text file, extract data from the field I specify and paste that data into the textfile, the formatting is kept exactly as entered into the FileMaker fileld.

The script is called "Extract Field Data From Found Set" and are available free from Automatedworkflows.com.

Regards

/U

[amypaulsmom]

This Automater FMP Extractor sounds like exactly like what I need. I downloaded the file and installed it, but now I cannot fine it!!! Should I be looking for it within Automater now?

Thank you for your help.

CS

[ulna]

HI

When you start automator select Filemaker Pro in the list to the left. Then the script will appear in the middle column.

/U

[amypaulsmom]

Yes - once I received an updated version of the Automator file I could see the action withing Automator. Thanks.

[Brudderman]

A fourth option is to compile all the data into one global field, and then do an "export field contents." This command will preserve carriage returns on export.

Bob, I have multi-paragraph text in a global field. I am exporting field contents as you suggest to a text file but still see the vertical tabs in in the exported file. Is there any way I can preserve the actual returns and avoid the vertical tabs in the exported? Thanks!

[Brudderman]

export as XML and use an XSLT to transform the data to a text file. XML will export the return-in-field as a return character.

Beverly, do you have a pointer to a simple XSLT to do this? I'm not to clever at this but do have your book. Thanks!

[Fenton]

Here's an example.

Export_1_field.zip

[Brudderman]

Thanks, Fenton; I appreciate your helping me figure this out.

I'm still having a problem, though. Both with your sample exported file and my own test export with the XSL, I see small squares (rather than returns) when I open the files in Notepad. The only real return is between the two records if I'm looking at this properly.

I'm attaching pictures of both files opened in Notepad for you to look at. What am I doing wrong?

[comment]

Try switching to UTF-8? I believe it can handle "characters like in Mañuel" just as well.

[Fenton]

Mine does not handle Mañuel with UTF-8. But, if you want to change your encoding, it's in the xsl stylesheet in this line:

Just change the 16 to 8.

[comment]

It seems Textwrangler and BBEdit have problems recognizing the file encoding - they think it's 'Western (MacOS Roman)', for some reason. But if you choose 'File > Reopen Using Encoding... UTF-8' it's fixed. TextEdit works fine with no adjustments.