Merge two flat files into a single flat file

, ,
1

Hi, I am new to webMethods. I need to merge two flat files into a single flat file using the same customer_ID.

customer.csv
customer_ID, customer_name, customer_email, customer_phone
101, John Doe, john@example.com, 1234567890
102, Jane Smith, jane@example.com, 9876543210

transaction.csv
customer_ID, product_name, product_price, transaction_number
101, Laptop, 1500, TXN001
102, Keyboard, 50, TXN003

expected output is
combine.csv
customer_ID, customer_name, customer_email, customer_phone,customer_ID, product_name, product_price, transaction_number
101, John Doe, john@example.com, 1234567890,101, Laptop, 1500, TXN001
102, Jane Smith, jane@example.com, 9876543210,102, Keyboard, 50, TXN003

here I have to match both flat-file comparing customer_Id.
webMethods

2

Hi Zaheer,

which version of webMethods are you running on?

Outline for your issue:

  • read first file via Flatfile Document Type
  • read second file via Flatfile Document Type
  • LOOP over the first file
    ** perform an pub.query:XQuery on the second file with customer_ID as key.
    ** combine the data from both files and add the result to the target FlatFile Document Type.
  • write the final target file.

You will find more detailed informations in the IS Built-In-Services Reference guide for the services to be used.
If existent, you can have a look at the FlatFile-Users-Guide as well.

Regards,
Holger

3 Likes
3

Hi @zaheer_baig ,

You can try a POC by referring below logic.

Need 3 Flat File Schemas to be Created for Each FlatFile Definition
(Alternatively you may create 3 Flat File Dictionaries and refer then in one same Flat Schema or 3 Different Flat File Schemas)

  1. One for customer.csv containing Field Definition for customer_ID, customer_name, customer_email, customer_phone
  2. One for transaction.csv containing Field Definition for customer_ID, product_name, product_price, transaction_number
  3. One for combine.csv containing Field Definition for customer_ID, customer_name, customer_email, customer_phone,customer_ID, product_name, product_price, transaction_number

Flow Logic to be something like

pub.file:getFile *(get customer.csv)*
pub.flatFile:convertToValues *(convert customer csv data to IS Document using Flat File Schema)*
pub.document:groupDocuments *(group the customer DocumentList using Key as customer_ID)*
pub.file:getFile *(get transaction.csv)*
pub.flatFile:convertToValues *(convert transaction csv data to IS Document using Flat File Schema)*
pub.document:groupDocuments *(group the transaction DocumentList using Key as customer_ID)*
LOOP over Grouped Customer DocList
            LOOP over Grouped Transaction DocList
                     BRANCH
                          IF (customer/customer_ID)==(transaction/customer_ID)
                                 MAP *the fields to the Combine csv Temp Document Structure*
                                  pub.list:appendToDocumentList  *(append each Doc to Final DocList)*
pub.flatFile:convertToString (convert the Final Combine DocList to FlatFile String/CSV)
4

Hi Kailash,

when using the XQuery service to pick the values from transaction document list via customer_ID you will avoid the inner loop and branch.
Additiionally there is no need to group the documents before looping over them.

Regards,
Holger

1 Like
5

In addition to Holger’s solution, if both flat files have same indexes, you can map them in a single loop by indexing the second document list using the $iteration as index. Beware though, its highly unlikely they will have the same index.

1 Like
6

Hi @Holger_von_Thomsen,

Thanks for your inputs.
Can you please share the full namespace of the XQuery flow service. Would like to view it in Designer and check it’s Documentation in the IS Built-In Services Guide to understand it’s usage

7

Hi Kailash,

sure, here it is:
pub.xml:queryXMLNode

Regards,
Holger

1 Like