0

I was following the answer in this stack overflow question. But I couldn't get the output I am expecting to get. I have a sample csv, which looks like this:

id,name,city
12,Jimmy,Ontario
33,Kimmel,York

Every city has a unique code, which I have stored in another csv.This is how my csv to be used to map looks like. (I have separated the two values in the row using a tab in the real txt file)

California   5435
Ontario   2342
York   3456

The final output must be like the following:

id,name,city,code
12,Jimmy,Ontario,2342
33,Kimmel,York,3456

This csv has much more data therefore the replacement cannot be achieved by using ReplaceText Processor. So it can be done only by using the ReplaceTextwithMapping Processor.

I have followed the exact steps used as an answer in this question. But it seems like the Replace TextwithMapping is not working as expected. Since a new column is made successfully. But it's just contains the same content of the city column not the codes I want.

Its greatly appreciated if you could submit an answer that I can follow to finally succeed in getting the desired output using ReplaceTextwithMapping Processor

Improve this question

1 Answer 1

Reset to default
3

I could make it work kind of using LookupRecord. The overall flow looks like this:

enter image description here

GenerateFlowFile:

enter image description here

LookupRecord:

enter image description here

Configure CSVReader and CSVRecordSetWriter to treat the first line as header line. For the other properties leave the defaults. Configure CSVRecordLookupService:

enter image description here

The mapping file has following content:

city,code
Ontario,2342
California,5435

The LookupRecord processor will take the city value and lookup the proper record from the mapping file. It will extract the code and add the field to the current record. Result:

enter image description here

As you can see the code has been added to the CSV file. However it is enclosed in an object, although I have selected Insert Record Fields setting. I could not solve this problem. This could be another question to ask explicitly.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks. This is close to what I am expecting. But I need only the code in my csv. Also as you also answered [this] (stackoverflow.com/questions/58553048/…) question. I realised that the regular expression is wrong. Cant this problem be solved using the same concept.
@HimsaraGallege I believe that this approach is the better one, because it does not require the additional step of cloning a column. However I revamped the question you've linked and it is working now. The challenge for you will be to find a proper regex expression. debuggex.com will help you to get started.
I followed the question I linked in mine. It works :) Thx.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.