Jump to content

Export as .txt


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

Recommended Posts

I'm trying to send exported data to an old mainframe that wants a .txt file type. It doesn't like the .tab type and hates the .csv type. I can copy and paste it into a notepad and send it ok, but that seems kind of clunky and unprofessional. Anybody know how to get FileMaker to export as .txt? Thanks.

Link to comment
Share on other sites

My bad, it does put something at the end of each record, which is a length-delimited set of values. The *.tab file type renamed test.txt has multiple length-delimited records on one line when viewed in Notepad. The character in question is ASCII 11, x0b, and labeled as VT (I'm assuming vertical tab).

Edited by Guest
ascii update
Link to comment
Share on other sites

I am getting more and more confused here. I am not sure what you mean by "length-delimited set of values", and I cannot figure out if you are describing what you currently get from Filemaker, or the output you need to get.

Please explain IN DETAIL how the exported file needs to be formatted in order for the other application to accept it.

Link to comment
Share on other sites

The information that needs to be imported into the mainframe is in an 80 character record. This is put together in FM in the correct format. When I export it as a .tab (since there is no .txt) it puts a vertical tab (ASCII 11, x0b) character at the end. If there was just a .txt file type, I'd be done. Or if I define my own type.

Link to comment
Share on other sites

Actually, fixed-length format is very old and isn't used very much any more; as delimited lengths became more popular because of their versitility. Fixed-length reminds me of the dbaseIII days (or slk)...

Link to comment
Share on other sites

ThePopp wrote: "Lack of support for a simple a .txt (THE most common file format) is just plain stupid."

There is NO SUCH THING as .txt export file format!

Firstly, *all* export files are plain ASCII text. So talking about an export file as ".txt" is meaningless, because they are*all* text.

Because they are all text, export formats are usually named after the way they delimit the data... such as comma separated values (csv) or tab delimited values. Fixed length is also text but the fields are not actually delimited, only records are. They are a fixed length so that the parser at the importing end knows when each field ends and the next one begins. It's old and clunky because it requires the field length to defined up front, and that's seriously inflexible. It's also wasteful because the export file needs to be padded out with spaces or zeros to make up the correct length.

The reason you're being asked to specify the details of the export file format is because with FileMaker's XML export function you can export data in *any* format you can think of, with any delimiters you want.

Link to comment
Share on other sites

Assuming you've got one 80 char field which concatenates and pads the fields you need to send in the format the mainframe is expecting...and THAT's what you mean by saying it's "the correct format"...

I would guess that the mainframe doesn't like something about the return character FileMaker's using between records in the export file format. Which I have seen before, actually, but not in a long time (Oracle and FM4 integration, don't ask).

It'd be relatively easy to test for this: copy the return char in the copy of the file that you know the mainframe successfully took, and do a find/replace in the FileMaker export file. See if the mainframe likes the file now.

If that's the problem, here's what I'd do next:

1. Try all the other export formats, to see if one of them gives a return char the mainframe likes.

2. If 1. above doesn't work, use a plugin or OS-level scripting (VB/applescript) to modify my export file after FM is done exporting it, but before uploading to the mainframe. Troi File can do the job IIRC.

Link to comment
Share on other sites

Try all the other export formats, to see if one of them gives a return char the mainframe likes.

That is hardly necessary, since export formats are described in the help file.

I believe OP has build up the entire export in a single field, so when exporting that (perhaps from a found set of one record), he gets vertical tabs where in-field carriage returns are. If he exported the field contents instead, he would get real carriage returns - but I doubt his "old mainframe" would read a UTF-16 encoded file.

A relatively simply way to do this could be to calculate each record's line within the record, then export multiple records as tab-separated. But since OP won't provide details on what he has to begin with, and what he needs as a result, it's not possible to guide him.

Link to comment
Share on other sites

Well, my point more generally was that this is likely an issue with how FileMaker's munging the data on export, rather than with how he's built his fixed-width export field per se. For example, return characters embedded within a text field would export as ASCII 11.

Actually, this is probably his problem, rather than the return char at end of line, now that I think about it.

I thought he gave enough to at least provide preliminary guidance...he was having an issue with a specific control character (vertical tab), he was exporting an 80 character record - which I read to mean, he's exporting x number of fields in a record padded out to 80 characters. I didn't see any particular reason to assume he was screwing that part of it up.

Link to comment
Share on other sites

I don't think that is the case. He has stated quite clearly he has a carriage return "at the end of each record".

Now, I don't know how he has put his "records" together - he could have scripted all of them into a global field, or maybe his file keeps multiple "records" in a single record, or perhaps something else again. In any case, what he has will not work. He needs to go back a step or two, perhaps his entire structure needs rethinking.

I see no reason why I should go into the trouble of guessing every possible scenario he might have - but that's just me.

Link to comment
Share on other sites

Of course he has a carriage return at the end of each line. But ASCII 11 chars are well documented (by FileMaker) substitutions for return chars within text fields, and he specifically noted he had a problem with those chars - and he didn't state where those were showing up in the export, or that those were in fact the end of line carriage returns.

Since he hasn't followed up on this thread, and hasn't given more specifics to his problem, you're quite right - not much point in trying to help further.

Link to comment
Share on other sites

he didn't state where those were showing up in the export, or that those were in fact the end of line carriage returns.

If you read his third post, I think he made that point clear. But it doesn't matter, because whatever he has done so far only leads to a dead end. He needs to start over - but we don't know what he has to start with.

Link to comment
Share on other sites

Post #3 could actually be read to mean exactly what it says: that his target system doesn't expect any delimiters between records. In other words, no vertical tabs in the middle of an 80 char long record string.

Just one big fat fixed width string, 80 chars long.

Read that way, I think the OP's point is quite different.

And now I'll stop belaboring the point; as you note it seems kinda pointless without the OP's involvement.

Link to comment
Share on other sites

So what do use if a line of text does not fill up 80 chars, use spaces I assume? See if the following works for you, sample attached. It should create two text files MyFirstExport.txt and MySecondExport.txt. The first should be the way you describe now as one line of text. The second is what I think you are trying to get.

1. Build your export text in a global field

2. Use Export Field Contents to export to a ".txt" file.

3. Import the file you just exported into a single field file within FM.

4. Export the imported "records" again as a .tab file but use .txt for the filename.

EightCharFile.zip

Link to comment
Share on other sites

LOL, could you not eliminate all that work by exporting the calculation field directly?

A more elegant way would be to export as XML, with a stylesheet doing the necessary padding. This saves the need to maintain a calculation field for export purposes only. A sample stylesheet is included in the 'XML Examples' folder installed with the application.

Link to comment
Share on other sites

LOL, could you not eliminate all that work by exporting the calculation field directly?

I saw that one coming (and from whom) from a mile away.

That would defeat the whole purpose of the example, would it not? *shrugs*

We have no real idea of how OP is building his export text. I assumed OP is building a global field and then using Export Field Contents. What I provided was a quick and dirty example.

The example is meant to show 1) the difference between an Export Field Contents export file and a normal export and 2) how to get a file that was exported with Export Field Contents into the commonly accepted .txt form (ie. The same as you would see if you copied and pasted the contents of a global field into Windows Notepad).

