FileMaker Export to Tab Delimited

I want to export a table to a tab delimited text file1.

FileMaker supports that without any question. What it doesn’t do is create a header row.

I’m using this file to import into PickATime, a software designed to book parent-teacher conferences. I want to automate this as much as possible. So how do I get the header rows that are required?

In FileMaker my field might be called OpenApplyID, whereas PickATime requires the field to be called StudentID. So I can’t just take those field titles.

There’s new script steps in FileMaker 18 that will let me do this.

Script

Go to Layout [ “StudentContacts” (StudentContacts) ] 
Show All Records
Set Variable [ $path; Value:"filemac:/Macintosh HD/Users/USERFOLDER/Downloads/" & Year ( Get ( CurrentDate ) ) & Month ( Get (CurrentDate ) ) & Day ( Get (CurrentDate ) ) &" - " & "PAT_Students.txt" ] 
Export Records [ File Name: “$path”; Create folders:No; Character Set: “Unicode (UTF-8)”; Field Order: StudentContacts:: OpenApply_id 
 StudentContacts::first_name StudentContacts::last_name StudentContacts::birthday ] [ No dialog ] 
Set Variable [ $additionalText; Value:"StudentID<tab>First<tab>Last<tab>SecurityValue"]
Open Data File [ “$path” ; Target: $dataFile ]
Write to Data File [ File ID: $dataFile ; Data source: $additionalText ; Write as: UTF-8 ; Append line feed: On ] 
Close Data File [ File ID: $dataFile ] 

English

First go to to the student contacts and show all of the kids. I tell it where I want to save the file.

Then it exports the records.

I create a variable called $additionalText in which I want to store the contents of the first row2.

Then we open the file

Write $additionalText to the file.

Then Close the file.

Problems

Usually I’d do filemac:../Downloads and not have the absolute path, so it will work on other computers. For some odd reason when I do the relative path, it fucks up when I try to write to data file.

It puts a spare blank line in the file, but that works.

  1. That’s a text file which is like a spreadsheet where you have a tab separating columns and line separating rows, you can open them in Excel. []
  2. WordPress doesn’t like the tabs, so I had to replace them with <tab> []

Leave a Reply

Post Navigation

%d bloggers like this: