Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 5796 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

I have a need to export fixed-length flat ascii text files from Filemaker. I do not know if there is a plug-in out there for this. But would like to know.

Basically I need to be able to specify the characters per field when exporting. Not sure if Filemaker is currently adjusting each output field length to manage the longest value in the database.

Any ideas?

thank you

Posted

It is possible to do this using Export, XML, with an xsl stylesheet. There is an example for this at the FileMaker XML Library page:

http://www.filemaker.com/products/technologies/xslt_library.html

Specifically:

http://transfer.filemaker.com/collection/XMLExamples.zip

In the "export" folder, the "fixed_width.xsl" file. It is generic, in that it will format any FileMaker file as fixed-width.

It is modular and documented. You can change the width (which is set at 17), padding character (a space) and line ending (currently Windows).

Posted

Oh yeah. You'd want to change the output to ASCII. Change this line:

But that means you must have only ASCII characters in your data. Anything else is going come out not very pretty.

Posted

Well, thank you for your answer.

I have seen the XML stylesheet.

It looks like the columnwidth variable applies for all fields.

I need a way to specify the columnwidth by each field.

Posted

Well, you could modify it to specify the length for each field. But that would be likely more trouble than just creating calculation fields (unstored) in FileMaker to pad/truncate the widths. How many fields are we talking about, approx.?

Unless there's a known overall system, like, "all number fields are 10 in length, all text are 20", etc.. Because it is possible to tell which kind they are in the xsl, by looking up at the Metadata section (which should be in the same order as the fields exported, therefore the COL elements further down).

Posted

There are about 200 fields. Some of them we will not use them but we have to "reserve" their space in the export file.

It's just that I thought Filemaker would have an easier way these days. I has hoping that the new version had this nailed. There are more difficult things that have been programed (SQL two way links, etc.) and this should have already been there.

The calculation field sounds good for a few fields.

I am thinking to export in another format and then using another application take the file and generate the final file.

thank you for your help.

Posted

I can see why they didn't include this. Because there are likely a lot of different formats for a "fixed-length" export, different lengths, like yours, different separators. They give you xml/xsl partly for that reason, so you can roll your own.

But you could do this in another table in FileMaker. All you'd need to import would be a primary serial ID of the data table. Then create an unstored calculation for each field to be exported, padded and/or truncated.

I had another idea about the xml/xsl. If you could name the fields with a suffix of their fixed length, the xsl could get that out of the Metadata/Field/@Name, set each into a variable, then use that for the fixed length.

field_name_7

another_field_10

etc..

a little weird, but it should work fine.

Posted

"It's just that I thought Filemaker would have an easier way these days."

Crikey, why does everybody blame FileMaker! The problem is not with FileMaker it's with the system that requires a fixed length export file! Sheesh. Fixed length files were a PITA way back then too.

The "easier way" exists in the form of tab and comma delimited file formats, and all the other export options that FileMaker offers.

Posted

The fact is we live in a world where there is a need to interact with other systems. And not all applications or systems change at the same speed.

There are many applications (financial, medical, and other industries) that run on mainframes, AS/400 or similar servers than run COBOL applications (and alike)that use the fixed length format. And if you think about it, it is a safe format. It does not depends on delimiters. You just need a layout (or schema) of where each field starts.

There are other databases out there that allow you to do this.

I like Filemaker very much, do not get me wrong. But while these huge applications, that have been running over 25 years or more do not update their input formats, there is a need for interaction. And if I could use Filemaker, which is my preferred database, I will try to use it for any task I have. If I can't use Filemaker, I will have to use another.

Although I have been using Filemaker since version 2, I think, I have not worked in depth with it in the past couple of years. So that it's why I thought this could be easier now. Just because it has been more than a couple of version since I checked this.

Posted

"The fact is we live in a world where there is a need to interact with other systems."

Yes, and the world now uses csv.

Talk with the system admins. I *betcha* they could work with csv or tab delimited files if they tried, only at some stage the process was set up to work with fixed length and nobody has been bothered to change it since then.

Even better, give the sysadmins the data in csv format and tell them to get their act together.

"And if you think about it, it is a safe format."

A format that breaks if one character is out of place? A format that is hugely space inefficient? A format that has hard-coded limitations on field length?

Should we complain that FMP cannot output to paper tape too? Or punch card? Fixed-length is about the same vintage...

Posted

I don't really think this is FileMaker's issue, they've provided us with all of the industry standards for data exchange... and then some.

I have personally never seen something that uses column length to determine field separation...

Posted

