dealing with Special Characters

[sbg2]

Working on changing a database from a single table to a multi-table solution (ie. from an Order database to an Order database and a Line Item database). I was hoping to make a calculation field to create a Tab Delimited file but can not figure a way to insert a tab between each field.

Is there any way to add and export special characters from an FM7 field? Or does anyone know of a simple Find & Replace utility to find/replace multiple Strings with a special character (ie. replace *TAB* with the tab character, or *ENTER* with a carriage return and line feed)?

Worst case I can write a program in Java or VisualBasic.NET but would rather not reinvent the wheel if there is already a nice simple program designed for this.

[cobra]

Hi sbg2,

I believe File->Export will achieve what you require (you select the output as tab delimited). If your fields already contain tabs in them, get filemaker 7 to import your single table (old) file and then import it directly into the appropriate table of your multi-table (new) file.

To do the latter, ensure that you are on a layout that uses the table you want to import into and then choose File->Import.

I hope this helps (and I'm not missing something).

Kind Regards,

Da'oud

[sbg2]

I found I can add tabs by pressing the Ctrl+Tab within quotes in a calculation (FieldA & "[Ctrl+Tab]" & FieldB & "[Ctrl+Tab]"), still can't get a carriage return and line feed though.

[comment]

Why not do 3 exports:

1st export:

- Order_ID

- DescA

- QtyA

- PriceA

2nd export:

- Order_ID

- DescB

- QtyB

- PriceB

3rd export:

- Order_ID

- DescC

- QtyC

- PriceC

[sbg2]

I think it might be easier to create 1 calculation field for export to a single file rather than setting up 16 different exports & imports with 8 or so fields at a time. All I need to do is figure a way to either add CrLf's or find & replace them outside of FMP.

[comment]

I am not sure which is easier. In any case FMP will NOT put tabs or CRs in an exported field.

I believe the two other options are using a XML export with an appropriate stylesheet (see here for example), or simply put placeholders for tabs and CRs and replace them in a text editor.

[Leader]

I'm not sure about FM7, but earlier versions would export tabs and line feeds, but, depending on the export format, they often were included in quotes, leaving you the task of stripping the quotes out either in a word processor.

Also, the easy way of capturing a caracter like a tab, line feed, or carriage return is to open a word processor, select the character, copy it, and paste it into your field. I did this once to pick up differences between raw text formatted for Unix, Windows, and Macs.

Also, you didn't mention whzt was wrong with Comment's suggestion about doing 3 exports.

[sbg2]

I am not sure which is easier. In any case FMP will NOT put tabs or CRs in an exported field.

I believe the two other options are using a XML export with an appropriate stylesheet (see here for example), or simply put placeholders for tabs and CRs and replace them in a text editor.

I thought it would export Tabs if you use the Export Field Contents Script step, but its actually exporting as ASCII code 22, not ASCII 9. I think I will create a field called Tab and a field called Enter to add those characters in calculations, then create a VB.Net program to replace Tab and Enter fields. Its really a shame, IMO, that FM will not export to a standard text format.

To answer the other posters question... there is nothing wrong with comments suggestion but I will have to do the same process for another file that contains 17 different items ( & maybe a few other files after that). It will probably be easier to create 1 or 2 scripts/programs to handle this problem rather than creating/doing 33+ different exports and imports.

[comment]

FMP will export to a standard text format - it's just that the TAB character is reserved as field boundary, and the CR character is reserved as record boundary.

If these were allowed to be exported as field content, then FMP wouldn't be able to import its own export.

[sbg2]

If these were allowed to be exported as field content, then FMP wouldn't be able to import its own export.

Sure it would, if the programmer created the right calculation field for export. But as it stands now FMP will not convert ctrl-Tab to the .txt equivalent nor will it convert its CR to the .txt equivalent. It just dumps the literal field contents rather than allowing the programmer to specify they want to export/convert to a .txt format. This would be useful to allow programmers to create their own exports, such as an IIF file type for import into quickbooks, and I'm sure there are other uses.

While I understand that most of the time you want to export records from FMP you would not want it to convert Tabs or CR's to the ASCII equivelant, there are times that it would be nice.

[comment]

I have just now made a simple test, exporting to CSV, and opening in a text editor. One field has in-field tabs and a CR.

In the exported file, the tabs are real tabs (t), and the CR is a line feed (n).

[sbg2]

I have just now made a simple test, exporting to CSV, and opening in a text editor. One field has in-field tabs and a CR.

In the exported file, the tabs are real tabs (t), and the CR is a line feed (n).

try importing back into FMP. When I try it will only let me import each line as 1 complete field. The Source Field on the Import Field Mapping form only shows 1 field -> field1

[Fenton]

When I export to CSV (or Tab) I get ASCII character 11 instead of a carriage return. It's been that way forever. When I Export Field Contents I get what comment does, a carriage return.

Yes, it would be nice to have a simple checkbox, to export as plain text. It may be that FileMaker is incapable of seeing it the way we think, however. It may be that the "returns" we see in a field cannot be seen as such by FileMaker, hence can only be exported as what they really are to FileMaker, which is ASCII 11. Otherwise its ability to separate records is broken (not good). So post-processing would be needed; which FileMaker rightly or wrongly considers someone else's responsibility.

[comment]

Perhaps it's a Windows/Mac thing. On your urging, I made an export to CVS and re-imported. It works perfectly. I have two records and two fields, RecordID and text. In the text field there is a CR and a tab.

[sbg2]

Perhaps it's a Windows/Mac thing. On your urging, I made an export to CVS and re-imported. It works perfectly. I have two records and two fields, RecordID and text. In the text field there is a CR and a tab.

How are you inserting the Tab character and CR character in your calculation?

[comment]

It's not a calculation - it's a text field. I typed it in directly.

[sbg2]

It may be that FileMaker is incapable of seeing it the way we think, however. It may be that the "returns" we see in a field cannot be seen as such by FileMaker, hence can only be exported as what they really are to FileMaker, which is ASCII 11. Otherwise its ability to separate records is broken (not good).

Filemaker can obviously see carriage returns or else we wouldn't be able to add them to a field, or visually see them in a field. Its already interpreting the ASCII character or characters it uses for CR, how hard could it be to replace ASCII (FM return) to CrLf before exporting? They have added functions to find and replace strings within their program is there really any reason they couldnt do the same before dumping field contents to disk? Seems fairly simple to me, but maybe I am missing something?

[sbg2]

It's not a calculation - it's a text field. I typed it in directly.

so you just typed in something like:

101(ctrl-tab)ItemA(return-key)

101(ctrl-tab)ItemB

[comment]

Exactly.

[sbg2]

Well its still not working on my machine. This morning I decided I would contact FileMaker to see if this might be a bug. I search their website, find a link to Report Product Problems... link is broken. Send a message using the Report Broken Link link, of course that works. I then find another area that has a Report Product Problems link, hey this one takes me to the right page. Fill in the top part of the form but when I get to the final text box its all screwed up (it contains the HTML for the text box to report the problem and the Submit button), there is no submit button showing on the form.

Anyone know an email address for FileMaker support?

* edit: found a link to Customer Service using the sit map link..... another broken link. Ok, now I'm a little annoyed! Not with the minor issue of exporting Tabs/CR but with the fact I can't contact filemaker through their website. Also tried form the Help menu in FMP7, which takes me to another page with another broken link.

* edit 2: Found this little blurb "To maintain our level of outstanding support, we do not answer technical questions via email." ... umm ok, $3 per minute for "outstanding" support, since when did outstanding support not include email support? Ok, mini rant over. Would really like an answer to this little perceived problem but I'm not paying $3/minute to discuss it. I think its time I put this one to bed and just deal with it.

Comment,

thanks for your input on this subject.

[sbg2]

Started working on an outside solution and figured out the problem by accident:

When selecting the file to import make sure "files of type" is set to "All files (*.*)" or "Tab Seperated (*.tab)". I was selecting import from the file menu, browsing to the file I wanted to import and then double clicking that file (the drop down filter list defaults to .csv which also shows *.txt files).

Admittedly it seems pretty simple and I wouldnt have encountered problems if I had simply given the export file a .tab extension rather than .txt.

[sbg2]

Not to beat a dead horse but in case anyone else runs across this "problem"...

When using the "All files (*.*)" filter FMP will automatically distinguish between the Comma Seperated format and a Tab Seperated format. My suggestion would be if you are building a "custom" export (for re-import into FMP), give the file a nonsense extension like .xyz or some such.

If you Import the exported file from above back into FMP and then re-export to a tab-delimited file (probably works for .csv also) the CrLf's will be "correct".

[sbg2]

Not that anyone but me cares at this point but......

Just when you think you have the problem licked, it turns out you dont. If you manually type in the data you want to format for export FMP will export to a format that can be reimported. If you use a calculation with the

[comment]

What - you not done yet?

Here is a file with 3 original records. I exported those, and imported them back. The exported file is included.

HTH.

Archive.zip

[sbg2]

Sorry I'm obsessed with this I guess. At any rate, you are still typing in the text for your fields. Try the attached file and see if you can export then import correctly.

ExportTest2.zip

[comment]

Yes, I can.

I have noticed that you are using tab-delimited format. FMP Help says that in this format "Tabs in fields are converted to spaces". IMHO, you should use .csv to preserve the tabs. Although the Help file doesn't say this, I see that tabs in fields are converted to regular tabs in the export, and re-imported as tabs in fields. That is what you want, no?

Archive.zip

[sbg2]

When you do the re-import of my last file do you get 3 import records or 6? I am looking for 6. ** Edit: I see from your attachment you only got 3, I forgot to remove the ImportText field from the file before I uploaded it, my mistake.

I would still have an issue with a .csv because how do I export the quotes around each field? I can export double quotes but the .csv file will not let me export a field with single quotes. It will put double quotes around the field and the field will then re-import with single quotes around the intended import text.

I understand the issue that if you are trying to export to .csv the quotes are reserved characters so you cant export single quotes and that in .tab format Tabs are reserved. What I want is a flat text export, a calculation field that exports as WYSIWYG.

I think the file attached to this post will show what I am after and how I have been trying to accomplish it. The file contains 1 database with 3 tables: Old order form, new order form, new order line items. I am migrating from the Old order form single file database, to 2 seperate databases - new order form, new order line items. The New Order Form layout shows the same information as Old order form but uses two files instead of 1 flat file.

There are also 9 exported files in the zip. Please let me know if you (or anyone else who is kind enough to help) can get any of the listed files to import correctly (32 line items) or if you know how to make a calculation field that will export 32 records from Old Order Form using File>Export Records then import correctly into New Order Line Items. If I can do the export/import without creating a huge global field it will save me a few hours of processing time.

Copy&Paste.txt - Actually highlighted all characters in 1 field of CustomExportFieldUsingTabs field, copied the contents (ctrl-C) then pasted (ctrl-V) into a blank text document. This file appears correctly in my text editor and imports correctly.

CustExpField_CSV.csv - Will only import 7 records rather than the intended 32 records

CustExpField_CSV.tab - Will only import 7 records rather than the intended 32 records

CustExpField_TAB.csv - Will only import 7 records rather than the intended 32 records

CustExpField_TAB.tab - Will only import 7 records rather than the intended 32 records

Customer.tab - simple export, exports/imports correctly

EFC_1record.tab - Using Export Field Contents script step - exports/imports 3 records correctly, but does not appear correctly in text editor (probably because CrLf is reserved).

EFC_ALLrecords.tab - Using Export Field Contents script step in a loop - exports all intended records, but only retains last 3 line items from OLD ORDER FORM because Export Field Contents rewrites the file after each loop step.

g_ExpFldCont_Script.tab - Exports all items with a Cr at end of each record (probably does not export "correctly" because CrLf is reserved. This file will import all items correctly.

Orders.zip

[Leader]

Perhaps it's a Windows/Mac thing.

For the record, this has historically been differences between Macs, Windows, and Unix. Up until OS X, Macs used either LF or CR (I don't recall which), Unix used the other, and Windows used BOTH. (There's also a NL character which nobody used.)

This may be the reason that FM formulae offer the

[comment]

I am losing the sight of the purpose here.

It seems like you are trying very hard to combine 2 fields into one, AND to fool the system into thinking that these are still two fields. This, IMHO, will not work.

The system was build to distinguish between field contents and field boundaries. If you are exporting to .csv, the quotes are reserved. If you are exporting to .tab, the tabs are reserved.

Why insist on trying something which the system was designed to prevent, instead of using the front door?

[Fenton]

As comment says, you're wanting to fool the system. I suggest that you do just that. I was able to get the results in a few minutes, by using a text editor. Since you want to preserve some kind of separation between the fields, and between the records, you have to fool FileMaker. It is only necessary because you have a poor structure, with multiple fields, code1, code2, etc.. But, you know that, and are changing to the correct one.

Export from FileMaker as CSV, but export your Tab calculation field, NOT your CSV one. That way you end up with tab-separated text, except for the commas (which are superfluous, remove them). But you also have ASCII character 11 between some of the "records" (this is the "box" you see).

Get a text editor which can recognize such characters (can Word?, or EditPad Pro, or JEdit; I'm on a Mac, so don't know PC editors). Find & Replace that character with a return. Remove double returns & trailing return (if any). You end up with 32 lines of tab-separated text.

[sbg2]

But I already have a working solution, the "Export to: g_ExpFldCont_Script.tab" button does an Export Field Contents dump that exports to the format I want, no outside editing needed. Problem is if I have a large number of records the time it takes to build the global field expands exponentially . It takes under 1 second to complete a field export which exports one of the calculation fields for each of 2000 records vs. 25 minutes to build a global field containing the exact same information(Except the CR is correct). What happens when I need to dump 20,000 larger field contents? It may take days. The difference is the Export Field Contents script step converts a Filemaker CR to a CR that is recognizable for imports.

I'm not trying to fool Filemaker, I am trying to get the information which is contained in my Filemaker solution into a usable format. It can disply this information correctly, it can print this information correctly, I can copy this information from a Filemaker field and it will paste correctly into a text editor, I can export 1 field from 1 record at a time and right click the field to Export Field Contents correctly but Filemaker PREVENTS me from exporting this information for each and every record correctly because they do not offer a .txt file export.

How hard would it have been to offer a File>Export Records>Field Contents menu choice. They are already able to export field contents why not allow the user to export the field contents for multiple records? Ex.

[color:"green"] ' User selects Menu>File>Export Records>Field Contents

ShowPop-up window that allows user to pick 1 field for export

Open save dialog to get user input for file name

Open Stream to write to file

For Count = 1 to Get(FoundCount) step 1

Export Field Contents

Next Count

Close stream

Close file

Why in the heck would I want to build such files, whats the sense? Well besides the example I have already give, which I have seen people bring up on numerous occasions, attached are two files which FileMaker can not export to. One is Poker Hands and another is an IIF file for quickbooks. In both cases its easy to get FM to manage and display the data for the files but there is no easy way to export that information in the needed format.

Allowing an Export Field Contents for mulitple-records would make the process of making the attached files much more efficient and much easier. Have you ever seen the FMP2QB solution for older filemaker versions? You had to jump through hoops to get the information already contained and easily displayed within filemaker. Allowing Multi-record Field Content Exports would add a lot of versatility to the program IMO.

** Edit: I'm sure it looks like Im just whining away, but honestly thats not my intent. I am just looking for an easily workable solution to my problems and wondering why FM added the function to export field contents but limited it to 1 record? Maybe they added the feature for some other reason without realizing the potential of allowing multi-record Field Content Exports... Yes I am going to make the suggestion that they add this feature, but I wont be holding my breath. Thanks again for any and all help!

ComplexExports.zip

[Fenton]

I can see your point that Export Field Contents could have a checkbox (like Send Mail does) to export all records of the found set as one file. Of course, then it would have to do something like put 2 returns between records, which people would then say wasn't what they wanted. And it wouldn't work for files or images, etc., which is what the step was really designed for. So the dialog would have to check whether your field was actually text or something else. And I suppose you'd want formatting preserved, cross-platform -|

Another way you could do what you want is to do multiple exports, one per Code1 fields, one per Code2 fields, etc.. Then you don't have to deal with the odd characters. You could also find a tool to concatenate the resulting files; or just do multiple imports.

If you had set up your fields as repeating fields, which have been available forever, you would have been able to split them on import. The way you did your fields was just wrong, so you're having problems.

I believe FileMaker would say, about all these other formats, is that most serious data programs these days have some kind of XML import/export, and that is the way to go. It is certainly the direction for the future. Quickbooks for example has awesome XML functionality (though it doesn't work on the Mac version -(

One reason the problem you're facing exists is that the only free file-manipulation plug-in (which could fix those characters in one step) has not been updated for years. I won't say more about it, as it is a controversial issue; but it is my belief that FileMaker does not take this issue as seriously as they should.

[sbg2]

"Of course, then it would have to do something like put 2 returns between records"

Huh? Think like your writing to a text file, not like your are writing a record to a database. There is no reason FileMaker would have to put return characters after each record it steps through. Open file, open stream, write text in field, advance to next record, repeat to end of records.

"And I suppose you'd want formatting preserved, cross-platform -|"

Well no, but at the bottom of this page is the code required to save a text box as either a .txt file or as an .rtf (rich text) file with formatting (for windows at least). Code doesn't look that complicated, does it?

"Another way you could do what you want is to do multiple exports, one per Code1 fields, one per Code2 fields"

This was suggested earlier in the thread by Comment and I agree it is a workable solution, however I still personally prefer 1 calculation field, 1 script and 1 import vs. building 16 exports & 16 imports.

"If you had set up your fields as repeating fields, which have been available forever, you would have been able to split them on import. The way you did your fields was just wrong, so you're having problems"

Just for the record I am the one fixing the fields not the one who created them. I'm sure there have been many before me, and will be many after, that had to go back and fix other fields that were "just wrong". One of FileMakers selling points is that it easily learned by novice users.... "FileMaker Pro is the #1-selling easy-to-use database software that effortlessly manages all your information".

"I believe FileMaker would say, about all these other formats, is that most serious data programs these days have some kind of XML import/export, and that is the way to go"

I certaintly hope that wouldn't be their stance! They should realize that a lot of their users are not going to have the latest versions of all programs. Many small businesses simply can't afford to update everytime a new version is realeased. Sorry, but we need our programs to be able to interact with older programs.

----------

Private Sub mnuFileSaveAs_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles mnuFileSaveAs.Click

        ' Determine if Text file

        If rdbText.Checked = True Then



            'Open a text file for writing

            SaveFileDialog1().Filter = "txt files(*.txt)|*.txt"

            

            If SaveFileDialog1.ShowDialog() = DialogResult().OK Then

                rtxtFile.SaveFile(SaveFileDialog1.FileName, RichTextBoxStreamType.PlainText)

            End If



        Else

            ' Open an RTF file for writing

            SaveFileDialog1().Filter = "RTF files (*.rtf)|*rtf"

            

            If SaveFileDialog1.ShowDialog() = DialogResult().OK Then

                rtxtFile.SaveFile(SaveFileDialog1.FileName, RichTextBoxStreamType.RichText)

            End If

        End If

    End Sub  

[comment]

How often does this happen? There are so few resources available in Filemaker (compare the number of functions to Excel, for example) - should that be a priority? I would hope not.

I believe there are better tools for manipulating text and text files than FMP. You could have exported a single calc field like:

Order_ID & "#TAB#" & DescA & "#TAB#" & QtyA & "#TAB#" & PriceA & "#CR#" &

Order_ID & "#TAB#" & DescB & "#TAB#" & QtyB & "#TAB#" & PriceB & "#CR#" &

Order_ID & "#TAB#" & DescC & "#TAB#" & QtyC & "#TAB#" & PriceC

Working very slowly in a text editor, you'd have your file ready for import 3 minutes later. But then I have said this before, too...

[sbg2]

How often does this happen? There are so few resources available in Filemaker (compare the number of functions to Excel, for example) - should that be a priority? I would hope not.

I would think it happens often enough, again many people who buy FileMaker are attracted to its ease of use. Many people create and use files before realizing there was a better way to create the same soulution utilizing 2 tables rather than 1. Again, this function could also be used for more complex text exports, not just to convert from a 1 file to 2 file solution.

Should it be a priority? Not neccesarily, but 95% of the code was already written in the Export Field Contents code. Its not like they would need to reinvent the wheel to implement the feature.

As I have stated, I already have a solution and I did build a calculation that used place holders for Tab and CrLf then replaced them in MS Word. One roadblock I encountered was the CrLf's at the end of each record, this left blanks between each record ID. I assume I could remove the CrLf's before replacing my Tab and Enter placeholders but dont have MS Word on my machine here at home to test that.

At that point I was experimenting with the different export options in FMP and stumbled across the "Export Field Contents of a global field soultion" which I can confirm works. Its slow, but it works. I'm not trying to gather a petition or anything, I'm just discussing and documenting my way through a solution. You may not find it useful, but hopefully someone else will.