Converting JSON, XML, YAML, CSV, or Excel to and from a SQL database in common data integration scenarios
Many data integration scenarios will have you reading and writing data files in various formats to or from an SQL database.
Most modern RESTful APIs will support a JSON or YAML payload and response. This demo will show you how to generate a JSON or YAML payload from a SQL database or take a response in JSON or YAML format and write it to a SQL database.
Many legacy systems or APIs with more complex data structures use XML to process data. This demo will also show you how to generate XML from a SQL database or take an XML file generated by another system and write its contents to a SQL database.
Finally, flat files remain one of the most commonly used data types used within organizations today. Whether they be Excel spreadsheets or comma delimited text files, flat files form the backbone of the way many organizations prepare, analyze, and exchange data. This demo will also show you how to visually define a data model from a flat file, automatically generate services to read/write/update the data file, and map the data file to/from a SQL database.
How to download and run this "Data files to & from a SQL database" demo
- If you haven't already done so, get yourself a Martini.
- Within either Martini Desktop or Martini Online select the "Data Files to and from SQL" demo from the Welcome Screen and click Install:
- The Package demo007-data-file-to-from-sql will be automatically downloaded. Right-click the Package and click Start. A green icon will indicate it has started:
- Documentation containing a Test Procedure to run the demo is included in the Package readme file \resources\readme\readme.md. The readme file is automatically opened when the Package is started.
- The Test Procedure will guide you through the process to run demo services that convert all of the following data formats:
- JSON file to SQL database
- XML file to SQL database
- YAML file to SQL database
- CSV file to SQL database
- Excel file to SQL database
- SQL database to JSON
- SQL database to XML
- SQL database to YAML
- SQL database to CSV
- SQL database to Excel
Line by Line: how this "data files to & from a SQL database" demo works
This demo uses the embedded HSQL database but the process is exactly the same for any of the other SQL databases supported by Martini including MySQL, PostgreSQL, Microsoft SQL Server, Oracle, or DB2. The database connection string to the embedded database is automatically created by a startup service.
Reading and converting data file format to a different data file format.
While there are several services present in the demo for reading and saving various data file formats to a different data file format, the functions used, and the data reading and extraction logic are the same.
For reading formats like JSON, XML, or YAML, you will notice in the respective services that each service starts with File.resourceStream(line 3) and the corresponding function for getting the data by its InputStream.
For reading formats like CSV and Excel, similar to the other formats, it also starts with File.resourceStream. The difference for these data formats is that it uses the generated service from its corresponding Flatfile descriptor, with the input to the service being an Input since the service will be reading a file to get its contents.
The service flow for the following formats is as follows.
- Line 3: A placeholder model is declared, called contact (this step is only when converting a file format to an XML, JSON & YAML)
- Line 4-5: The contents of the selected data file format are pulled.
- Line 6-7: Each of the records in the pulled will be mapped to the contact model.
- Line 8-9: An OutputStream File.packageOutputStream function is called to create an OutputStream where the data can be written. then is written to the OutputStream using the File.write function.