How to convert XML files to CSV format using Boomi in Docker
Tags: ipaas, docker, formatting, aiops
Published: 2024-04-03 07:23:51

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](https://boomi.com/)
* 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](https://eyer.ai/blog/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](https://blog.hubspot.com/website/what-is-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

<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](https://www.hostinger.com/tutorials/what-is-ftp) is a digital storage locker you can access online. It uses a specific set of rules called [File Transfer Protocol (FTP)](https://www.fortinet.com/resources/cyberglossary/file-transfer-protocol-ftp-meaning#:~:text=FTP%20(File%20Transfer%20Protocol)%20is,to%20communicate%20with%20each%20other.) 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](https://eyer.ai/blog/installing-boomi-atom-runtime-on-docker/).

To achieve this, update your `docker-compose.yml` file with this additional information and replace placeholders with your specific values.

```yml
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_LOCALHOSTID` variables: These are specific to your Boomi Atom setup and are discussed intensively in this [installing Boomi Atom runtime on Docker article](https://eyer.ai/blog/installing-boomi-atom-runtime-on-docker/). 

II. The FTP service, which is configured by the following fields

* `image:  delfer/alpine-ftp-server`: This uses the official [ delfer/alpine-ftp-server](https://github.com/delfer/docker-alpine-ftp-server)
* `USERS`: This sets the FTP user and password
* `ADDRESS`:  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 containers home/ftpuser/ftp directory. This directory must contain the `data.xml` file
* `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 allow 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. 



![The Boomi Start Shape ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hyryn4d36nkwhnkbka79.png)


This action will open the connection page, where you should enter your FTP credentials into the provided input field.

* **Host**: The `FTP_HOST`` in your `docker-compose.yml` file
* **Port**: Use the default port 21
* **Connection Mode**: Passive
* **User Name**: The username in the `FTP_USERS` field  in your docker-compose.yml file
* **Password**: Click the **&lt;Encyrpted>** button to open a password modal. In this modal, input the password in the `FTP_USERS` field in your `docker-compose.yml` file




![The Boomi Start Shape FTP connection tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uydqlm7chhfk4ibjp9vo.png)


Click the **Save and Close** button to return to the **Start** Shape sidebar.

Click the **+** icon in the **Operation** input field in the sidebar. This will open the FTP operation page. 



![The Boomi Start Shape FTP Operation Input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zlfb4dg4hzzgu0eu0e4a.png)


![The Boomi Start Shape FTP operation page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xpv1pvck0irnh1kj35v4.png)


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.xml` file in your `docker-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.  

![The Boomi Start Shape Operation Page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0o3h6f29kijjxy8j728a.png)



**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 Find a step or connector modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dfgz2n8q0fd347jwudeb.png)


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.




![The search for the Boomi Set Properties shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/92baifwb752fe18yupsl.png)


Click on the **Set Properties** shape to configure it. 

![The Set Properties Shape tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7dmwacjkutkmr9cjz24p.png)


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




![The Choose Property modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/75ps0o4942020pkua0ri.png)


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. 



![A newly created Disk Parameter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/flo2xfwko882dexm5phw.png)




![The Parameter Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5swod7apcnnwope33soj.png)


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.




![The Parameter Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t8iem2ubg4r3j3nmvqr2.png)


Click the **Import a Profile** button on the new profile page.




![The Boomi XML Profile page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y6ai6pomzmx3f7eq8l6n.png)


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.




![The Boomi XML Import Wizard modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vkoakzu16e3avf5uvp8g.png)


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.


![The Boomi XML Import Wizard modal with the imported data.xml file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4w2129xv2cm8osy56rpo.png)


 Click the **Finish** button to see the XML profile. 


![The newly created XML profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ds6nd0hszvs0t5qbrjuc.png)


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.




![The Parameter Value modal with the loaded XML profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d4k8jil6u9bljmkv03xx.png)


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.




![The Parameter value that enforces that the file type should be .xml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzsmu74klyx4dpagp2zd.png)


Click the **OK -> OK** buttons.



**3**.**Creating the Map source and destination data Profiles**

[Boomi profiles](https://help.boomi.com/docs/atomsphere/integration/process%20building/c-atm-profile_components_e9b3ea44-7b4a-4d1e-8185-e09e429275f6/#:~:text=Profiles%20describe%20the%20layout%20or,Profiles%20are%20used%20in%20maps.) describe the layout or format of the documents read into or sent out of processes. This section will create profiles that describe 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.




![The modal search results for Map](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kwg9dsrqz5tzpl756lmt.png)


To configure the **Map** Shape, click on it. This action will open the **Map** Shape sidebar. Within this sidebar, click the **+** icon in the **Map** input field to open a new **Map** page.




![A new Map page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/77qdzm2b1jlh4nvqece0.png)

In the **Map** source section, click on the **Choose** link at the top right of the Map source section. 




![The Choose link in the Map source ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1bljaghsqqgxbh7mvqyi.png)


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.




![The Choose a Source Profile modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gg0qfwln7xb1vqch555j.png)


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.** 




![The Elements page of the Map destination profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6stiy9jl6zy8uub054qi.png)

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




![The XML fields mapped to the CSV fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oo7qcn0cinf5wzinsp3d.png)


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.




![The button that allows us to create Boomi functions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3cw8tex1bp67kmetg8h0.png)


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.




![The Add a Function modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zuygryfimx6rk6z4u5b8.png)




![The Get Current Date function](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0mla5y5hvu32exr8fjfc.png)


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.


![The results field mapped to the current date field on the Map destination profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8g1ngvsnrotyj04xumwz.png)

**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. 




![The Boomi Disk Connector connection page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vl5kaswou8yxouc11vym.png)


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.



![The Boomi Connector operation page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6f1j67v1yx9auvtpbdvr.png)

Connect the lines from the **Map** Shape to the **Disk** connector so 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.



![The complete Boomi process for transforming XML files to CSV](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hcifm3oewjxx75z7rngp.png)




**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 Shape Source Data tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/683mibs2zwrnmzskrwgg.png)

The first file is the XML file, and the second is the newly created CSV file.




![The original XML file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jm9wfq7de4pk74wsmbfw.png)





![The newly created CSV file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/36lzd8ujv6i3v4eyukfk.png)


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.




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2f328866twffd1vaovpq.png)



## 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](https://help.boomi.com/) for a deeper understanding of Boomi's extensive capabilities.