Exporting as Return delimited

[ChuckL]

I have an excel file that I need to convert to a "return-delimited" txt file. If I import the file into FM, is there a way to export it as a return-delimited file? (I tried to do it with MS Word, but it exports as "paragraph mark" instead of an actual "return", and the system that will be reading the subsequent txt file does not recognize Word's "paragraph mark" as a Return.

Thanks much for any help.

[comment]

I am not sure what you mean by "return-delimited" txt file. If you import the data into Filemaker, then export as tab-separated text, you will get tabs in-between fields, and carriage returns in-between records. I believe you can do the same from Excel directly.

[ChuckL]

When you have "commas" between the fields (.csv file) it's a "comma-delimited" file. I need to have a "return" between the fields. Excel offers "tab-delimited", "comma-delimited", but no "return-delimited". MS Word offers about the same. My first idea was that I could open a .csv file with Word and then have Word replace the commas with a Return, but Word doesn't offer that. It can replace the commas with a "paragraph mark" but that doesn't come thru as a Return in Notepad on the computer that will be reading data out of this file.

-thanks for the comment.

[comment]

I need to have a "return" between the fields.

And what will you have between the records?

[ChuckL]

There is only a "return" between the last field of a record and the first field of the next. There is no need for a record "indicator". There are, for example, 5 fields in each record. The app that will be reading the data from this file will read the first 5 values and use them, then read the next 5, etc.

This is not a database that I'm creating. It's just a text file for a machine to read data from.

[comment]

I see. Well, it CAN be done in Filemaker, but it's not exactly simple. I believe there are better tools for this - if conversion is the only purpose here.

For example, you could export the data as tab-delimited, open it in a text editor (such as the free TextWrangler) and replace the tabs with returns. This could be automated, if you need to it periodically.

[TheTominator]

There is only a "return" between the last field of a record and the first field of the next. There is no need for a record "indicator". There are, for example, 5 fields in each record. The app that will be reading the data from this file will read the first 5 values and use them, then read the next 5, etc.

This is not a database that I'm creating. It's just a text file for a machine to read data from.

One way is to use XML and a relatively simple XSLT stylesheet.

Say that you have 5 fields in each record that you want to export: Name, Address, City, State, and PostalCode. Let us also assume that the data in each field does not extend across multiple lines.

Create a calculated field (text) named cMyFields that has this calculation:

List(

Name;

Address;

City;

State;

PostalCode

) & "¶"

Copy and paste the following stylesheet into a text file called MyFields.xslt.

<?xml version="1.0" encoding="UTF-8" ?>

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

exclude-result-prefixes="fmp">

Export using the format XML.

Choose the default Grammer: FMPXMLRESULT.

Check the box for "Use XSL style sheet" and select the file MyFields.xslt.

In the Field export order choose to export only the field cMyFields.

Edited February 18, 2010 by Guest

[ChuckL]

I've downloaded TextWrangler and it does the job just fine, Comment.

Thanks very much.

[ChuckL]

Tominator,

Thank you very much for the solution. I'll keep it in mind should I run into more difficulties.