How to generate an ASCII 28 delimiter in filemaker

Hello,

we are trying to create an export of data that must strictly adhere to a specific format.

This requires us to generate a text file that uses an "ASCII field separator character with a value of "1C" (binary value 28).

so I'll basically want to be exporting a txt file that runs like:
field1 (field separator) field 2 (fs) field3 (fs) (carraige return)

Can someone point me in the right direction for exporting in this type of format. Basically the delimited formats I can see by default will not work due to the delimiters. I have included the specific format instructions per the format below.


fields
are separated (delimited) by ASCII “Field Separator” characters. (An ASCII field separator (FS) character is a 1-byte code with a hexadecimal value of ‘1C’. (The binary value is 28 and in many software programs can be coded by holding down the Alt key while typing 28 on the numeric keypad.) Each FS character marks the end of one field and the beginning of the next. As with prior versions of the format, each record is terminated with CRLF (carriage-return+line-feed) hexadecimal ‘0D0A’ characters. Trailing unused fields need not be marked by FS Field Separator delimiters. (In order to maintain continuity with previous releases of the FEC format, double-quote mark characters are not allowed within text fields.)

Open in new window

ErootsAsked:
Who is Participating?
 
North2AlaskaCommented:
@Eroots,

Let's say you had 5 fields.  I would make a calculated field with a calculation like:

Field1 + Char(28) + Field2 + Char(28) ...

Then I would export just the calculated field.
0
 
Bill PrewCommented:
I don't think FileMaker lets you define the field delimiter character when you export data, you can either do comma delimited or tab delimited.  I'd suggest you do a tab delimited export from FileMaker, and then repace the tabs in the resulting file with the x'1C' delims.  I have a small VB script that can do that easily and will post below.

~bp
0
 
North2AlaskaCommented:
This is an export out of FileMaker?  One thing you could do is make a single calculated field that took all the other fields and combined them with the separator you wanted between each field.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
Here's a VB Script to replace tabs with x'1C'.  Save as a VBS file, then run as:

cscript vbscript delims.vbs "c:\temp\inputfile.txt" "c:\temp\outputfile.txt"

If you leave off the second file name (output file) on the command line then the original file will be overwritten with the changes.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  sInfile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  sOutfile = WScript.Arguments(1)
Else
  sOutfile = sInfile
End If

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Read entire input file into a variable and close it
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)
sData = oInfile.ReadAll
oInfile.Close
Set oInfile = Nothing

' Do desired replacements
sData = Replace(sData, Chr(9), Chr(28))

' Write file with any changes made
Set oOutfile = oFSO.OpenTextFile(sOutfile, ForWriting, True)
oOutfile.Write(sData)
oOutfile.Close
Set oOutfile = Nothing

' Cleanup and end
Set oFSO = Nothing
Wscript.Quit

Open in new window

~bp
0
 
ErootsAuthor Commented:
@ North2alaska. If I was to do that, How in filemaker do I insert an ASCII character, and then export the data as entered in the field (without it trying to strip out any ASCII I enter).

@bill Is it theoretically possible that I use the SendEvent script step to do this automatically as part of a script? Basically what I need to do is export to the specific format, and launch it into a format checker .exe file.
0
 
Bill PrewCommented:
@Eroots,

Will have to research that a bit, I'm not a FIleMaker expert but will research a bit.

~bp
0
 
Bill PrewCommented:
@Eroots

==> bill Is it theoretically possible that I use the SendEvent script step to do this automatically as part of a script? Basically what I need to do is export to the specific format, and launch it into a format checker .exe file.

Yes, I think this might work.  You could create a BaT file that ran the CSCRIPT, or you could try running the CSRIPT.EXE right from the SendEvent.  Not sure how parms work in SendEvent though.

~bp
0
 
ErootsAuthor Commented:
I ended up using North2Alaska's solution for my specific application. However Bill's is also valid. It's just easier to use something that's native to the application you're dealing with, rather than running processes using external protocols.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.