Wiki

Scale Your Enterprise

User Tools

Site Tools

Trace: • Introduction (WebService Tool v2) • Introduction (Email) • contact • Workflow Schedules Version 2.4 and prior • downloads • Loop Tool • about • File Writer Tool • Calling Bizweaver from SQL • Building a Workflow
bw2:file_writer_tool

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
bw2:file_writer_tool [2020/12/15 12:51]
akoehler 		bw2:file_writer_tool [2024/09/24 16:22] (current)
dlee [File Writer Variables]
Line 1: Line 1:
-====== Introduction (File Writer)====== 
- 
-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. 
  
 ====== File Writer Tool ====== ====== File Writer Tool ======
Line 20: Line 17:
 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. 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.
  
-{{  :bw:fwimage1.png?384x408  }}+{{ :bw2:bizweaverfilewriter1.png |}}
  
-  - Enter the **Filename** (target folder and file name) for the output file.+  - Enter the **Filename** (target folder and filename) for the output file.
       - Press <F4> to show variables from previous steps that might be used to create the file name.       - Press <F4> to show variables from previous steps that might be used to create the file name.
-      - 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.+      - 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.
   - Select the data **Source** from the drop-down list.   - Select the data **Source** from the drop-down list.
       - Only SQL Command and File Reader workflow steps are listed.       - Only SQL Command and File Reader workflow steps are listed.
Line 34: Line 31:
       - Column header names cannot be changed when using the Datasource output option.       - Column header names cannot be changed when using the Datasource output option.
       - This option does not apply when XML output is selected.       - This option does not apply when XML output is selected.
-  - 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. +  - 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. 
-  - As of version 2.2, Excel must be installed on the Bizweaver server to write output as an Excel file.+  - As of version 2.2, Excel must be installed on the Bizweaver server to write the output as an Excel file.
  
 ====== Custom String Output ====== ====== Custom String Output ======
Line 50: Line 47:
 </WRAP> </WRAP>
  
-{{  :bw:fwimage2.png?384x408  }} +{{ :bw2:bizweaverfilewriter2.png |}} 
- +  - Enter the **Filename** (target folder and filename) for the output file.
-  - Enter the **Filename** (target folder and file name) for the output file.+
       - Press <F4> to show variables from previous steps that can be used to create the file name.       - Press <F4> to show variables from previous steps that can be used to create the file name.
-      - The extension (e.g. csv, txt, etc.) must be included in the file name.+      - The extension (e.g., CSV, txt, etc.) must be included in the file name.
       - //**To create tab-delimited output, press Shift + Tab at the point where a delimiter is needed.**//       - //**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 first row(s) of the output file.+  The **Header** section contains whatever values should be included in the output file's first row(s).
       - This section is only written once when the file is created       - This section is only written once when the file is created
-      - This section is optional but must be included if the output file must include column headers.+      - This section is optional but must be included in the output file must include column headers.
       - The appropriate delimiter value must separate column headers.       - The appropriate delimiter value must separate column headers.
   - The **Body** section contains the values for each data record coming from the input source.   - The **Body** section contains the values for each data record coming from the input source.
 +        - 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.
       - The appropriate delimiter value must separate columns.       - The appropriate delimiter value must separate columns.
-      - 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.+      - 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.
   - The **Footer** section contains whatever values should be included as the last row(s) of the output file.   - The **Footer** section contains whatever values should be included as the last row(s) of the output file.
       - This section is only written once when the file is created       - This section is only written once when the file is created
       - This section is optional.       - This section is optional.
-      - The appropriate delimiter value must separate columns, if appropriate. +      - The appropriate delimiter value must separate columns, if applicable. 
-      - An example of the use of this section might be a summary record for the contents of the file. +      - An example of this section's use might be a summary record for the file's contents. 
-  - The output data can be placed into one, or more, files based on the “Grouped By”, “Each Record,” and “Append” options. +  - The output data can be placed into one or more files based on the “Grouped By”, “Each Record,” and “Append” options. 
-      - To create a single file, select the “Grouped By” option and leave the grouping value field blank, or use the “Append” option. See the notes below about the “Append” option.+      - 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.
       - 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.       - 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.
           - 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.           - 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.
