Table of Contents
- About the Project
- Getting Started
- fmapi Product Suite
About The Project
I was browsing over at the FileMaker forums and came across a question about parsing CSV data.
This caught our interest and made us ask the question.
Could we take some CSV data and transform it into a valid JSON Object?
A FileMaker script which when passed a valid CSV string, will covert this string into a JSON object.
- FileMaker Pro
- No 3rd party plugins.
Minimal Dependencies: This script depends only on 2 custom functions and no plugins.
Specify a Custom Delimiter Character: By default we use the comma character but you can pass a custom delimiter.
Ignore Empty Columns: You can specify a setting that allows the JSON to ignore empty columns.
The FileMaker script makes use of the JSON Functions introduced in FileMaker 16.
This means that this script will only work with FileMaker 16+ products.
Using the script with anything less than 16 will have unexpected behaviour.
We make use of the #Name-Value custom function provided by filemakerstandards.
Please copy the following Custom Functions to your solution before copying the script:
Large CSV Data
FileMaker Pro is not the most efficient tool for text parsing. This script was designed primarily for small CSV data. It wasn't designed, in mind, to convert large CSV files into JSON.
However, if given enough time, this script will convert large CSV files into JSON and has been optimised to the best of our ability.
Unfortunately, FileMaker can only go so far before it starts to fall over.
If you do have this need, we strongly suggest using a plugin or performing the conversion outside of FileMaker and then importing the result back into FileMaker.
FileMaker Text Parsing Functions
We have built the rules for identifying CSV columns in FileMaker using all the Text Functions available to us and from our testing it has handled all the testing CSV data thrown at it.
However, there may be some fringe cases where this breaks down. If you discover such a case, please create a Bug Report and we'll see if we can correct it. Unfortunately, there may be cases that can't be solved.
- Make sure that the Custom Functions have been added to your solution.
- Copy the
fm-csv2jsonscript to your solution.
There are 2 fmp12 files provided here
fm-csv2json.fmp12 file contains the script and custom function that you need to copy over.
fm-csv2json-tests.fmp12 file contains our test suite to confirm that the script behaves as intended.
If you open the
fm-csv2json file, you can paste in some xml and convert it to a json object by pressing the Convert button.
The below parameters can be used as
# ( name ; value ) parameters for the script and must be passed:
||The CSV String you want to turn into a JSON|
||Fully Quanlified field name for storing the result.|
The below parameters can be used and are optional:
||Tells the parser that the first row is headers, and should be used as a JSON key.|
||Will make the parser try to determine the correct JSON type. e.g. "15.00" will be used in JSON as 15.|
||The character being used to group columns between delimiters.|
||The charater being used to distinguish columns.|
||Will trim any leading or trailing whitespace from header and values.|
||Will ignore any columns that don't have data in them.|
Contributions are what make the open source community such an amazing place to be learn, inspire, and create. Any contributions you make are greatly appreciated.
- Fork the Project
- Create your Feature Branch (
git checkout -b feature/AmazingFeature)
- Commit your Changes (
git commit -m 'Add some AmazingFeature)
- Push to the Branch (
git push origin feature/AmazingFeature)
- Open a Pull Request
Distributed under the MIT License. See
LICENSE for more information.
Project Link: fm-csv2json
fmapi Product Suite
When FileMaker 16 introduced cURL with the Insert from URL script step. Use cases for FileMaker Pro increased dramatically in relation to REST APIs.
It allowed developers to finally communicate with other web services and APIs without the need for 3rd party plugins. Integration with Couriers, Payment Gateways & Social Media Sites all became within touching distance.
However, without prior knowledge of cURL, HTTP Request Methods, HTTP Headers, JSON, OAuth Authentication, API Keys, API documentation etc, it can be extremely difficult to get started for the novice user or even the most proficient FileMaker developer.
And with that comes our goal, to simplify communications between your FileMaker App and the vast amount of Web Services available, no matter your ability level.
Read more over at What is fmapi?
A collection of FileMaker apps that communicate directly with popular 3rd party REST APIs.
- fmapi-vies-vat - Integrate the EU Commissions Vies VAT API directly into you FileMaker App allowing you to validate EU VAT Numbers.