In the article I will describe how to create a flat file schema from a given file and use it to parse the fie and convert it to json or xml. I will also show how to use a ftp port to send and receive the files.
The complete sample code can be found here on Github.
webMethods Designer and an Integration Server or Microservice runtime >= 10.5 or just webMethods Service Designer. You can get Service Designer from this page in our TechCommunity. If you need guidelines how to get it running, please have a look at the first section of my article Build a microservice converting csv flat file to xml or json using Service Designer.
The flat file I use is oriented on the bank statement of a bank:
Auftragskonto;Buchungstag;Valutadatum;Buchungstext;Verwendungszweck;Beguenstigter/Zahlungspflichtiger;Kontonummer;BLZ;Betrag;Waehrung;Info
DE97100100000000123456;01.10.2019;03.10.2019;debit note;Birdseed;ACME;DE85200100000000654321;20010000;300,00;EUR;
DE97100100000000123456;02.10.2019;04.10.2019;debit note;Springs;EMCA;DE17300300000000333555;30030000;500,95;EUR;
If youjust want to wath the code working, you can get the ready made sample code from Github here. If you want to build the example (or get a desciption of the code), please see below in Build the solution.
If you want to get the package properly into your workspace using the git integration of designer, please have a look at my article about getting packages from git or github into Service Designer.
If you want to have it quick and easy,
- Copy the directory Demo_File_Converter (located inside ISPackages ) inside C:\Programs\wMServiceDesigner\IntegrationServer\packages (This is just for demo purposes and not a recommended way to deploy packages to a Microservice Runtime or Inetgration Server).
- Go to it's admin page (http://localhost:5555/)
- On the left side expande the Packages section, click Packages-Management
- Click Activate Inactive Packages
- Select Demo_File_Converter.
- Click Activate Packages
In Package Navigator rightclick on the server and select New - Package.
Name the Package Demo_File_Converter.
RIghtclick the new package and select New - Folder.
Name the Folder DemoFileConverter.
Create 4 subfolders namedDocuments, Flatfile, FTP and Services.
Flat file parsing in webMethods is commonly done using Flat File Schemas which describe the structure of a flat file. The schema especially holds details like typ (delimited, fixed length), record and field identifiers and the record structure. A flat file schema may describe fiels consisting of different record types, if we have the means to distinguish the record types. The structure of the records (the fields) can be described in the schema as well but usually is done in a flat file dictionary which is then referenced in the schema.
Rightclick on the folder Flatfile and select New - Flat File Schema.
Name the Schema bankStatement.
Select Create File from Sample and use the Browse button to select the sampel file. The Sample file used here can be found in the Ressources folder within the package e.g. C:\Programs\wMServiceDesigner\IntegrationServer\packages\Demo_File_Converter\resources. Click Nect.
Make sure to set Sample has a record identifier No. Yes will only be used if we have multiple different record types. Click Next.
Select Delimited as Record Parser Typ. Click Next.
In the next dialogie we will select the separators for lines and fields. Record should already be prepolulated with carriage return / linefeed. For Field choose ";". Observe the preview showing the recognized columns. Click Next.
Designer geneartes the schema and a dictionary for the records. Click finish.
Review the Flat File Definition Tab. Record Identifier should be set tto Nth field and Default Record shouldpoint to the generated Dictionary. Default Record will be used for all records which are not identified by the Record Identifier.
Go to the FlatFile Structure tab. We want to exclude the header line from the parsing process. As the header line consists of the column names, we use the first column name as "record identifier". In the previous tab we made sure that the record identifier is the first (0th) field of every record. The record parser matches the content of the identifying field with the name we give the record in the record structure of the schema. Therefore rightclick the recorname bankStatement, select rename and rename it to Auftragskonto, as this is the first column name.
All record with different content in the first column will be parsed as default record.
Click the Create Document Type button in the top pane. Save your work.
The created document type will appear beside the schema and dictionary. It will mirror our document structure with teh header line and several "recordWithNoID" before, after and one level below the header id together with some internal fields.
We already have a document type for holding the data read from the file, but we want to have a dedicated business representation of the data. Therefore we create 2 new document types. One for a single line and a secong one holding a complete list. In this example we will choose the easy path and have the field identical to the file, in real life those structure quite often differ.
Rightclick on the Documents folder and select New - Document Type.
Name the document type bankStatement.
Go to the generated document type in the flatfile folder bankStatementDT (open it by doubleclick if it is not open anymore), select the fieldnames below Auftragskonto including the internal fields @record-id and @segment-id and copy them.
Go to the new document type bankStatement and select insert to paste the fields.
Save your work. Create a second new documenttype and name it bankStatementList. From the palette drag and drop a Document Reference List to the canvas. In the dialog which opens select the bankStatement as document.
Cfter clicking ok the name of the new reference will be directly editable. If not rightclick the reference and select rename. Name it bankStatementList.
Save your work.
This will do the actual parsing and conversion into an internal document type. Rightclick the folder Services and select New - Flow Servic.
Make sure the Input/Ouptut tab is active. Drag a String variable from the palette on the right side to the Inputr pane. If the palette is not visible, click the small triangle in the upper right corner of the output pane. Drag and drop a Document Reference to the output pane. In the dialog opening select the bankStatementList Document we created.
Switch to the tree tab. In the Package Navigator expand WmFlatFile - pub - flatFile. Drag and drop the service convertToValues to the canvas. In the Pipeline tab in the Pipeline In pane there is the variable inData we defined as input for the service. Click and drag this to the input ffData of the service convertToValues. Doubleclick the input variable ffSchema of the service or select it an click the setValue Icon in the upper bar of the Pipeline tab. Insert the qualified name of our flatfileschema: DemoFileConverter.Flatfile:bankStatement.
Drag and drop a Document Reference from the Palette to the Pipeline Out pane. In the dialogue opening select the bankStatementDT document type from the FlatFile folder. Map ffValues from ServiceOut to this document reference. Drop the variables ffData, ffSchema, ffValues and ffIterator from the pipeline by selecting them and clicking the downward Drop the selected Variable button in the upper pane of the pipeline tab.
Drag and drop a map step from the palette into the Tree. In the pipeline pane drag and drop a Document Reference to the Pipeline Out pane. Expand this document and the bankStatementFileLines document in the Pipeline Input pane. Drag the subnode Auftragskonto of bankStatementFileLines to to the subnode bankStatementList of the document bankStatementList. Drop the document bankStatementFileLines from the pipeline.
Add another map step. Expand the bankStatementList. Drop the internal reference fields @record-id and @segment-id as we do not want to have them in the generated xml or json. Save your work.
Create a new service called bankStatementFileToJson in the folder Services.
Create a Sting variable named inData as input and outJson as output.
Switch to the Tree tab. Drag the service DemoFileConverter.Services:parseBankStatementFile to the canvas.
Expand the package wmPublic to pub.json and drag documentToJSONString to the canvas below the last service. Map the serbice output JsonString to outJson. Drop the variables document, bankStatementList and jsonString from the pipeline. Save your work.
To test the service rightclick on DemoFileConverter.Services:bankStatementFileToJson and select Run - Run As Flow Service.
Click the 3 dotes beside the edit field of inData top open the larger editor. Paste the sample content into the editor:
Auftragskonto;Buchungstag;Valutadatum;Buchungstext;Verwendungszweck;Beguenstigter/Zahlungspflichtiger;Kontonummer;BLZ;Betrag;Waehrung;Info
DE97100100000000123456;01.10.2019;03.10.2019;debit note;Birdseed;ACME;DE85200100000000654321;20010000;300,00;EUR;
DE97100100000000123456;02.10.2019;04.10.2019;debit note;Springs;EMCA;DE17300300000000333555;30030000;500,95;EUR;
Click OK. Go to the results tab andclick the field outJson to see its content.
This service is build the same way like bankStatementFileToJson, just name the ouput field outXML and instead of pub.json:documentToJSONString use pub.xml:documentToXMLString from WmPublic.
For calling the service fir ftp we need a ftp port. If we access the Integration Server directly via ftp, the file will not be written to disc butr directly passed to the service. Open the Integration Server Admin page (http://localhost:5555/). Go to Security - Ports.
Click Add Port.
Select webMethods/FTP.
Enter
Port: 8021
Alias: ftp
Package Name: Demo_File_Converter
Click Save Changes.
Per default the Access Mode is set do deny by default, that means, every service called via this port must be explicetly allowed. For this example we will change this to Allow by Default. This is not reccommended for real life scenrios!
Click the link Deny + in the Acces Mode column of the ftp port.
Click Set Access Mode to Allow by Default.
Confirm with OK.
webMethods Integration Server and Micxroservice runtime can use output templates to format output of services in various formats. For historical reasons the templates have either the ending html or xml. *.html is used for html formatting. *.xml can be used for all the other. A template with extension .xml can return formats other than xml and we will use this for our json output.
Go to the template directory within our package directory, e.g. C:\ServiceDesigner\IntegrationServer\packages\Demo_File_Converter\templates. Create 2 files there,
bankStatementFileToJson_ftpOutput.xml with content %value outJson encode(none)%
bankStatementFileToXml_ftpOutput.xml with content %value outXML encode(none)%
This will insert the content of the according output variables into the generated file. As outXML will already be a complete xml string we do not need to add additional tags here.
For accepting input via FTP we create wrapper services which accept the ftpdata, convert the input stream to a string and apply an output template to return the output as a file.
Rightclick the ftp folder and select New - Flow Service.
Name the service bankStatementFileToJson.
Drag an object from the palette to the input pane and name it ffdata. A file sent to the ftp port will be passed o the service as stream named ffdata. Drag a string varianle to the output pane and name outJson.
In the packagenavigator go to WmPublic - pub - io and drag the service streamToString into the canvas. Map ffdata to inputStream. Drop inputStream from the output.
Advanced note: We made the input for our transformer services a string to be as flexible as possible for reusage in other entry methods. For very big files you cann also pass a stream to the convertToValues service and have a node iterator process the file in parts, so you do not need to load the complete file into memory at one. Please look into the Flat_File_Schema_Developers_Guide for more details.
Drag the service DemoFileConverter.Services:bankStatementFileToJson to the canvas below streamToString. Map string, the ouput of the former service, to indata.outData is automaticalyl assigned to outJson of the actualservice bevause of the identical name, but you may still map the 2 together to indicate this mapping is intended.
Go back to the Input/Output tab. In the proprties pane write bankStatementFileToJson_ftpOutput.xml into the field Output Template - Name. Thisis the name of the template which will be used. Although the extension is xml, this can be as well used to generate arbitrry other formats like json.
Build the service the same like FTP wrapper for bankStatementFileToJson , just insert bankStatementFileToXml_ftpOutput.xml as output template name and
insert DemoFileConverter.Services:bankStatementFileToXML instead of DemoFileConverter.Services:bankStatementFileToJson as the second service.
A filepoller can monitor a directory where Integration Server or Microservice Runtime has access to. This directory needs to be accessible locally via standard fileoperations, but this include accessible shares in windows or volumes in a docker environment. For this demo I created directories on the C drive as below:
C:\FilePoller\Json\in
C:\FilePoller\Json\work
C:\FilePoller\Json\done
C:\FilePoller\Json\error
If you do not have write access to C or do want to have his in a different location you may choose one, just set the paths in the below configuration accordingly.
Althoug you can use only one directory for a filepoller it is recommended to use 4, so the flow is as follows:
- File arrives in Monitoring Directory, here C:\FilePoller\Json\in
- File is renamed (with FilePolling and timestamp as prefix) and moved to Working Directory (optional), here C:\FilePoller\Json\work
- After successfull processing the file is moved to Completion Directory (optional), here C:\FilePoller\Json\done
- In case of an error the file is moved to the Error Directory (optional), here C:\FilePoller\Json\error
To keep the example easy we will write the output file to the Working Directory just with the extension of the target format (e.g. .json).
Again we need a wrapper service, as we want to write the ourput data to a file. If not already present create a folder FlatfilePolling. Create a new flow service in this folder and name ot bankStatementFileToJson.
In the Input/Output tab. Drag a variable of type object to the input pane and name it ffdata. Drag 2 string variables into the input pane and call them fileName and originalFileName. Those will be populated at runtime from the filepoller. ffdata will be the input stream of the found file, fileName the working path and name of the file while in the working directory and originalFileName the original path and name before transfered to the working directory.
In the Tree tab drag the service streamToString from pub.io in the Package Navigator to the canvas. Map ffdata to inputStream in ServiceIn.
Drag our service DemoFileConverter.Services:bankStatementFileToJson to the canvas below streamToString. Be sure to choose the service from the folder DemoFileConverter.Services. Map string (the output of the previous step) to inData. Drop inData and string from the pipeline.
Drag pub.file:stringToFile from the package WmPublic into the canvas. Map outJson to data. Use the tSet Value button on fileName to set it to %fileName%.json. make sure to have Perforam pipeline variable substitution checked. This way we will get the output file in the working directory with .json attached to the end. Set append to false. Save your work.
Filepoller are setup as ports, although they do not open a tcp/ip port. On the Microservice Runtime Administrator page go to Security - Ports. Click Add Port.
Select webMethods/FilePolling- Click Submit.
Enter
| Run services as user |
Administrator |
| Alias |
JsonBankStatementFilePoller |
| Monitoring Directory |
C:\FilePoller\Json\in |
| Working Directory (optional) |
C:\FilePoller\Json\work |
| Completion Directory (optional) |
C:\FilePoller\Json\done |
| Error Directory (optional) |
C:\FilePoller\Json\error |
| Enable |
Yes |
| Processing Service |
DemoFileConverter.FlatfilePolling:bankStatementFileToJson |
| File Polling Interval (seconds) |
5 |
| Maximum Number of Invocation Threads |
10 |
If you choose different directories you need t oadjust thoose values. Click Save Changes.
Per default the Access Mode is set do deny by default, that means, every service called via this port must be explicetly allowed. For this example we will change this to Allow by Default. This is not reccommended for real life scenrios! Click the link Deny + in the Acces Mode column of the ftp port.
Click Set Access Mode to Allow by Default.
Confirm with OK.
Check if the port is enabled.
The called service will write the result back to the working directory. For security reasons local writes of Integration Server / Microservice Runtime need to be authorised. This is done in the file file AccessControl.cnf in the directory config of the WmPublic package, e.g. in C:\Programs\ServiceDesigner\IntegrationServer\packages\WmPublic\config.
Add the work directoy to the allowedWritePaths of this file, if you left the paths as in my example, the file should look like
allowedWritePaths=C:\\FilePoller\\Json\\work
allowedReadPaths=
allowedDeletePaths=
Make sure, the FTP port is enabled. The example below it is on port 8021.
An example file can be found in the Ressources dirctory of this package, if you may need to adjust the local path according to the location of your Installation:
cd C:\wMServiceDesigner\IntegrationServer\packages\Demo_File_Converter\resources
ftp
open localhost 8021
cd /ns/DemoFileConverter/ftp/bankStatementFileToJson
put DemoBankStatement01.csv file_01.csv;application:x-wmflatfile
get file_01.csv
cd C:\wMServiceDesigner\IntegrationServer\packages\Demo_File_Converter\resources
ftp
open localhost 8021
cd /ns/DemoFileConverter/ftp/bankStatementFileToXML
put DemoBankStatement01.csv file_02.csv;application:x-wmflatfile
get file_02.csv
Inspect the output files in your local directory.
Copy the sample file DemoBankStatement01 from C:\Programs\ServiceDesigner\IntegrationServer\packages\Demo_File_Converter\resources to C:\FilePoller\Json\in (or use the according directories in your installation).
After the polling interval (5 seconds in the example) the poller will pick up the file and the processing service will write the ouput to the work directory (e.g. C:\FilePoller\Json\work), with the extension .json and the prefix FilePolling and a timestamp.
The processed input file will be moved to the completion directory (e.g. C:\FilePoller\Json\done).
#wiki#flat-file#Integration-Server-and-ESB#Integration-Server#Service-Designer#flat-file-schema#webMethods#microservice-runtime#flat-file-adapter