-      - To create a unique file for each input record, select “Create file for each record.” A separate file will be created for each record.+      - 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.
 <WRAP center round info> <WRAP center round info>
-When creating multiple files, a variable from the data source must be used in the output file name to make each file unique. If this is not done the specified file will be over-written each time a new “grouping” is found, and the file will only reflect the last record(s) processed. This applies for both the “Grouped By” and “Each Record” options.+When creating multiple files, a variable from the data source must be used in the output file name to make each file unique. If this is not done, the specified file will be over-written each time a new “grouping” is found, and the file will only reflect the last record(s) processed. This applies to both the “Grouped By” and “Each Record” options.
 </WRAP> </WRAP>
  
 ===== Append Option for Custom Output ===== ===== Append Option for Custom Output =====
  
-The “Append” output option can be used in two ways. First, it can be used to create a single “custom” output file from the input data. Second, it can be used to write the output from multiple input steams 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.+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 do want a single output file, but you don’t want subsequent data to be written into the initial file, there are two 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.
  
   - Add a timestamp value to the file name so that each time the workflow runs, the file name will be unique.   - Add a timestamp value to the file name so that each time the workflow runs, the file name will be unique.
Line 88: Line 85:
 ====== Binary Output ====== ====== Binary Output ======
  
-The Binary Output option is used to process a Base64 input string and write it out as the appropriate type of file. The primary use of this process is to convert images saved via a Versago form (uploads or signatures) and save them as a disk file of the original type.+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.
  
 ===== Uploaded Versago Data ===== ===== Uploaded Versago Data =====
  
-Data files that are uploaded to Versago via a Versago Form are stored in the table “TWBS_VGO_FormAttachments,” which is found in whatever database is used to hold data captured in forms.+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. The image below illustrates the record structure. The column headings in yellow are used to obtain the needed data.
Line 103: Line 100:
   - **FileContent** is the binary data that will be used to recreate the file.   - **FileContent** is the binary data that will be used to recreate the file.
  
-To convert these files, we will need workflow steps to obtain the ID of each record to be converted. The SQL statement to create the list of records to feed into the loop will be:+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 > Select ID from TWBS_VGO_FormAttachments where ObjectID = 26
Line 113: Line 110:
 The first step within the loop is to gather the detail for each specific record. The SQL statement to do this is: The first step within the loop is to gather the detail for each specific record. The SQL statement to do this is:
  
-<code sql> Select FileName, FileContent from TWBS_VGO_FormAttachments where [ID] = +<code sql>SELECT fileName 
- +     ,fileContent 
-WorkFlow(SQLCommand1).Get({FIELD_ID}) </code>+FROM twbs_vgo_formAttachments  
 +WHERE id = WorkFlow(SQLCommand1).Get({FIELD_ID}) </code>
  
 The two values are then used in the File Writer step. The two values are then used in the File Writer step.
Line 122: Line 120:
   * The FileContent variable indicates what data is to be written out   * The FileContent variable indicates what data is to be written out
  
-{{  :bw:fwimage4.png?384x305  }}+{{ :bw2:bizweaverfilewriter3.png |}}
  
 This process occurs for each record passed into the loop. The recreated files are created in the designated folder using their original name. 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.+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.
  
 ===== Versago Signature Data ===== ===== Versago Signature Data =====
  
-The Binary Output option can also be used to convert a signature captured in a Versago Form into a file on disk. The process is like the one described above but requires some additional pre-processing.+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. 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.
  
 ====Option 1==== ====Option 1====
-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. This can then be used by Bizweaver to create an image file for use in reports, etc. The advantage of this approach is that the signature images can be created automatically and stored on disk for future use. Following is a framework version of the procedure.+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.
  
 <code sql> <code sql>