You might try "pre-processing" the file in Excel first as it has a text import wizard that recognizes fixed width fields. Then if that works, you can open the resultant Excel file in Filemaker.

James

www.james-mc.com

Posted

I am trying to export FROM Filemaker to fixed-length ascii files.

That would helped me if I were importing to Filemaker.

But thank you anyway

Posted

I can understand that many people do not have the need for this format now, specifically if you do not interact with legacy applications that usually run on mainframes, as/400, or similar servers. But I do not control these applications and still have a need to interact.

Also I can understand that FMI can not please everyone and has to choose the most popular features.

But I think it does not hurt to ask for it. FMI has listened to its users in the past and I think it will keep listening. If we stay quiet, FMI will never know of our need. If enough people request it, maybe they will add it someday. They have done advanced things with their new version. I am sure they can pull this off.

I am including an example of what I would like (sorry form the screenshot of the other database)

Posted

I'm sure that you could export to excel and write a fairly basic macro to do what you're after... Specialized situations like this always crop up, the solution is not to wait for FM to do something about it (it doesn't do stuff about what a lot of developers want in most cases - only has so many resources)... but go and spend an hour on google on conversion from one of the formats FM does support, to your required format.

Posted

"The fact is we live in a world where there is a need to interact with other systems."

Yes, and the world now uses csv.

Well, that is a perfect example of a classic generalized statement. And I do not comment on those, it is a waste of time.

Believe me that I am fighting a battle in two fronts. I know there are two ways for this to get done. Either my application does fixed-length or the other application changes to read another format. In the same way I ask FMI to incorporate this ability, I asked for the other application to change theirs.

A format that breaks if one character is out of place? A format that is hugely space inefficient? A format that has hard-coded limitations on field length?

Should we complain that FMP cannot output to paper tape too? Or punch card? Fixed-length is about the same vintage...

I really do not care about the format. I am not defending fixed-length or any other format. I just have a need to do a job, and for this task I am required this specific format. Any format can break if missing a character(or delimiter). CSV is no different. Fixed-length is space inefficient, although that has been irrelevant light years ago since compression. Anyway, this was never an issue of debating which one is a better format.

Is an issue of a need and finding out if Filemaker could do it. We as users have a right to provide feedback to FMI.

At the end of the day the features that FMI will incorporate are the ones that they think will likely to increase sales. Many times those features are related to new technologies or easier ways to do things. But many times they are related to user needs. If there is suddenly a huge need to output to punch cards or tape, and FMI thinks it will help them sell the next version and increase their market share, they will do it. It does not matter if it is legacy.

Again, at the end of the day, it is sales appeal that drives features.

And for the record, I have never complained about Filemaker. Again, Filemaker is my preferred database and I have been out of developing anything in filemaker for about three years, although I keep using it. So when I say that I though this would be easier now, it is because there has been about three new versions since I got to develop something in Filemaker. So no need to be defensive.

Posted

Othni, Fenton gave you the solution: an XML/XSL export.

It can be done, FileMaker Pro has the ability. It merely requires a bit of self-assembly.

Posted

I have never complained about Filemaker

Really... why not? I do it all the time lol.

But once again, as Vaughan says... XML / XSL = easiest way to run the conversion. This is the whole point of XML and XSL. I have to provide feeds to various companies in the form of an XML document, the thing about xml though is, the whole thing is a custom data type -- its not "comma delimited" or "space delimited" - it is description and level delimited, i.e. non-generic. You can do a LOT with XML and XSL to reconstruct data. PS I hate XML... most of the time... but whenever I have to use it, I google.

Posted

The unstored calculation field is really the way to go. If you write yourself a couple of custom functions, it can be a fairly clean & self-documenting calc, e.g.

FixedLenghExportField =

PadField(field1, 20) &

PadField(field2, 24) &

PadField(field3, 96) &

PadField(MyDateToISO8601(field4), 22)

etc.

It's really no more work than doing in using the export wizard in Excel.

Posted

Same can be done with XML. If you use the document() function to grab the output field names and lengths from another XML file.

The advantage is that you can reuse your propriatary XML format definition in other databases and applications by simply dropping the XML /Xsl files in appropriate locations and do a xml/xsl export.

But then, you can buid a separate filemaker database which imports fields from your solution and subsequently exports them to the target format using the calcs described by xochi.

  • 1 year later...
  • Newbies
Posted

This post was helpful. I am facing the same issue of exporting to fixed base legacy DB via modem. I wonder if any one could assist in directing me on how to append EOF (control-Z)character to the end of file.

This topic is 5796 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.