I am in need of having "," data separators converted to "^" and without quotations.

Sample (1)... data exported using .bas

28017,"NY","01/01/2004",400003,"0","1000",,,"5.0

Sample (2)... data needed for validation tool (win) using .txt

28017^NY^20040101^400003^0^1000^^^5.0^

Forgive me, but I have forgotten the term for the character or separator "^". The date format is easy to modify and achieve the same result, but I don't have a clue how to accomplish the conversion of separators.

Thoughts are deeply appreciated.[color:brown]

Since you have figured out the date change, this should handle the rest of it.

Substitute(Substitute(Substitute(yourtextfield, ",""","^"),

",", "^"),

"""", "")

Lee

Hi Lee,

Thanks for the reponse. I'm not exactly sure what to do with your calculation. Should this be the first field to be exported?

Steve

Oh, oh.

Are you exporting or importing. I was under the impression that this data already resided in your file, and you were applying this formatting for some reason.

Lets start at the beginning because, if it is being imported, I would suggest some different things, and if it is being exported I would also do some thing differently.

MY WAG is, that this is a comma delimited text file that you are importing?

Lee

The previous post was meant to be put into a new calculation field.

I modified my calculation a bit, so that it will also change the date to your format.

Substitute(Substitute(Substitute(Substitute(YTF,

Middle(YTF, 13, 10) , Middle(YTF, 19, 4) & Middle(YTF, 13, 2)& Middle(YTF, 16, 2)),

",""","^"),

",", "^"),

"""", "")

The YTF represent you current Field where this data is prior to your changing.

HTH

Lee

Hi again Lee.

YES, I am exporting data from 126 fields then runnung the .txt file through a validation program by FEMA.

Is there a way to export a .txt file directly, or will I have to continue to change the .bas to .txt? I'm not sure whether the validator will accept a basic file.

Thanks for the calc, but I tried it with the "Start Date" and did not get the proper result.

Incidentally, Of the 126 fields, there are three dates to convert...

- Start Date

- Arrival Date

- Return Date

Oh, I have another little issue with FM. Formatting a time with ":" separators will not auto enter the separators-- They must be entered manually. It can be stamped, but the times are never entered during real-time. Hmmm...

Thanks!!!

What does the original data have to do with this problem.

Your posts says you are exporting .bas (which doesn't ring any bells for me).

Your recent post says .txt (this I use all of the time).

Also, you are saying dates and time and know , etc.

Would it be safe to say that you now have a bunch of fields that you wish to export using ^ as the separator instead of comma?

Lee

The samples in Post#171128, were only a few of the many fields that are exported via the .bas (Basic Language source code file) selected from the FM export file type options.

So yes, all data exported from the 126 fields needs the ^ separators instead of commas and quotations.

Gotta run, i'll check back early afternoon.

Thanks again Lee.

Sorry, I didn't clarify the time separator thing.

Not related to the exporting and conversion of the ^ separator issue. This is just one nagging issue I have had with FM. The users of my file(s) complain on occation that they have to manually enter colon ( time separators when entering a time.

That's all-- sorry for the confusion.

Hi, I have never used the Basic Format, but it turns out to be no big deal.

Here is how I would do what you are describing.

I would use the Tab Delimited export file format, and I would use a Text Editor such as BBEdit, or TextWrangler to change the Tabs to ^ and remove the " at the same time. .

Both are from Bare Bones Software, and TextWrangler is [color:Red] Free. They both have the abilitiy to do Find and Replaces using Grep Patters. If you don't have either, do yourself a favor, download the Free TextWrangler, as it makes manipulation of text a heck of a lot easer than any word processor and or trying to do it in FileMaker.

If you need help with the patterns, let me know.

Next, the question about the colon is a common one, there are several threads about this in the Forum, just do a search for +colon and +Time and that should bring up a workaround. Let me know if you have a problem, and I can send you a sample of a workaround I posted a while back.

HTH

Lee

Thanks for the tips Lee. I've downloaded the TextWrangler and started to mess with it. Following your steps, I haven't quite figured out how to replace yet, but I'll get it.

Interestingly, via the Special Character menu, I rediscovered the separator character "^" is a "Circumflex Accent".

Thanks so much for your time on this strange issue.

Select Search > Find from the menu (or press Command-F).

In the Find & Replace window, type t in the "Search For" box, and ^ in the "Replace With" box.

Check "Start at Top" and click Replace All.

Worked great thank you so much!!!

Is the ^ Character used only to identify the tab or space between fields, or is there some language significance?

I'm wondering if the validation tool requires this separator. If not, two thumbs way up!

You said

I am in need of having "," data separators converted to "^" and without quotations.

Why it is needed you never said.

Lee

I am not sure I understand your question. When you export to a tab-deliminated file, the fields are separated by ... well, tabs, of course. In TextWrangler's Find, tabs are noted as t.

You said you needed the separator to be ^ instead. So you simply find t, replace with ^.

I don't know what other significance the ^ character might have in your target application. In TextWrangler itself, if you turn on the Grep option in the Find & Replace window, the ^ character will aquire a meaning. In such case it will have to be escaped using the backslash character , if you want it to be interpreted literally.

Hey Lee. I took your advise and exported tab-delimited, which of course eliminated the commas and the quotations as "comment" states. After that, it kind of simplified things by replacing tabs with ^'s.

Incidentally Lee, after "comment's" find and replace function of t, I tried , and replaced with ^. It worked equally well, but then I had to find " and replace with nothing to remove them.

I just wish there was a way I could export with ^, as I cannot expect users or administrators to conduct this conversion, but as I've mentioned, I will try validating the tab-delimited file first. Who knows, it just might work out.

Anyway, both of your suggestions have helped me immensely.

Thank you for all your help!

Edited August 12, 200520 yr by Guest

Check out Fenton's post in this thread (ignore mine there, as it is, sadly, grossly incorrect!).

You could also use Export XML, with an XSL stylesheet, to do it straight out of FileMaker. The .xsl file would have to be somewhere anyone could access however, such as on a mounted volume, or at a stable URL (though that would be slightly slower).

Other ideas for automation: If the computers are Macs you could use AppleScript to do the Find/Replace after export, with either a text editor, such as TextWrangler, or with straight AppleScript, or with AppleScript running a shell script. Or with command line on either platform; but I wouldn't know how to write it on Windows. Or use the Troi File plug-in, using the TrFile_Substitute( ) function.

The xsl code below produces what you want as a Unicode file, which is cross-platform. You could adjust it to be Windows specific if you had to. It's a text file with .xsl extension:

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

xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >

^

Hey Fenton.

Thanks for your time and detail on this issue. Wow, I'm finding more and more things I never knew about FM. I should remove the Advance from my skill level.

I've messed with just about all of the export types but the web related ones. I tried this, but I'm sure I missing something. I've attached the error message to an e-mail. Please take a look if you have time.

Platforms... It is necessary have this work for both Mac & Win.

Thanks!!!

Edited August 13, 200520 yr by Guest