How to Convert CSV to Microsoft Excel

Introduction

In this article, we will perform a simple transformation, converting a dataset from CSV to Microsoft Excel format. This process demonstrates how to:

• Set date attribute types
• Split string (text) data into separate fields

The dataset includes the location, quality, date of inspection, and a unique identifier (“Code”) for all cell towers. Since this information has been supplied in CSV format, our goal is to:

• Translate it into Microsoft Excel
• Ensure valid date formatting
• Split the “Code” field into two new attributes

Step-by-step Instructions

1. Start FME Workbench and Add CSV Data

Start FME Workbench, and click on New to open a blank workspace. 

First, we need to add a source dataset. Click on the Reader button. In the Add Reader dialog, enter the following:

• Format: CSV (Comma Separated Value)
• Dataset: CellSignal-Small.csv
  • Click on the ellipsis and browse to the downloaded dataset

Then click OK.

This dataset contains location information, which FME can use to create point features. In this example, we will not be using the location information, but if you would like to learn more, see Part 2: Inspecting Spatial Data in Viewing and Inspecting CSV Data. 

2. Split Fields (Attributes) Using the AttributeSplitter Transformer

We need to split the “Code” field into two new attributes: one for the first three characters and one for the trailing characters. To do this, we will use an AttributeSplitter transformer. 

Add an AttributeSplitter transformer to the canvas, and connect it to the CSV reader feature type. In the parameters, set:

• Attribute to Split: Code
• Delimiter or Format String: 3s15s

Then click OK. 

The Format String uses a #s#s#s format - integers defining the width of each part, separated by an “s” character.  This will split the field (attribute) into the leading three characters and the trailing characters, up to 15, and populate them in a list array.

3. Set Fields (Attributes) using the AttributeManager Transformer
The AttributeSplitter creates a list containing all of the split attribute values. To make it easier to read, let’s create two new attributes with the list values, update the data type for record_tstamp to format it correctly in Excel, and remove the extra _list attribute.

Add an AttributeManager to the canvas and connect it to the AttributeSplitter. In the AttributeManager parameters, create the following:

• Update Attribute:
  • Input Attribute: record_tstamp
    • Type: datetime
• Add Attribute:
  • Output Attribute: ID
    • Value: _list{0}
  • Output Attribute: Number
    • Value: _list{1}
• Remove Attribute:
  • Input Attribute: _list
    • Action: Remove

Then click OK.

The AttributeManager performs each attribute action top down, so the ID and Number attributes need to be above the _list attribute to use the _list attribute values prior to removing _list. Use the arrows to reorder the attributes if needed.

For more information on working with lists, see Tutorial: Getting Started with List Attributes. 

4. Add a Microsoft Excel Writer
Now the data needs to go to the destination. Click on the Add Writer button on the toolbar. In the Add Writer dialog, enter the following:

• Format: Microsoft Excel
• Dataset: CellSignals.xlsx
  • Use the ellipsis and browse to a location to save

Then click OK.

After clicking OK, the Feature Type dialog appears, click OK to close it for now, as we will need to connect it to our data. Connect the Sheet1 writer feature type to the AttributeManager. 

5. Set Sheet Names

We want to create a new Excel worksheet for each individual ID that appears in the dataset, for example, ABC, ABD, TXU, and TXV. We can achieve this by doing a feature type fanout. 

Double-click on the Sheet1 writer feature type to open the parameters. In the parameters, set:

• Sheet Name: @Value(ID)

Then click OK.

To set the Sheet Name to an attribute, click the drop-down arrow, then expand Attribute Value and select ID. 

6. Run the Workspace
Run the workspace, then open the containing folder and view the newly created Excel file in Microsoft Excel. There will be four worksheets, two new attributes, and the record_tstamp will be recognized as a datetime format.

For more information on working with Microsoft Excel, see Tutorial: Getting Started with Microsoft Excel.

 

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.