Skip to main content

Hi everyone,

I have struck a roadblock in the creation of a particular FME Workbench that I now require help with. I appreciate your time in advance.

I have multiple csv files that contains a table of data that I would like to process into a single csv file table. 
Lines 1-26 of these files contains general information
Line 27 contains headers for a table below
Line 28 onwards contains the data for the table. 

In Line 2 Row 2 there is a “cell” (B2) which contains text which I would like to add into a new column in the table repeatedly.

I have done and been able to do the following:
- Used 2 x CSV Readers
- 1st CSV Reader data is transformed down to just cell B1 and B2, with B1 text being replaced with a header name 'Sensor_ID' and B2 simply being the original data in B2 eg. SN_ABC123
- 2nd CSV Reader data is transformed to Line 27 onwards (i.e. just showing the table), plus a few other modifications using AttributeRenamer, AttributeRemover etc.
- Tried using both FeatureMerger and FeatureJoiner to merge the 1st CSV Reader data with the 2nd, such that 'Sensor_ID' becomes a new header for a new column with the data from B2 (eg. SN_ABC123) being populated the full extent of the column.

The problem I have is that when working with multiple CSV files (I sometimes process up to 40) the data from Sensor_ID does not match up correctly with the data in the table. i.e. the wrong Sensor_ID eg. SN_ABC123, with the wrong values in the table.

I am using FME Workbench 2024.1. I have tried joining with FeatureMerger and FeatureJoiner. I have tried joining via a name like FME_basename and FME_Dataset. 

I have attached two modified csv files as examples of the raw data to be processed in FME. The output I want is something like in the 3rd file.

Any help would be greatly appreciated.

Thanks,

Luke

Hi ​@lukeh81 

I would suggest using a workspacerunner. I set up an example of how to set it up. This way you can read through each csv in a folder and then run your processing adding the results to the same spreadsheet (insert). 
Test Batch Processing Method 3: Using the WorkspaceRunner – FME Support Center
Hope that helps.

I would use this sort of workflow, rather than merging

You can use a text file reader to read just the second line and extract the Sensor_ID, keep that attribute and the filepath and send to a second feature reader to read the CSV file from 27 onwards, if you merge initiator and result the Sensor_ID relating to the file will be added to all features.

​@lukeh81 

I am not sure if this idea would be acceptable to you, but still thought of sharing.

The solution is a two step process:

Step 1: Create a lookup csv file containing two columns, first column is the name of the csv file that you are processing and the second column for the Sensor_ID.

For the CSV file name, the fme_basename format attribute needs to be exposed on the Reader Featuretype.

For this step, the workspace will read all the CSV files of interest and create a sensor_id_lookup CSV file.

The output CSV would be something like this:

The CSV Reader would read only the first two lines.

Step 2:

This step involves a CSV to CSV workspace reading from row 27 (header) and 28 (data) with the a Reader FeatureType set to Merge Featuretype

Again, for the featuremerger down the line, the fme_basename format attribute needs to be exposed on the Reader Featuretype.

You will add another CSV Reader for the Sensor_ID lookup.

Then use FeatureMerger to merge based on the fme_basename for the CSV files featuretypes and the csv_name value from the Sensor_ID lookup csv.

The workspace would look like this:

 

I also noticed that the CSV files contain empty lines like “,,,,,”, which if you need to skip, then you will need to carryout additional checks.

Hope that helps.

Happy FME:-) ing

Cheers

SRG

Thanks everyone. I appreciate your responses and will give them all a shot. Regardless of what works best for me, I will have learnt some new FME tricks.

Cheers,

Luke 

Community Stats

32,029
Posts
121,640
Replies
39,655
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.