Viewing and Inspecting CSV Data

Introduction

Comma Separated Value (CSV) data is a common way to store values in a transferable format that can be read by a large number of programs. As CSV data mimics the tabular format of rows and columns, FME is able to easily read this as a table format for a user to inspect the records to determine how best to approach their upcoming workflow.
In this article, we will inspect CSV data using Visual Preview in FME Workbench. Inspecting your data before you work with it is a good habit to practice. If there is any missing data or any misaligned records, as coinciding with the CSV header, it will help to have a table formatted view of the data. This way, we can decide how to handle the errors before processing the data.
Remember, even though, by definition, a CSV has values separated by a comma (“,”), FME is able to make use of other delimiter characters, as long as the character is used exclusively as a separator and not used as part of a value.
 

Step-by-step Instructions

Part 1: Inspecting Tabular Data

1. Start FME Workbench and Add CSV Data
Start FME Workbench. In a new workspace add in a Reader by clicking on the Reader button. In the Add Reader dialog, select CSV (Comma Separated Value) as the Format, then browse to Census2016.csv. The dataset is a tabular (non-spatial) census data from 2016 for the City of Vancouver. 


2. Set the Parameters
Before we can inspect the data in Visual Preview, we need to ensure that the expected formatting is correct.
In the reader Parameters, under the Fields section, there are three parameters: Delimiter Character, Field Names Line, and Data Start Line. The Delimiter Character is the character that separates each attribute in the CSV file. Usually, this is set to a comma "," but it can be other characters too like a semi-colon ";", a pipe "| " or a tab or multiple spaces. The Field Names Line refers to the line that will be used as the attribute headers (attribute names) once read. Finally, the Data Start Line refers to the line of the CSV where the records (attribute values) start.
For this example, we will keep the delimiter set to auto, which will scan the file for the most common character and determine that as the delimiter, in this case, it will be a comma. Notice that at the top of the Preview, the first four lines are not the start of the table, they are header information about the table. We can ignore these lines by setting the Field Names Line to 5, and FME will automatically change the Data Start Line to 6. If your data starts on a different row, this can be manually set. 
 
Finally, before clicking OK, a good practice when working with CSV is to change the name that will be displayed on the reader feature type. By default Feature Type Name(s) is set to  From Format Name, which will result in “CSV’ being shown. Instead change this parameter to From File Name(s), which will result in the name of the file being displayed. This is useful when you have more than one CSV reader on the canvas. Click OK twice until your data has been added.


3.  Open Visual Preview
After clicking OK in the Add Reader dialog, a reader feature type will appear on the canvas labeled CSV. To view the data in Visual Preview, click on the CSV reader feature type to open the popup menu, then on the popup menu click on the View Source Data button.


The View Source Data button will open Visual Preview. If FME Data Inspector opens, you are most likely running an older version of FME or have the Inspect with Data Inspector when Visual Preview Window is Closed option enabled. For more information about using Visual Preview, see Getting Started with FME Desktop: View Data and for information about FME Data Inspector, see Getting Started with FME Desktop: Introduction to FME Data Inspector.

Part 2: Inspecting Spatial Data

1. Open a Blank Workspace and Add CSV Data
Start a blank workspace in FME Workbench and add in a Reader by clicking on the Reader button. In the Add Reader dialog, select CSV (Comma Separated Value) as the Format, then browse to wayfinding-map-stands.csv. This dataset is spatial (contains latitude and longitude coordinates) and is of map stands around downtown Vancouver. Open the Parameters.


2. View the Parameters
In the CSV Parameters, we need to confirm that the x and y coordinates have been set correctly before we can continue. FME can auto-detect when a format has latitude and longitude coordinates in the dataset, and will automatically set them to y_coord and x_coord, respectively. In the Parameters dialog, under the Attributes section, ensure that Latitude and Longitude have y_coord and x_coord assigned as the value. If they do not, set the Attribute Definition to Manual and then correct the Values.
 
While in the Parameters dialog, confirm that the Preview is showing the table correctly, if not, review Step 2 in Part 1. Finally, change Feature Type Names to From File Name(s)s, then click OK, but leave the Add Reader dialog open.  


3. Set the Coordinate System
Before you click OK again in the Add Reader dialog, we need to define the coordinate system if you know this information beforehand. This will allow you to see the spatial data overlaid onto a background map (once setup) for better context.
For our example, enter LL84 into the Coord. System box, then click OK to finish adding the Reader.


4. Open Visual Preview
After clicking OK in the Add Reader dialog, a reader feature type will appear on the canvas labeled CSV. To view the data in Visual Preview, click on the CSV reader feature type to open the popup menu, then on the popup menu click on the View Source Data button. 


5. Inspecting the CSV Data
If your reader has been set up correctly, both the Table View and Graphics View will display data. You may need to enable each view by clicking on the view buttons on the left-hand side of Visual Preview. If there is no data being displayed in the Graphics View, the x_coord and y_coord, or the Coord. System may not have been set up correctly, review steps 2 and 3. See Viewing Data for more information on interacting with Visual Preview.

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.