The File Reader tool is used to read data from “flat” delimited files and “structured” formats such as XML, JSON, Microsoft Excel. Plain text can also be read with “text reader” as a single string.

The File Reader tool is also used to process data being received from a webservice call. More information on this process can be found in the Webservice Tool page.

File delimiters include:

• Comma
• Tab
• Pipe ( | )
• User defined

“Structured” file formats include:

• XML
• JSON
• Microsoft Excel

1. If processing multiple files, consider using the “FileOperations” component operation “Get Files” with a loop component to reduce multiple file readers.
2. The File Reader uses a “template” file to describe the source file structure.
   1. It is recommended that the template file be placed in a separate folder and be named uniquely.
   2. For files with a header row, the template file should have the header row and one data line at minimum.
   3. For files without a header row, the template file needs one data line minimum.

Example showing static file name.

Example showing input from Get File operation

1. Select the delimiter type from the drop-down list.
   
   1. If the “Custom Separated” delimiter type is selected, an additional field is displayed below the Input/File field. Enter the delimiter character here.
2. The FileReader2 tool requires a source file path & name or a Bizweaver variable that identifies the source file (alternatively you can use the output of the previous too - i.e. SQL or webservice).
   1. A specific file is a reasonable option if the source file name is always the same.
   2. If the source file name may not always be the same, then the File Operation tool should be used to feed into a looping cycle where the FileReader tool is a step within the loop.
   3. Wildcard file designations are not permitted for the file reader. Use the File Operations tool to process multiple files.
   4. See Appendix A - File Operations Variables for definitions of the options available using step variables.
3. The File Reader requires the use of a “template” file. This is simply an abbreviated version of the file to be processed (the template file can be the same file that is being processed but it's easier to debug a problem with a static file when and if the file structure changes). The template is used to define the expected columns when reading an input file.
   1. Best practice is to place the template files in their own folder (C:\ProgramData\Bizweaver\Templates), so they are not mixed with files to be processed.
   2. The column structure and sample data from the template file are displayed in the area below the template file name.
4. Select the No header row option if the source files do not have a header row.
   1. Use of a header row is not required. However, all files to be processed through this File Reader step must be consistent; header or no header.
5. Files with and without header rows are supported (this does not apply to XML and JSON files). There are some basic rules about the column headers.
   1. The name cannot have spaces and should typically not have special characters other than an underscore ( _ ).
   2. The name should be relatively short but should be descriptive enough so that its purpose is apparent.

XML (eXtensible Markup Language) and JSON (JavaScript Object Notation) are structured formats commonly used with websites.

The setup for these formats is the same as described for delimited files, with one exception; when either of these formats is the source, the tool has additional functionality to deal with the hierarchy structures often found in these files. The tool “flattens” the data so that it can be used in subsequent processing.

The sample used to explain handling these structures is JSON input returned from a web service call. The data being processed is freight carrier information.

The “Use Null Value” option is used when some records in the input file do not have any data. In these cases, the columns can become “mismatched,” causing problems correctly reading the data. The ”null value” option accounts for these missing values based on the template.

When the input is XML or JSON, a new link named “Select object for formatting the data” is displayed. Click on this link to open a new dialog.

This dialog presents the various data groupings (nodes) in the input structure. Select a combination of checkboxes to “flatten” the data to the selected level.

To get the desired results, you must select all the associated higher-level nodes as well. In the first example, we want just “RateResults”, so the /root level must be selected and/root/RateResults. When this is done, we get the following results:

If we now want to get the “Charges” data, we change the object selector to look like this:

Note that we must include /root and /root/RateResults since “Charges” is a sub-group of /root/RateResults.

With this change, we now see:

The standard functionality of the File Reader tool is to separate different levels of JSON and XML with “_” Underscores. In some cases, especially in JSON files, the “_” is used in a name of a named-value pair. In this case, a different character can be used to separate these levels accurately.

e.g., The below JSON will return two lines due to the “order_number” name-value pair we can correct to display one line as intended.

 {
  "order": [
    {
      "id": 12345565677,
      "email": "Email@twbs.com",
      "closed_at": null,
      "created_at": "2020-11-17T19:10:24-05:00",
      "updated_at": "2020-11-17T19:10:25-05:00",
      "number": 91,
      "order_number": 1091
     }
          ]
} 

As displayed in the File Reader Tool

To Specify a new character enable the option and input the desired character

Refresh the data in the table and now different levels of the JSON file will be separated with “^”

Effective with v91.1.0.161, Bizweaver can process native Microsoft Excel (.xlsx) files. The only limitation is that only the first worksheet in a workbook is processed. The processing of multiple sheets in a workbook is not supported.

The setup for Excel files is the same as described for delimited files.

The Excel file reader requires that the Microsoft Access Database Runtime engine be installed on the Bizweaver server. Contact Third Wave support to obtain the installation package for this application.

The Text Reader file type is used when the input file needs to be read as a single string of text. The uses for this option are very specialized and limited.

The only input for this function is a file name or workflow variable that identifies the file.

There are several variables from the File Operations tool that can be used in other workflow tools. In the following examples, the file being addressed is named “FileName.txt” and it is found in the folder “C:\SourceFolder”.