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 article will also demonstrate how to set date attribute types, and to 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 a CSV format, our job will be to translate it into a Microsoft Excel format. It is important to ensure that the Excel data includes a valid date format, and we also need to split the unique identifier code into its correct format; with the three leading characters, and the remaining trailing characters, each stored in their own attribute field.
 

Step-by-step Instructions

1. Create a New Workspace
Open up FME Workbench and create a new blank workspace. Add a new Reader to the canvas and set the format to CSV. For the Dataset and select the attached CellSignals-Small.csv file, then set the Coord. System to LL84. In the parameters, confirm that Latitude and Longitude Attributes have the Value y_coord and x_coord, respectively set. Click OK twice to finish adding the reader.


2. Split Fields (Attributes) Using the AttributeSplitter Transformer
In order to split the leading and trailing characters on the Unique Identifier attribute, we will use an AttributeSplitter transformer. Add an AttributeSplitter after the CSV reader feature type and open its parameters. In the parameters, set the Attribute to Split to Code, and the Format String to 3s15s.
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
We now need to clean up the names for the attributes created while using the AttributeSplitter transformer. To do so, add an AttributeManager transformer, and connect it to the AttributeSplitter. Let’s assign new names for the values we will be reading from the list array. To do this, go to the "Attribute Value" column and from the Attribute Value selector, choose "list{}". You will be prompted to select a list element. Enter 0, to create the attribute value “_list{0}”, then assign _list{0} the Output Attribute name “ID”. Then repeat with _list{1}, with an Output Attribute name “Number”. Finally, remove _list{}, then click OK. 


4. Add a Writer and Set the Parameters
Add a Microsoft Excel writer to the canvas, save the file as CellSignals.xlsx. Set the Sheet Definition to Copy from Reader and click OK.

 
Connect the CSV writer feature type to the AttributeManager. We want to create a new Excel worksheet for each individual ID that appears in our dataset, for example, ABC, ABD, TXU, and TXV. We can achieve this by doing a feature type fanout. Open the writer parameters and for Sheet Name enter @Value(ID). Then switch to the User Attributes tab.


5. Update the Attributes and Date Type
Finally, we need to update the writer to include the two new attributes we created, as well as update the record_tstamp to be a date type. In the User Attributes tab, click on Automatic, you will see the new attributes (Code and ID) populate into the list. Switch back to Manual, and change the record_tstamp data type to datetime. Click OK to confirm the writer parameters.


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.

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.