This page describes the use of the “File Writer” tool in Bizweaver. Information regarding the associated File Operations and File Reader tools is found in separate documents.

The File Writer tool is used to format data from any previous step in a Bizweaver workflow whose output can be processed to write the data in a delimited format, structured format (XML), Excel, or Base64 binary data. The resulting file can then be used as needed in subsequent workflow steps.

There are three output methods available: Datasource, Custom String, and Binary.

The Datasource output option is used to write the input data set as it is received and create the file with data records only. Use the Custom String output method if header and footer sections, or custom column headings, are needed as well. No Bizweaver looping is required.

1. Enter the Filename (target folder and filename) for the output file.
   1. Press <F4> to show variables from previous steps that might be used to create the file name.
   2. The extension (e.g., CSV, txt, etc.) must be included in the file name. The extension should be consistent with the File Type selected step 3 below. The extension is not automatically assigned based on the File Type selection.
2. Select the data Source from the drop-down list.
   1. Only SQL Command and File Reader workflow steps are listed.
3. Select the output File Type from the drop-down list.
   1. See step 1.2 above for additional information.
4. Enter the appropriate column separator character if the Custom Separated File Type option is selected in step 3.
5. Select the Include Column Headers checkbox if column headers should be included in the output file.
   1. The column header names are whatever is presented from the source workflow step.
   2. Column header names cannot be changed when using the Datasource output option.
   3. This option does not apply when XML output is selected.
6. XML output will create a basic XML structure based on the data presented. There is no provision to create hierarchical output or to change the node names.
7. As of version 2.2, Excel must be installed on the Bizweaver server to write the output as an Excel file.

The Custom String output option is used when it is necessary to manipulate the output file format. With the Custom String option, you can:

• Change the order of columns
• Change the column heading names
• Include header and footer records

1. Enter the Filename (target folder and filename) for the output file.
   1. Press <F4> to show variables from previous steps that can be used to create the file name.
   2. The extension (e.g., CSV, txt, etc.) must be included in the file name.
   3. To create tab-delimited output, press Shift + Tab at the point where a delimiter is needed.

The Header section contains whatever values should be included in the output file's first row(s).

1. This section is only written once when the file is created
2. This section is optional but must be included in the output file must include column headers.
3. The appropriate delimiter value must separate column headers.
4. The Body section contains the values for each data record coming from the input source.
   1. When data reaches the end of a line, it will wrap to the next line. This will not add spaces to the file; if line breaks are desired for the Body's data, you must press Enter where the line break is intended.
5. The appropriate delimiter value must separate columns.
6. In most cases, the values in this section will be variables from the input source step. This is shown in the example image above. Press <F4> to list available steps, then click on the step name to see & select the desired values.
7. The Footer section contains whatever values should be included as the last row(s) of the output file.
8. This section is only written once when the file is created
9. This section is optional.
10. The appropriate delimiter value must separate columns, if applicable.
11. An example of this section's use might be a summary record for the file's contents.
12. The output data can be placed into one or more files based on the “Grouped By”, “Each Record,” and “Append” options.
13. To create a single file, select the “Grouped By” option, leave the grouping value field blank, or use the “Append” option. See the notes below about the “Append” option.
14. To create a unique file based on some value in the input data source, select “Grouped By.” Select a variable from the data source step and place it in the grouping value field. Press <F4> while in the field to see a list of data sources. Click on the appropriate data source to select the value.
    1. In this case, a variable from the data source must be used in the output file name to make each file unique. If this is not done, all records will be written into a single file.
15. To create a unique file for each input record, select “Create a file for each record.” A separate file will be created for each record.

The “Append” output option can be used in two ways. First, it can create a single “custom” output file from the input data. Second, it can be used to write the output from multiple input streams into a single output file. When this option is used, the defined output file is not over-written as is done with the Datasource and the other two Custom options.

Care should be taken when using this option. If you want a single output file but don’t want subsequent data to be written into the initial file, there are two options.

1. Add a timestamp value to the file name so that each time the workflow runs, the file name will be unique.
2. Add a “Move” File Operations step to move the file to a different location as part of the workflow process.

