Experiences of a FileMaker Pro Database Developer

Data Interchange

Recently I’ve been working on exports from FileMaker that are being used to import into other systems. FileMaker is great at accepting and interpreting many formats, but not so true the other way around. I find it interesting how inflexible those other systems are.

When exporting data in CSV format, FileMaker puts quotes around the data in every field, including number fields. Excel seems to only quote data when there is a comma in the data. I was told by another developer of another database system that the CSV format I’m supplying is not in “standard CSV format.” But, there is NO industry standard CSV format and it’s acceptable to put quotes around the data of every field. In order to make the client happy and conform to their inflexible CSV import, I have to open and save the file in Excel before posting to their FTP site. An extra step that’s totally unnecessary.

I had similar experiences with tab delimited files exported from FileMaker. A client requested the files as pipe delimited, but tab would also be acceptable although not preferred. Most of the work I do is on a Mac, but I make sure its compatible across both platforms. I usually export in a Unicode format. However, I was told there were no carriage returns, so I exported as ASCII (dos) format. I was again told there are no carriage returns in the document and everything is in one long line. They also wanted the file saved with a txt extension instead of a tab extension stating tab was specific for Tableau. This is just not true. Although .tab may be an extension for Tableau, it’s not the only application associated with the extension.

The actual problem with the file is the issue with Carriage Returns (CR) and Line Feeds (LF). Older Macs use a Carriage Return and newer Macs use a Line Feed, like their Unix counterparts, including Linux. Windows requires both Carriage Return and Line Feed (CRLF). If you transfer the files via FTP in ASCII mode the conversion between platforms is supposed to be taken care automatically. This does not always work from my experience. I’m looking for a solution now and will post it once I have it. In the meantime, I’m exporting on a PC.

Although FileMaker is flexible with file formats it can import, many systems are not. Keep this in mind when exporting data for use in other systems.

Post to Twitter

Comments

  1. Is there an available and workable solution for the issue of exporting .csv files from FileMaker so there’s a Carriage Return (CR) and Line Feed (LF) at the end of each record?

    • Hi Jerry,
      When you need to get FileMaker to export into a specific format, there are usually 2 options; 1)Post Process the Export with another tool or 2)Build the entire export into a single field formatted exactly how you need it.
      I’ve had to do special formatting in the past and I usually do it by concatenating the fields into a single calculated result. Then I do one of two things, just export that field as tab-deliminated (so no quotes are added, all my field formatting for CSV is in the concatenated field and since it’s only one field, FileMaker is not adding a tab to separate fields). The other option is to loop through all the records and build the data set into a single global field and then export that single field in one record. This may be necessary when header records need to be applied or special characters need to used as record delimiters. I’ve also done this in a separate table that contains just one field and inserted different data into each record like a header and then a row, etc.
      On FileMaker’s forum there are a few threads regarding your specific subject:
      http://forums.filemaker.com/posts/d70dffd81a
      http://forums.filemaker.com/posts/1e98d64509
      http://forums.filemaker.com/posts/2188d5bca9
      Hope this helps,
      Matt

Leave a Reply to Matt Cancel reply

*