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.