Yes, you can use XML but XML is intimidating IMO.

Link to comment
Share on other sites

I guess I don't know what "the whole purpose of the example" is. First you collect them, then you go around the block to break them up again. As I said before, Export Field Contents is not likely to work here, because of the encoding. I think we are agreed on that, otherwise you would have stopped there.

Link to comment
Share on other sites

I think Export Field Contents might work here. I am thinking it is a very similar to the problem encountered in this thread: http://fmforums.com/forum/showtopic.php?tid/189643/post/264934/hl/Export+Field+Contents/fromsearch/1/#264934

Link to comment
Share on other sites

If you think it works, why all the additional steps? Why don't you Export Field Contents (while naming the file with a .txt extension), and import it directly into the target application? Obviously you don't think that will work - otherwise you wouldn't need any more steps.

So the question remains: what's the purpose of assembling data from all records into a single field, exporting it and importing it, only to end up with the data broken up into individual records in a new table? They already are individual records in the old table.

Link to comment
Share on other sites

So the question remains: what's the purpose of assembling data from all records into a single field, exporting it and importing it, only to end up with the data broken up into individual records in a new table? They already are individual records in the old table.

The only purpose was to get to a a multiline global field where each line is 80 characters in length. Either you create many fields and many calcs or you create one field, one calc and many records. The second way was quicker.

In a real world file I agree it would probably be many fields in one record but then again we don't know how OP is accumilating the data.

Link to comment
Share on other sites

Is this really so difficult as to warrant such a lengthy exchange? Here is your file with no modifications other than removing all the nonessentials. It produces EXACTLY the same file as your final export does. I don't know why you think "many fields and many calcs" are required - one calculation field is all it takes. And even that could be eliminated by using XML.

EightCharFile2.fp7.zip

Link to comment
Share on other sites

LOL, I didn't think it warranted a lengthy exchange at all but you have officially changed my mind.

I have explained in great detail below. I will also provide a second post that has a real world example. Those who have the OS Windows, Windows Notepad and Microsoft Outlook can play along with the second post to see the results for themselves.

Short story long........

As I previously stated "The only purpose was to get to a a multiline global field where each line is 80 characters in length". Yes, the ShowSpaces and ConfirmLength fields are not needed. I put them in as visual aids for what is happening with the file.

Again previous statements such as the ones quoted below lead me to believe the closest OP got to getting the format he wanted was by using Export Field Contents on a field.

The .tab type is the closest, but it puts a tab in between the records which creates the problem. It doesn't expect any delimiters between records.

My bad, it does put something at the end of each record, which is a length-delimited set of values. The *.tab file type renamed test.txt has multiple length-delimited records on one line when viewed in Notepad.

The information that needs to be imported into the mainframe is in an 80 character record. This is put together in FM in the correct format. When I export it as a .tab (since there is no .txt) it puts a vertical tab (ASCII 11, x0b) character at the end. If there was just a .txt file type, I'd be done.

It works if I copy and paste it. Lack of support for a simple a .txt (THE most common file format) is just plain stupid.

Now imagine OP has 25 fields in one record of a FileMaker file. He has painstakingly created a calculated global field that gets the text into the format he wants. Now he exports that field using the Export Field Contents script step.

From a field defined something something like:

Left(MyFieldA & " " (<---- 80 spaces) & ; 80)

&

Left(MyFieldB & " " (<---- 80 spaces) & ; 80)

& ....

Yada Yada Yada through 25 fields.

When he tries to import the file into the mainframe it is rejected. So he opens up the text file with Windows Notepad only to discover that there are "no returns" between each line of text. In fact he/she only sees one line of text in Windows Notepad (assuming Word Wrp is turned off).

Now OP scratches his/her head, "why did that happen"? He/She wonders what would happen if they simply copied the global field, they so painstakingly created, and pasted it into a blank Notepad file. Lo and behold when they create the file in this fashion it imports correctly into the mainframe. Whats going on?

As you know the Export Field Contents script step exports to a Unicode file which exhibits the "strange" behavior OP witnessed when they opened the file in Windows Notepad.

Now what can they do? Well as you suggested they can learn to use XML. However I think their is an easier way. Simply:

1)Create a 1 field file.

2)Import the file you created using Export Field Contents into your one field file.

3)Now export (not Export Field Contents) all of the records from step 2 using the "Save as type" option set to "ab-Seperated Text Files (*.txt)". Rename the extension of the file to .txt do not use the default .tab extension.

****** Please note your text should not contain the Tab character when exporting in this format.

4)open the file in Windows Notepad.

The file should now be the way you and your mainframe expect.

Back to the original file I created. If the OP did indeed have a 25 field record to create his mainframe import file then he/she could simply create a new record in the file I provided for each field in his FileMaker file. If the resulting "MySecondExport.txt" was in the format he/she wanted/expected then that would confirm my hunch.

** I do however see now that my original file would have an extra at the end of the file but that is easily solved.

Link to comment
Share on other sites

Real world example... Thos who run MS Windows, MS Outlook & MS Notepad can follow along. If you want to get quickly to the results you can just download the attached zip file with the FileMaker file and the two VCard files.

Suppose you have a Contacts file you have created in FileMaker Pro. Each record contains things like Contact Name, Phone Numbers, Address etc. Now you need to get the records into Microsoft Outlooks contacts. You have created a global field that is the exact correct format that Outlook needs.

The text in the global field is as follows:

BEGIN:VCARD

VERSION:3.0

N:Ledbetter;Tom ;;Mr. & Mrs.;

FN:Mr. & Mrs. Tom & Theresa Ledbetter

ORG:RE/MAX Suburban Inc.;

TITLE:ePro Certified Internet Professional

EMAIL;type=INTERNET;type= home:[email protected]

EMAIL;type=INTERNET;type= work:[email protected]

TEL;type=Mobil:(586) 675-6751

TEL;type=Home:(586) 623-5522

TEL;type=Fax:(586) 477-4791

TEL;type=Work:(586) 262-2000

TEL;type=School:5 Not enough #'s

item2.ADR;type=WORK:;;;;;;

item3.ADR;type=HOME;type=pref:; ;43599 Schoenherr;Sterling Heights;MI;48313;;

item3.X-ABADR:us

NOTE:

CATEGORIES:Agent

END:VCARD

Try the following:

1) Create a new Filemaker file with one global field. Enter the above text into the global field. You will have to imagine that you created the global field contents from various fields contained in one particular record. You might have to expand the field to be sure it displays as shown above.

2) right click on your global field and choose Export Field Contents. Save the file as MyFirstExport.vcf

3) Open Microsoft Outlook and drag the newly created MyFirstExport.vcf file onto your outlook Contacts. What happens? You end up with a blank VCard! Not quite what you expected.

4) right click on the MyFirstExport.vcf file and select Open With>Notepad. Make sure after you open the file that Format>Word Wrap is not on.

Notice how the file is all on one of text and how there is a square between what you expected to be each line of text (if you dont see a square try setting Format>Font to Lucida Console.

5) Go back into your global FileMaker field and select the entire contents of the field (Ctrl-A when you are in the field). Then create a new text file (right click an open space on the Desktop and choose New>Text Document". Paste the text you copied from your global field and save the file with the name MyCopy&Paste.vcf.

6) Drop the MyCopy&Paste.vcf file onto the Contacts portion of Microsoft Outlook. Now you should a new VCard with the information as we expected.

So you don't want to manually Copy and Paste each FileMaker contact to create each VCard?

well then....

1)Create a 1 field file.

2)Import the file you created using Export Field Contents (MyFirstExport.vcf) into your one field file.

3)Now export (not Export Field Contents) all of the records from step 2 using the "Save as type" option set to "Tab-Seperated Text Files (*.txt)". Rename the extension of the file to .vcf do not use the default .tab extension. Name the file MySecondExport.vcf.

****** Please note your text should not contain the Tab character when exporting in this format.

4) Drag and Drop the MySecondExport text onto the contacts in Outlook. It should create the new VCard as expected.

It may look complicated as I have explained this in great detail. However have a look at the attached file to see how simple it really is.

MyVCard.zip

Link to comment
Share on other sites

I'm afraid you are missing the point that in a fixed-length export there are NO separators between fields. Therefore, your calculation of:

[color:red]

Left(MyFieldA & " " (<---- 80 spaces) & ; 80)

&

Left(MyFieldB & " " (<---- 80 spaces) & ; 80)

& ....

Yada Yada Yada through 25 fields.

needs to be:

[color:blue]

Let (

spaces = Substitute ( 10^80 - 1 ; "9" ; " " )

;

Left ( Field A & spaces ; 80 ) &

Left ( Field B & spaces ; 65 ) &

Left ( Field C & spaces ; 40 ) &

....

)

and it cannot be global, since the 25 fields need to be concatenated individually for each record.

However, once you have done that, you can export this field (and only this field) directly, using the tab-separated format. Since only one field is exported, there will be no tabs in the resulting file, and individual records will be return-separated.

Your vCard example is misleading in this context, because it is NOT a fixed-length format.

Link to comment
Share on other sites

Ah, I see.

I was taking a stab in the dark that even though the OP clearly stated "It doesn't expect any delimiters between records" the mainframe did indeed expect a return between each record.

OP later stated "My bad, it does put something at the end of each record, which is a length-delimited set of values. The *.tab file type renamed test.txt has multiple length-delimited records on one line when viewed in Notepad.". Which made me wonder was OP expecting to see multiple lines of text in Notepad?

Link to comment
Share on other sites

So he opens up the text file with Windows Notepad only to discover that there are "no returns" between each line of text.

Uh, no, there wouldn't be. Notepad has NEVER shown carriage returns with any document I've ever opened in it and I've opened a LOT. Even so, this isn't supposed to be a return-delimited test but rather space padded. And when I open the text file I don't get one line as you state, even with word wrap off ... I get several lines (which opens in Excel as several records). But again, you are using an example unrelated to the question at hand. :wink2:

Link to comment
Share on other sites

This topic is 1815 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.