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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
North2AlaskaSenior EngineerCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 PrewIT / Software Engineering ConsultantCommented:
@Eroots,

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

~bp
0
North2AlaskaSenior EngineerCommented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.