Using TextStream to generate a text file from Excel Spreadsheet
This article is designed to show you how to write to a file using a Text Stream by pulling the data from an Excel Spreadsheet.
- Your project may require you to write to a file using the Text Stream Module.
- You may need to loop through an Excel Spreadsheet to pull and set data for multiple instances
In addition to the Standard Modules, this solution requires both the Excel Modules and the TextStream modules, which can be downloaded from the Tosca Exchange Portal
1. Using the Excel Modules, Open your Workbook and set the Active Worksheet, the Range, and the Header accordingly using the "Excel Open Workbook", "Excel Set Active Workbook", "Excel Create Range", and "Excel Set Header" modules.
2. Open the TextStream using the "TextStream Open" module.
3. Create a TestStep folder and set the Repetition attribute to the size of your spreadsheet.
Then, within the folder, set a buffer with a value equal to the repetition, so that it increases with each repetition. Use the the "Excel Range Manipulation" module to get the data.
4. Next, you need to create the Module to format the data that you want to store. This must be done manually.
First, Create a new module using the context menu or (Ctrl+N,Ctrl+M).
Next, create an Object Map for the new module from the Context Menu, and create a Param Keyword for this Oject Map called FieldSeparator and set the value to ";" .
Next, create a Module Attribute for each value you wish to store in the text buffer, and for each Attribute, use the context menu to create ControlSimple, or use (Ctrl+N,Ctrl+S).
For this new attribute, set the TypeInfoDescription to "TxtStrEditBox" and use the Context Menu to "Create Param Steering" to create two Parameters named "Format" and "Position" and set the Position to where you want the data to be stored and Format to the type of data being stored (String, Number, etc).
5. Put your new module into the Textcase folder. Set the Values of the TestStep to the buffer's that you set in the Get From Excel TestSteps.
6. After the While Loop, Use the "TestStream Save" Module to save the data to the desired location, and then use the "Excel Close Workbook" Module to close your workbook. The final TestCase should be structured like this.
Running this TestCase will create the text file using the data from the Excel file in the format you specified using the ModuleAttribute Parameters.
Here is the data that I used in the excel Spreadsheet:
And here is the text file that has been written by the TestCase: