With its structured hierarchy, Extensible Markup Language (XML) excels at storing and sharing data across different applications. However, a simple comma-separated value (CSV) table format shines for its easy readability and compatibility with many data analysis tools.
This conversion bridges the gap between these formats, making data more accessible to use and analyze. Whether managing databases, migrating data, or integrating with other systems, transforming XML to CSV streamlines your information into a format readily usable by a wide range of applications.
This article discusses converting XML data retrieved from an FTP server into a CSV format. It will guide you through setting up the connection, retrieving the files, and converting the data using Boomi.
Prerequisites
To get the most out of this article, you must have the following:
- A Boomi platform account: if you do not have one, create a 30-day free trial account
- A Boomi Atom is installed locally. To understand how to install a Boomi Atom on Docker, check out this Installing Boomi Atom runtime on Docker tutorial
- A basic understanding of Docker and its commands
Creating an XML data file in your project’s root directory
An Extensible Markup Language (XML) file is a file that is used to store data in the form of hierarchical elements. This data stored in this way can be easily exchanged between different computer programs and platforms because XML utilizes a standard format. This makes XML a versatile tool for sharing configuration settings, financial records, or scientific data.
Before transforming your data, you must create the XML file itself. In the folder containing your Boomi Atom, create an "ftp-data" folder. Within this folder, create a file named data.xml. Copy and paste the following code into this file to populate it with sample customer data.
<?xml version="1.0" encoding="utf-8"?>
<customersList>
<customer id="1">
<name>Aisha</name>
<email>aisha@senegal.com</email>
<country>Senegal</country>
<state>Dakar</state>
</customer>
</customersList>Connecting an FTP server to the Boomi Atom
A File Transfer Protocol (FTP) server is a digital storage locker you can access online. It uses a specific set of rules called File Transfer Protocol (FTP) to upload and download files.
You can use an FTP server as a secure transfer station to send files into your Boomi Atom, specifically the newly created XML file. This section will walk you through the process of creating and connecting an FTP server to your Boomi Atom, building upon the knowledge you acquired installing the Boomi Atom in Docker.
To achieve this, update your docker-compose.yml file with this additional information and replace placeholders with your specific values.
version: '3'
services:
atom:
image: boomi/atom:5.0.2-rhel
container_name: boomi-atom
volumes:
-<your host directory>/data:/mnt/boomi:Z
environment:
- BOOMI_ATOMNAME=<your atom name>
- INSTALL_TOKEN=<your atom installer token>
- BOOMI_ENVIRONMENTID=<your environment ID>
- ATOM_LOCALHOSTID=<your atom name>
ports:
- "9090:9090"
ftp:
image: delfer/alpine-ftp-server
container_name: ftp-server
environment:
- USERS=<username>|<password> # Use a strong password!
- ADDRESS=ftp
volumes:
- <path>:/home/ftpuser/ftp
ports:
- "21:21"
- "20:20"
- "21000-21010:21000-21010"This code block above defines two services:
I. The Boomi Atom service which is configured by the following fields:
- image:
boomi/atom:5.0.2-rhel: This specifies the Boomi Docker image used for the service. container_name: This allows you to personalize the container name, making it easier to identify and manage<your host directory>:/mnt/boomi:Z: This mounts a host directory onto the container's /mnt/boomi directory with the "Z" compression option. Make sure the host directory exists and has the necessary permissions.BOOMI_ATOMNAME,INSTALL_TOKEN,BOOMI_ENVIRONMENTID,ATOM_LOCALHOSTIDvariables: These are specific to your Boomi Atom setup and are discussed intensively in this installing Boomi Atom runtime on Docker article.
II. The FTP service, which is configured by the following fields
- image:
delfer/alpine-ftp-server: This uses the official delfer/alpine-ftp-server USERS: This sets the FTP user and passwordADDRESS: This is the external address to which clients can connect for passive ports- volumes:
<path>:/home/ftpuser/ftp: This mounts a host directory to the containershome/ftpuser/ftpdirectory. This directory must contain thedata.xmlfile - ports: This exposes the ports for connection
Converting your XML data format to a CSV format
This section walks you through a series of steps that allows you to convert your newly created XML file.
1. Collecting your XML file from the FTP server
To create the Boomi process that converts your XML file, navigate to the Integration page and click the Create New button. From the dropdown menu, select Process.
Upon selection, the Start Shape sidebar will appear. Here, opt for the Connector radio button. Then, in the Connector input field, choose FTP. You will retrieve the XML data for conversion from your newly created FTP server.
To proceed, click on the plus icon within the Connection input field.
This action will open the connection page, where you should enter your FTP credentials into the provided input field.
- Host: The
FTP_HOSTin yourdocker-compose,ymlfile - Port: Use the default port 21
- Connection Mode: Passive
- User Name: The username in the
FTP_USERSfield in your docker-compose.yml file - Password: Click the <Encyrpted> button to open a password modal. In this modal, input the password in the
FTP_USERSfield in yourdocker-compose.ymlfile
Click the Save and Close button to return to the Start Shape sidebar.
In the sidebar, click the + icon in the Operation input field. This will open the FTP operation page.
On this page, fill out the necessary input fields with the required information.
- FTP Action: Select Get to retrieve the file
- Remote Directory: Input the directory where you mounted the folder containing the
data.xmlfile in yourdocker-compose.yml - File Filter: Input ‘data’ to specify that you want to retrieve the file with the name data.
- Transfer Type: Change the transfer type from Binary to ASCII to transfer files as text
- Maximum files to Read: Leave the value at zero to signify that you want to read all the files in that directory
Click the Save and Close button, then in the Start Shape sidebar, click on the OK button to save that FTP connector.
2. Set properties of the data.
Next, you will use the Set Properties shape to dynamically set the output file name for the data coming from the Start Shape. Additionally, you will map the data values from your XML file to the fields you wish to create in your CSV file.
To ensure these processes run sequentially, the Branch shape comes into play.
At the end of the dotted line from the Start shape, you'll find a small box with a plus + sign in its middle. Click on this box to open a modal, then search and click on the Branch shape within this modal.
The Branch shape typically offers two paths by default, with a maximum of 25. However, for this tutorial, you will only need two paths.
Click on the box representing the first path from the Branch shape. Then, select the Set Properties shape. This will enable you to set values for documents and process their properties.
Click on the Set Properties shape to configure it.
In the Properties to Set section, click the + sign to open the Choose Property modal. In this modal, select:
- Property Type - Document Property
- Source Type - Connectors
- Connector - Disk
- Property - File Name
Next, click on the OK button.
You should see a Disk parameter, select this parameter, and click on the + sign in the Property Value section. This action opens up the Parameter Value modal.
In this modal, change the Type from Static to Profile Element and set the Profile Type to XML.
Then, click the + icon within the Profile input field to create a new XML profile. This action will take you to a new profile page.
Click the Import a Profile button on the new profile page.
Clicking this button will open up an XML Import wizard modal. From the dropdown in this modal, select XML File to build an XML profile.
Next, upload the data.xml file that you created initially. After uploading, click on the Next button.
You should now see the Profile Loaded modal.
Click the Finish button to see the XML profile.
Click the Save and Close button to return to the Parameter Value modal.
In the Parameter Value Modal, click the Element field, expand the menus, and select the name element in the customer object. This specifies that each XML file should be titled in the customer’s name.
To set the default type value of the files to a .xml file, create another Parameter value. Click on the + sign in the Parameter Value section. This action opens a Parameter value modal. In this modal, choose a Type of Static, and input .xml in the Static Value field.
Click the OK -> OK buttons.
3. Creating the Map source and destination data Profiles
Boomi profiles describe the layout or format of the documents read into or sent out of processes. This section will create profiles the describes the source XML file and the expected destination or output CSV file.
To create these profiles, click on the second branch from the Branch Shape and search for the Map Shape.
To configure the Map Shape, click on it. This action will open the Map Shape sidebar. Within this sidebar, click on the + icon located in the Map input field to open a new Map page.
In the Map source section, click on the Choose link at the top right of the Map source section.
This opens up a Choose a Source Profile modal. In this modal, select a Profile Type of XML, and for the Profile input field, select the profile you just created.
Click on the OK button to see that section populated with your object from your XML profile.
In the Map destination part of the page, click on its Choose link to open up the Choose a Destination Profile modal.
In this modal, select a Profile Type of Flat file, and click the + button in the Profile input field to create a new profile for the CSV file.
This opens up a New Flat File Profile page. Right-click the downwards-facing arrow next to Elements on this page, and select Add Multiple Elements. This opens up a modal that asks you to choose how many elements you'd like to add. This tutorial decides to create six new elements. Click on the OK button to close the modal.
Next, click on each of the newly created elements and change them so that they can respond with the field in your XML profile. This tutorial creates fields for name, email, country, city, id, and current date.
Click on Save and Close and the OK buttons to save this profile.
4. Mapping the XML data fields to the CSV data fields
Next, click on a field in the Map Source on the left-hand side, drag a line from the field to their corresponding field on the Map destination, and do this for all the fields. For example, in the diagram below, the following fields are mapped together:
- id -> id
- name -> name
- email -> email
- country -> country
- state -> city
After you are done, you will notice that the current date field does not match with any data. To get the current date, click the + button in the Functions part of the Map page.
Clicking on the + button opens up an Add a Function modal; in this modal, select a Category of Date, click the Get Current Date function, and Click the OK button. You should see a small box titled Get Current Date in the middle of your Map.
Next, drag the line from the Results point of the box to the current date menu, then click the Save and Close button. Next, click on the OK button in the Map shape sidebar.
5. Configuring the Disk Connector
The Disk connector gets files or sends files to directories on the disk to which the Atom has access, so once the Map shape transforms the data from XML to CSV, the Disk connector stores this transformed data on your atom.
To add the Disk connector to your Boomi process, click on the box along the dotted lines that lead out of the Set Properties shape. Then, search for the Disk connector and click on it to open the sidebar.
Change the Action from Get to Send in this sidebar, as the Disk connector will send out the data. Then, click the + sign in the Connection input field, which opens up the Disk connection page. On this page, input the directory where this newly converted file will live in your atom.
Next, click the Save and Close button to return to the Connector Shape sidebar.
In the Operation input field, click the + sign to create a new Operation for the sidebar. The Disk Operation defines how to read files from or write files to a given directory.
In this Operation page, tick off the Create Directory if it doesn’t exist checkbox. This allows the Disk Connector to create a new directory if the directory you specified in the connection does not exist.
Next, select what you want the Disk Connector to do in the event that the file you are trying to convert and store already exists in the directory. This tutorial chooses Create unique name if file exists. Next, click on the Save and Close button, and then click on the OK button.
Connect the lines from the Map Shape to the Disk connector so that the Disk connector can store the CSV file from the Map Shape.
Lastly, connect the Disk Connector to the End and Continue Shape.
Your complete Boomi process should look like this.
6. Testing the Boomi process
To test this process, click the Arrange tab at the top of the canvas to straighten the lines and arrange the connections. Next, select the Test button on the top right of the canvas page.
To verify the success of your process, click on the Disk Connector, and check out the Shape Source Data. In this tab, you should see two files.
The first file is the XML file, and the second is the newly created CSV file.
Since you specified a directory, you wanted the CSV file to be stored in your Atom.
In your project’s root directory, open the folder you specified in your Boomi Atom volumes mount. Navigate to this folder <Atom name>/<directory you inputted in the disk connector>. Here, you should see both your XML file and CSV file.
That’s a Wrap!
This guide has discussed how an Integration Platform as a Service (iPaaS) can convert data from one format to another, making it more compatible with various programs. In this instance, the conversion was from XML data format to CSV format. However, this is just a glimpse of what iPaaS solutions, particularly Boomi, can offer. Please refer to the official documentation for a deeper understanding of Boomi's extensive capabilities.
