Filemaker to flat file/ EDI

[Peter Buchanan]

Some javascript/style in this post has been disabled (why?)

Can anyone offer advice and direction on exporting data from FM10 (I am thinking in CSV format) and from that output, creating a flat text file with definite fixed widths for each field. This file is then used for EDI with other service providers.

 

Is it possible / feasible doing this in FM itself (unused spaces would have to be filled with blanks) , or can anybody recommend software that will do this – I have heard of ‘translator” software?

 

Many thanks

Peter

 

 

[Winfried Huslik]

Peter,

The most reliable way would be to utilize XML export.
See the example files in /Applications/FileMaker Pro Advanced/English  
Extras/Examples/XML Examples/Export as a starting point.


Winfried
www.fmdiff.com



On 2009-10-01, at 16:06, Peter Buchanan wrote:

> Can anyone offer advice and direction on exporting data from FM10  
> (I am thinking in CSV format) and from that output, creating a flat  
> text file with definite fixed widths for each field. This file is  
> then used for EDI with other service providers.
>
> Is it possible / feasible doing this in FM itself (unused spaces  
> would have to be filled with blanks) , or can anybody recommend  
> software that will do this – I have heard of ‘translator” software?
>
> Many thanks
> Peter

[Bob Minteer-2]

Some javascript/style in this post has been disabled (why?)

I would recommend building your EDI file in a global field and then exporting the field contents of the global field to a text file.

You can control all of your padding and such in the script and through some of the custom functions already available at Brian Dunnings site.

If you do this, keep in mind that the output format will be UTF-16 (not UTF-8).   Some progs have no problem with this... And some do.   

If the intended recipient of the EDI file can only take in UTF-8 text then you can buy a TROI file plug in to output the data in the proper format (I believe).

My database retrieves remittance advice via EDI data several times a week (native... No plug-in involved).   It comes to us as UTF-8.

Regards...

Bob Minteer


On 10/1/09 9:06 AM, "Peter Buchanan" <caradoc@...> wrote:

Can anyone offer advice and direction on exporting data from FM10 (I am thinking in CSV format) and from that output, creating a flat text file with definite fixed widths for each field. This file is then used for EDI with other service providers.
 
Is it possible / feasible doing this in FM itself (unused spaces would have to be filled with blanks) , or can anybody recommend software that will do this – I have heard of ‘translator” software?
 
Many thanks
Peter
 
 

[Corn Walker]

On Oct 1, 2009, at 11:24 AM, Bob Minteer wrote:

> If you do this, keep in mind that the output format will be UTF-16  
> (not UTF-8).   Some progs have no problem with this... And some do.

You can choose a different output file character set. This is  
accomplished by using the standard "Export Records" command to export  
a found set instead of the "Export Field Contents" command. Your found  
set contains only a single record and your export order includes only  
the previously recommended global field. Choose the "Tab-Separated  
Text" file format and on the ensuing dialog select your output file  
character set.

I might still use XML/XSLT but this method also works in a pinch.

Cheers,
-corn


Corn Walker
The Proof Group
http://proofgroup.com/

[Beverly Voth-2]

On 10/1/09 11:36 AM, "Corn Walker" <[hidden email]> wrote in whole or in part:

> On Oct 1, 2009, at 11:24 AM, Bob Minteer wrote:
>
>> If you do this, keep in mind that the output format will be UTF-16
>> (not UTF-8).   Some progs have no problem with this... And some do.
>
> You can choose a different output file character set. This is
> accomplished by using the standard "Export Records" command to export
> a found set instead of the "Export Field Contents" command. Your found
> set contains only a single record and your export order includes only
> the previously recommended global field. Choose the "Tab-Separated
> Text" file format and on the ensuing dialog select your output file
> character set.

Yes! Just keep in mind the "return-in-field", gets converted to VT (vertical
tab) and the TAB gets converted to space when you export as Tab-delimited.
That is because this format uses "return" as the row ender (even though you
have one row) and the "tab" as the field delimiter (even though you only
have one field). If you need these characters "as-is", then XML is a great
choice.
 
> I might still use XML/XSLT but this method also works in a pinch.

I've worked "EDI" with calcs/scripts (way long ago, probably FMP 3/4.1 days)
and with XML. Maintaining the calcs is much harder than revising an XSLT.
But maybe that's just me. <smile />
Beverly
 
> Cheers,
> -corn
>
>
> Corn Walker
> The Proof Group
> http://proofgroup.com/

[Peter Buchanan]

Thanks to everyone for the helpful replies - this is an area that I have no
knowledge at all, so here goes....

Steve, your reference to Brian Dunning's website seemed to provide the ideal
solution at

http://www.briandunning.com/cf/773