The Binary Output option is used to process a Base64 input string and write it out as the appropriate type of file. This process's primary use is to convert images saved via a Versago form (uploads or signatures) and save them as a disk file of the original type.

Data files uploaded to Versago via a Versago Form are stored in the table “TWBS_VGO_FormAttachments,” found in whatever database is used to hold data captured in forms.

The image below illustrates the record structure. The column headings in yellow are used to obtain the needed data.

1. ID is the unique record ID in the table.
2. ObjectID is the ID number of the source form.
3. RecordID is the unique ID number of the record in the source table.
4. FileContent is the binary data that will be used to recreate the file.

To convert these files, we will need workflow steps to obtain each record's ID to be converted. The SQL statement to create the list of records to feed into the loop will be:

Select ID from TWBS_VGO_FormAttachments where ObjectID = 26

This gives us a list of four records (5 – 8)

The selected records are processed through a loop to handle each one individually.

The first step within the loop is to gather the detail for each specific record. The SQL statement to do this is:

SELECT fileName
     ,fileContent
FROM twbs_vgo_formAttachments 
WHERE id = WorkFlow(SQLCommand1).Get({FIELD_ID}) 

The two values are then used in the File Writer step.

• The Filename variable is used to name the file written to disk.
• The FileContent variable indicates what data is to be written out

This process occurs for each record passed into the loop. The recreated files are created in the designated folder using their original name.

In the SAP Business One environment, these files can now be processed further as Attachments to an SAP Business One object such as a Business Partner, Sales Order, etc.

The Binary Output option can also convert a Versago Form signature into a file on disk. The process is like the one described above but requires some additional pre-processing.

Signature data is stored in a column in the table used by the Versago Form. It is stored in a format that must be converted back to base64 before it can be used. There are two ways to approach this.

When you know that a form will be capturing a signature, the conversion can be done in the CustomPostExecute procedure. The framework code shown below can be included in the CustomPostExecute procedure; the framework procedure can be called from the CustomPostExecute procedure. The framework procedure does the conversion and stores the converted data in a new table. Bizweaver can then use this to create an image file for use in reports, etc. This approach's advantage is that the signature images can be created automatically and stored on disk for future use. Following is a framework version of the procedure.

CREATE PROCEDURE [dbo].[SignatureImageConvert] @vgoRecNum INT AS
 
/*
12/AUG/2018 (R Unger / TWBS)
Used to convert a signature image from text to base64 binary for use with Crystal Reports or to save as a disk file.
--
@vgoRecNum and vgoRecNum are the primary keys of the table. Change as needed.
@ObjectID is the form ID.  Change as needed.
F2_SigCapture is the Versago table. Change the name as needed.
*/
 
DECLARE @ConvString nvarchar(MAX), @ObjectID INT
SET @ObjectID = 1
--
-- Parse out image data from text string
SELECT @ConvString = SUBSTRING(Signature,23,999999)
FROM F2_SigCapture WHERE vgoRecNum = @vgoRecNum
--
/* Convert image data to base64 Binary and insert it into a holding table named SignatureCapture.
Assumes a text field named “Name” is used to capture the signature name */
INSERT INTO SignatureConvert
(ObjectID, ControlID, RecordId, FileName, FileType, FileContent, CreateDateTime)
SELECT
@Object,'SigCapture',@vgoRecNum, isnull(Name,'None'), 'image/png',
CAST('' AS xml).value('xs:base64Binary(sql:variable("@convstring"))', 'varbinary(max)'), getdate()
FROM F2_SigCapture WHERE vgoRecNum = @vgoRecNum

The image below illustrates the record structure of the “SignatureCapture” table. The column headings in yellow are used to obtain the needed data.

The conversion to disk file is then the same as described in the Uploaded Versago Data section above.

The signature image can also be converted as needed in a Bizweaver workflow. In this case, a SQLCommand step is used to parse out the signature data using

SUBSTRING(Signature,23,999999)

on the database column that holds the signature data. This step feeds into a FileWriter step using the Binary Output option to convert the base64 data back to an image file, saved to disk for subsequent use.