Skip to main content
Solved

Multi CSV w/ Dual Reader Merging Help

lukeh81
Participant
Forum|alt.badge.img+2

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

Best answer by raghavendrans

​@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

View original
3 Attachments
Luke
crutledge
1 person upvoted this
  • crutledge
    crutledge
Did this help you find an answer to your question?

4 replies

crutledge
Influencer
Forum|alt.badge.img+32
  • crutledgeInfluencer
  • Influencer
  • May 21, 2025

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.

1 Attachments
At your rest service ^B
lukeh81
1 person upvoted this
  • lukeh81
    lukeh81
ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • May 21, 2025

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.

geomancer
crutledge
lukeh81
3 people upvoted this
  • geomancer
    geomancer
  • crutledge
    crutledge
  • lukeh81
    lukeh81
raghavendrans
Enthusiast
Forum|alt.badge.img+14
  • raghavendransEnthusiast
  • Enthusiast
  • Best Answer
  • May 21, 2025

​@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

crutledge
lukeh81
2 people upvoted this
  • crutledge
    crutledge
  • lukeh81
    lukeh81
lukeh81
Participant
Forum|alt.badge.img+2
  • lukeh81Participant
  • Author
  • Participant
  • May 21, 2025

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 

Luke
crutledge
raghavendrans
2 people upvoted this
  • crutledge
    crutledge
  • raghavendrans
    raghavendrans

Reply


Helpful Members This Week

Community Stats

31,327
Posts
118,859
Replies
38,955
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.