but this involves customs functions(?), which I am not familiar with. Can
anyone unravel this formula for me?

Regards
Peter



-----Original Message-----
From: FileMaker Pro Discussions [mailto:[hidden email]] On
Behalf Of Corn Walker
Sent: 01 October 2009 17:36
To: [hidden email]
Subject: Re: Filemaker to flat file/ EDI

On Oct 1, 2009, at 11:24 AM, Bob Minteer wrote:

> If you do this, keep in mind that the output format will be UTF-16  
> (not UTF-8).   Some progs have no problem with this... And some do.

You can choose a different output file character set. This is  
accomplished by using the standard "Export Records" command to export  
a found set instead of the "Export Field Contents" command. Your found  
set contains only a single record and your export order includes only  
the previously recommended global field. Choose the "Tab-Separated  
Text" file format and on the ensuing dialog select your output file  
character set.

I might still use XML/XSLT but this method also works in a pinch.

Cheers,
-corn


Corn Walker
The Proof Group
http://proofgroup.com/
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.14.1/2407 - Release Date: 10/01/09
06:34:00

[Bob Minteer-2]

Corn,

Oh man!  I didn't even think using Export Record and a global field to do
the deed and in the desired format.   Easily scriptable!

See what happens when I am paying attention... I end up learning something.

BTW... There are some EDI hold-outs that I have attempted to push over to
XML as well.  To them...  Nothing is broke, so they don't think it needs
fixing.

Regards!


On 10/1/09 10:36 AM, "Corn Walker" <[hidden email]> wrote:

> On Oct 1, 2009, at 11:24 AM, Bob Minteer wrote:
>
>> If you do this, keep in mind that the output format will be UTF-16
>> (not UTF-8).   Some progs have no problem with this... And some do.
>
> You can choose a different output file character set. This is
> accomplished by using the standard "Export Records" command to export
> a found set instead of the "Export Field Contents" command. Your found
> set contains only a single record and your export order includes only
> the previously recommended global field. Choose the "Tab-Separated
> Text" file format and on the ensuing dialog select your output file
> character set.
>
> I might still use XML/XSLT but this method also works in a pinch.
>
> Cheers,
> -corn
>
>
> Corn Walker
> The Proof Group
> http://proofgroup.com/
>

[Steve Lemond]

Some javascript/style in this post has been disabled (why?)

Peter,

 

It may be old school, but I use a calculation field to build my export record.  I can pad spaces or leading/trialilng zeroes as needed to make each field the desired length.  Then, I export only the calculation field in a tab delimited format.

 

Steve Lemond

Univ of Tennessee Martin

731-881-7895

 

From: FileMaker Pro Discussions [mailto:[hidden email]] On Behalf Of Peter Buchanan
Sent: Thursday, October 01, 2009 9:06 AM
To: [hidden email]
Subject: Filemaker to flat file/ EDI

 

Can anyone offer advice and direction on exporting data from FM10 (I am thinking in CSV format) and from that output, creating a flat text file with definite fixed widths for each field. This file is then used for EDI with other service providers.

 

Is it possible / feasible doing this in FM itself (unused spaces would have to be filled with blanks) , or can anybody recommend software that will do this – I have heard of ‘translator” software?

 

Many thanks

Peter

 

 

[Beverly Voth-2]

Uh, forgot to mention, XML with XSLT can output TEXT just fine. :)
Beverly

On 10/1/09 11:57 AM, "Bob Minteer" <[hidden email]> wrote in whole or in
part:

> Corn,
>
> Oh man!  I didn't even think using Export Record and a global field to do
> the deed and in the desired format.   Easily scriptable!
>
> See what happens when I am paying attention... I end up learning something.
>
> BTW... There are some EDI hold-outs that I have attempted to push over to
> XML as well.  To them...  Nothing is broke, so they don't think it needs
> fixing.
>
> Regards!
>
>
> On 10/1/09 10:36 AM, "Corn Walker" <[hidden email]> wrote:
>
>> On Oct 1, 2009, at 11:24 AM, Bob Minteer wrote:
>>
>>> If you do this, keep in mind that the output format will be UTF-16
>>> (not UTF-8).   Some progs have no problem with this... And some do.
>>
>> You can choose a different output file character set. This is
>> accomplished by using the standard "Export Records" command to export
>> a found set instead of the "Export Field Contents" command. Your found
>> set contains only a single record and your export order includes only
>> the previously recommended global field. Choose the "Tab-Separated
>> Text" file format and on the ensuing dialog select your output file
>> character set.
>>
>> I might still use XML/XSLT but this method also works in a pinch.
>>
>> Cheers,
>> -corn
>>
>>
>> Corn Walker
>> The Proof Group
>> http://proofgroup.com/
>>