Merge two input files in order to populate a template with variable data

fsh22 · November 23, 2020, 10:28am

Hello OL users,

I am searching for a solution to complement my input data. I have been searching through this forum and have not found a viable way yet, maybe I am missing something out. I will describe my situation step by step and hopefully someone will explain if it is possible to accomplish this with Workflow.

My first file is csv without header delimited by pipes | , example:

|1234|||||Name||||Balance||Street address|City|County|||||||||||||||Payment details|||||Data||||||||||||||||||||||||||||||||||||More|||||||||||||||||||||||MoreDATA|||||||||||||||||||||||||||||||||||||||||||||||||||||||Date||||||Time||||||||||

In this first example my address is not a complete one. After I extract my data that is necessary for my external software to complete my address, I get the second example.

Second file is a txt delimited by pipes |.

Example :

Both of these files have the same ID (eg 1234)

Any ideas?
Regards,
Fsh22

jchamel · November 30, 2020, 1:20pm

You would need to do this in a script in Workflow.
Provided that the format of these files is consistent, you could store these infos in 2 arrays (one for the original file and one for the second) where each cell of the arrays is a value from the CSV file delimited by pipe.

Then it is only a matter of grabbing the value from one array to put them in the next and saving the file.

fsh22 · November 30, 2020, 1:39pm

Hello and thank your for your reply!

Would you please provide a sample of this kind of script?

Thank you in advance!

johndoe · November 30, 2020, 2:27pm

Try this:

use a splitter to split supplementary file to separate records and store them in a temporary directory using ID field as a name
next, use a splitter to split main file to process record by record
store the contents of the main record in the variable/variables
load the supplementary file using the ID
read contents of supplementary record into variables
create a text file and construct the new merged record from variables
output the text file to a folder concatenating using CRLF

thomasweber · December 3, 2020, 1:08pm

Maybe the following vbscript will help. Otherwise feel free to change it as you need.
The script reads both files and store each line in a dictionary with the ID as Key. Read the comments in the script to understand the steps…

set data_first_file = CreateObject(“Scripting.Dictionary”) 'Dictionary first file
set data_second_file = CreateObject(“Scripting.Dictionary”) 'Dictionary second file
set fso = CreateObject(“Scripting.FileSystemObject”)

store_value “C:\TEST\first_file.csv”,1 'Call sub with parameters
store_value “C:\TEST\second_file.txt”,2 'Call sub with parameters

new_file = “C:\TEST\final_file.csv”
set newFile = fso.CreateTextFile(new_file, True) 'Create a new file and overwrite existing
for each key_first in data_first_file.keys 'Loop through all keys of first dictionary
if(data_second_file.Exists(key_first)) then 'Check if the key is found in the second dictionary
cont = Replace(data_first_file.Item(key_first),“|Street address|City|County|”,data_second_file.Item(key_first)) 'replace the specific content from first dictionary with the content from second dictionary
newFile.WriteLine cont 'write the correct content to the new file
end if
next
newFile.Close

set data_first_file = Nothing
set data_second_file = Nothing

'Sub to store values in dictionaries
sub store_value(myFile, nr)
set read_file = fso.OpenTextFile(myFile,1)

Do While Not read_file.AtEndOfStream 'Loop through first_file
lineCont = read_file.ReadLine
matchID = Split(lineCont,“|”)(1)
if(nr = 1) then
data_first_file.Add matchID, lineCont
else
data_second_file.Add matchID, lineCont
end if
Loop
read_file.Close
end sub

Hope that helps.

Regards,
Thomas

fsh22 · January 5, 2021, 5:09am

@johndoe, @thomasweber, @hamelj

Good morning and Happy New Year!

First of all I would like to thank you for your responses. In the meantime I have discovered and analyzed the request I have posted before, therefore I`m going to edit this post. What I was trying to achieve was:

Merge two input files in order to populate a template with variable data

Created a data model with Javascript empty fields.
Executed data mapping of my 1st input file(Output Metadata ID`s only)
Set properties plugin for dataset. The property was based on a RUN ID (%U), defined in a local variable.
Load External File(2nd input file)
Execute data mapping(2nd input) (Output JSON)
Run Script JSON.parse(Watch.ExpandString(“%c”));
Update Data Records ( Source JSON %9)
Retrieve items ( based on RUN ID) Output type Metadata
Create Print Content
Job Creation
Output Creation

Hope this solution is going to be useful for other users.

Regards,
Alex

Joe · January 5, 2021, 4:53pm

Hi Alex,

I’ve been trying to merge multiple data files, so was happy to see this post. I’ve only been using Pres Connect for a short time so I’m still finding my feet.
Could you expand on number 3? I’m unsure on whether this is ‘Set Properties’ within the Data Mapping or within Workflow!?
I’ve attached a screen shot of my test workflow for reference.

Any additional help would be gratefully received.

Many thanks
Joe

fsh22 · January 7, 2021, 1:42pm

Hi Joe,

Here are some screenshots:

First, try to set a local variable for your process

Second, you execute your first datamapping with Output Metadata(ID only), then you set a property for this data set.

Set property plugin with the property runId

Third, create a branch(uncheck backup job file/information) where you load your second file for the second Execute datamapping plugin, where you make output to JSON

Fourth, you update your data records

Fifth, use retrieve items plugin

Hope this helps!

Cheers,
Fsh22