Line 144: Line 142:
 Used to convert a signature image from text to base64 binary for use with Crystal Reports or to save as a disk file. 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 key of the table. Change as needed.+@vgoRecNum and vgoRecNum are the primary keys of the table. Change as needed.
 @ObjectID is the form ID.  Change as needed. @ObjectID is the form ID.  Change as needed.
-F2_SigCapture is the Versago table. Change name as needed.+F2_SigCapture is the Versago table. Change the name as needed.
 */ */
  
Line 156: Line 154:
 from F2_SigCapture where vgoRecNum = @vgoRecNum from F2_SigCapture where vgoRecNum = @vgoRecNum
 -- --
-/* Convert image data to base64 Binary and insert into holding table named SignatureCapture.+/* 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 */ Assumes a text field named “Name” is used to capture the signature name */
-insert into SignatureConvert+INSERT INTO SignatureConvert
 (ObjectID, ControlID, RecordId, FileName, FileType, FileContent, CreateDateTime) (ObjectID, ControlID, RecordId, FileName, FileType, FileContent, CreateDateTime)
-select +SELECT 
-@Object,'SigCapture',@vgoRecNum, isnull(Name,'None'), 'image/png', +     @Object 
-cast('' as xml).value('xs:base64Binary(sql:variable("@convstring"))', 'varbinary(max)'), getdate() +     ,'SigCapture' 
-from F2_SigCapture where vgoRecNum = @vgoRecNum+     ,@vgoRecNum 
 +     ,ISNULL(Name,'None') 
 +     ,'image/png' 
 +     ,CAST('' AS xml).value('xs:base64Binary(sql:variable("@convstring"))' 
 +     ,'varbinary(max)') 
 +     ,GETDATE() 
 +FROM F2_SigCapture 
 +WHERE vgoRecNum = @vgoRecNum
 </code> </code>
  
Line 170: Line 175:
 {{  :bw:fwimage5.png  }} {{  :bw:fwimage5.png  }}
  
-The conversion to disk file is then the same as described in Uploaded Versago Data section above.+The conversion to disk file is then the same as described in the Uploaded Versago Data section above.
  
 ====Option 2==== ====Option 2====
-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 <code sql>substring(Signature,23,999999)</code> 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, which is saved to disk for subsequent use.+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 <code sql>substring(Signature,23,999999)</code> 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. 
 + 
 + 
 +====File Writer Variables==== 
 +^Name         ^ Description          
 +| AppendData         | True or False if the append data radio button was selected        | 
 +| BinaryData|      The value that was entered in the binary data section if any              | 
 +|ColumnID| If create a file for records - grouped by is selected it will display the current record that is being grouped| 
 +| CreateFileForAllRecords          | True or False if the create file for all records radio button was selected| 
 +| CreateFileForGroupedRecords |True or False if the create a file for grouped records radio button was selected| 
 +| CustomSeparator         |Will display the customer separator if any is selected| 
 +| DataSourceWorkFlowID          |The source of the data that is being used in the file writer tool| 
 +| EnableBinaryData |True or False if the binary data radio button is selected| 
 +| EnableColumnHeaders          |True or False if the enable column headers radio button is selected| 
 +|EnableDataSource          |True or False if there is a data source used| 
 +| EnableStringBuilder |True or False if custom string radio button is selected| 
 +| FileName          |The file location where the file that is being created is being saved with extension| 
 +| FileType          |The file type that is selected ex. JSON,XML,Comma Seperated| 
 +|FormattedStringBody |The value that was entered in the body section of the custom string if any| 
 +| FormattedStringFooter         |The value that was entered in the footer section of the custom string if any| 
 +| FormattedStringHeader         |The value that was entered in the header section of the custom string if any| 
 +| StepMessage|During a failure the reason for failure will be populated here | 
 +| StepStatus        |True or False| 
 + 
 +---- 
 +**[[bizweaver:Home|Home]]**
bw2/file_writer_tool.1608054696.txt.gz · Last modified: 2020/12/15 12:51 by akoehler

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial 4.0 International
CC Attribution-Noncommercial 4.0 International Valid HTML5 Valid CSS Driven by DokuWiki