Tips
The best thing you can do is to record a macro when you import one txt file manual.
Then look at the recorded code and add the code lines to Workbooks.OpenText .
If you want to format or skip a columns then you see you can add FieldInfo in OpenText
FieldInfo:=Array(Array(1, 2), Array(3, 4))
This example change the format of column 1 and 3 (column number, format number)
This are the format numbers
xlGeneralFormat General 1
xlTextFormat Text 2
xlMDYFormat Month-Day-Year 3
xlDMYFormat Day-Month-Year 4
xlYMDFormat Year-Month-Day 5
xlMYDFormat Month-Year-Day 6
xlDYMFormat Day-Year-Month 7
xlYDMFormat Year-Day-Month 8
xlSkipColumn Skip 9
Merge txt files
Replace
Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
With
Print #1, "Copy " & Chr(34) & foldername & "*.txt" _
If you use it for txt files then you can change the delimiter or maybe you want to use FixedWidth.
The best thing you can do is to record a macro when you import one txt file manual.
Then look at the recorded code and add the code lines to Workbooks.OpenText .
More information
Copy every TXT or CSV file that you select in a new worksheet of a newly created workbook
http://www.rondebruin.nl/txtcsv.htm
For more information about importing txt files visit Chip Pearson's site.
http://www.cpearson.com/excel/imptext.htm
Saving XL files as Text/CSV (J.E McGimpsey)
http://www.mcgimpsey.com/excel/textfiles.html
Manual Correlation
Objectives
After completing this you should be able to:
• Determine when manual correlation is required.
• Correlate dynamic values using the create parameter option.
Why Perform Manual Correlation?
Sometimes "Scan for Correlation" fails to find values that must be correlated for proper playback.
Example:
The server returns to the browser a time stamp, formatted as 12:13:14. JScript
in the browser reformats and sends it to the server on the next request as 121314.
Scan for correlation will never find this. The string from the server must exactly match the string to the server.
In such a case, manual correlation is the only solution.
Manual Correlation - Procedure
To perform manual correlation, there are six steps. Each step will be discussed in detail.
1. Play back the script with the ‘Data returned by server’ setting and determine
whether the error is due to correlation.
2. Identify the first step that gets the dynamic value.
3. Determine the value to correlate.
4. Create a parameter for the dynamic value by adding a web_reg_save_param
function to the script.
5. Parameterize the dynamic value in the script every time it occurs.
6. Verify correct execution.
Set Run-time Settings
Figure 11-1 Enable Data Returned by Server in RTS
Correlation issues are best understood by choosing ‘Data returned by server’ within RUN-TIME SETTINGS and playing back the script. This allows you to see the dynamic value in the REPLAY LOG sent out by the server. Viewing the source code in the REPLAY LOG also helps in drilling down to the issue.
If it was not selected before playback, select the script and play back again.
Examine Replay Log
Figure 11-2 Investigate the Replay Log
Play back the script at least once. If there is an error, troubleshoot to confirm that the error is not due to buggy code or another application error. Investigate the REPLAY LOG.
Make a note of the step name that failed.
In the example above, the checkpoint failed, indicating that the text we were looking for could NOT be found on the replay. The next step is to investigate the TEST RESULTS.
1.b Test Results Reveals the Problem
Figure 11-3 Test Result Reveals the Problem
In the example above, the TEST RESULTS reveal that the Checkpoint failed the page was reached incorrectly (probably a bad user session value).
This indicates that the recorded script contains hard-coded data that is dynamic. The solution will be to capture the server-generated number every time the script is run and input it where needed. Correlating the script will allow us to accomplish this.
Determining the Step
Figure 11-4 First Step that Retrieves the Server-Generated Value
Once it is established that an error requires correlation, the next step is to find the step that calls for the server-generated value.
1. Switch to TREE VIEW if in WORKFLOW or SCRIPT VIEW.
2. Double-click on the failed step in the REPLAY LOG. This will take you to the step that has failed in TREE VIEW.
3. Make sure you can see the snapshots, by enabling Snapshot view with VIEW ?
Snapshot ? VIEW SNAPSHOT. Snapshot will appear in the right pane.
4. Click on the VIEW REPLAY SNAPSHOT button , and click the SERVER RESPONSE
tab. Click on the BODY link.
5. The current step shows the snapshot of the login.pl page. Is this the page that gets the session value? Review the message in the snapshot view. The message indicates that you reached the page incorrectly (probably a bad user session value). Therefore, this indicates this is not the step that first retrieves the server-generated number.
6. The previous step - Url: WebTours, is the page that is requesting the session value.
The dynamic value returned by the server should be captured here in a parameter in order to supply the dynamic value to the failed step, “login.pl.“
Determining the Value to Correlate
Figure 11-5 Value to Correlate
1. Display both the Recording and Replay Snapshots for step that first gets the servergenerated data. This allows you to see the different between the record and
playback values.
2. Review the snapshots. In our example, the only difference is the userSession value.
Copying the Dynamic Value
Figure 11-6 Switch to Recording Snapshot
1. Make sure that you are on the step that first gets the server-generated data.
In our example, Url: WebTours step.
2. Switch to SCRIPT VIEW.
3. Locate and copy the dynamic value.
Searching For the Dynamic Value
Figure 11-7 Search for the Dynamic Value
1. Return to TREE VIEW.
2. Select the step where the dynamic value is called from.
3. Switch to the SERVER RESPONSE tab.
4. Click BODY under the SERVER RESPONSE tab.
5. Expand the steps inside of the BODY.
6. Select the step that contains the dynamic value.
7. Press CTRL + F and paste the value into the FIND WHAT box.
8. Click FIND.
9. Keep searching by pressing F3 until you find the number.
Creating a Parameter
Figure 11-8 Create a Parameter
1. Select the entire dynamic value to be correlated.
2. Right-click on the number, then select CREATE PARAMETER from the pop-up
menu.
Parameterize Each Occurrence of Dynamic Value
Figure 11-9 Parameter the Dynamic Value in the Script
1. Click YES on the confirmation message to replace the 1576849332
the hard-coded value with a parameter.
Parameterized Dynamic Value - Script View
Figure 11-10 Parameter Added in Script View
SCRIPT VIEW shows more detail compared to the TREE VIEW. The function
web_reg_save_param adds the parameter to the failed step Submit Data: login.pl.
The parameter “WSCParam_Text1” will capture the dynamic value from the WebTours
step and supply the dynamic value to the failed step.
How does the web_reg_save_param work? When you run the script, the
web_reg_save_param function scans the subsequent HTML page that is accessed. It
searches for an occurrence of the left boundary followed by any string and then followed by the right boundary. When such an occurrence is found, VuGen assigns the string between the left and right boundaries to the parameter named in the function's argument.
After finding the specified number of occurrences, web_reg_save_param does not
search any more HTML pages and continues with the next step in the script.
Reviewing the web_reg_save_param Function
Figure 11-11 Function Arguments
This is the function that is used for correlation (to capture system-generated data and supply it as input data to the required step).
The web_reg_save_param looks for the characters between (but not including) the
specified boundaries and saves the information beginning one byte after the left
boundary and ending one byte before the right boundary.
Where Was the web_reg_save_param Added?
Figure 11-12 Parameter Added in Script View
The web_reg_save_param function is added before the step that first gets the dynamic value.
web_reg_save_param is a registration type function. It registers a request to find and save a text string in a parameter within the web page that was retrieved.
The operation is performed only after executing the next action function. This parameter then supplies the dynamic value to the required step.
Note: When a parameter has been inserted in this way, it appears in green, just like the surrounding text, rather than in purple, as is found when inserted using VuGen’s Insert Parameter feature.
Verifying Correct Execution
Figure 11-13 Replay Log
To verify correct execution, ensure that EXTENDED LOG, PARAMETER SUBSTITUTION
and DATA RETURNED BY SERVER are checked in the RUN-TIME SETTINGS.
During playback, when VuGen executes the web_reg_save_param
statement, three things occur (using the example in the above figure):
1. VuGen creates the parameter "WSCParam_Text1" specified in the first
argument (1).
2. When the next step is executed, VuGen searches the HTML source of that page for the text specified by the left and right boundary arguments in the statement.
3. When the value is found, VuGen places it into the defined parameter
{WSCParam_Text1}.
Thereafter, whenever VuGen encounters a reference to {WSCParam_Text1} in the
script, it will plug in the value stored in the parameter.
Summary
how to:
• Determine when manual correlation is required.
• Correlate dynamic values using the create parameter option.
Auto Correlation During Recording
Objectives
After completing this you should be able to:
• Create correlation rules to auto correlate during recording.
• Import and export correlation rules.
When to Correlate During Recording
Figure 12-1 Existing Correlation Rules
Correlate during recording when:
• The web application server is on the RECORDING OPTIONS list of known server
types (to handle session data).
• You can determine rules for correlation because context is known in advance.
About Automatic Correlation During Recording
• VuGen can correlate dynamic data based on predefined rules in the Recording
Options.
• Define correlation options before you record a script.
• If server-generated data gets re-used by the client, VuGen automatically correlates it (or prompts the user to choose between correlating while recording or later).
Determining Which Correlation Method to Use
• After every recording, you find the script requires correlation. You may continue to correlate using the after recording correlation method.
or
• You may create a custom correlation rule to correlate during recording if the
context is known in advance. This will be a one-time task. After setup, all future recordings will add the correlation in the script during recording.
Note: Correlating during or after recording results in the same script. It is just a matter of which method you prefer.
Correlating During Recording – Procedure
To define a new rule and set the recording options to correlate during recording:
1. ENABLE CORRELATION DURING RECORDING in Recording Options.
2. Create a new application.
3. Create a new rule for the newly created application. Define properties for the new rule.
4. Record the script.
5. Play back the script to verify correct execution.
To instruct VuGen to correlate your statements during recording, use one of the
following methods:
1. Built-in Correlation
2. User-Defined Rule Correlation (the focus of this lesson)
The above steps are for creating a user-defined correlation rule. Define the rule before you begin the recording session.
If the application has unique rules and you are able to determine them, you can define correlation rules using the Recording Options. User-defined rules must be created before you record a session. Create the rules in the Recording Options dialog box. The rules include information, such as the boundaries of the dynamic data you want to correlate and specifications about the match, such as binary, case matching, and the instance number.
Another time to define an auto-correlation rule is when the same correlated values are seen multiple times in the script or across different recordings. Once the rules are determined through auto-correlation after recording or manual correlation, they can be added to the correlation rules. This avoids having to manually correlate or scan for correlations after each recording.
Enabling Correlation During Recording
Figure 12-2 Enable Correlation Rules
Before recording the script, perform the following steps:
1. Open the RECORDING OPTIONS dialog by selecting TOOLS ? RECORDING
OPTIONS
2. Select the CORRELATION node.
3. Check ENABLE CORRELATION DURING RECORDING. If the application server
exists in the list, indicate the servers to which the correlation rules apply.
Select the checkboxes next to the server names to enable rules for that server. If the server name is not in the list, continue with the steps to create correlation rules for your application.
Creating a New Application
Figure 12-3 Create New Application WebTours
1. Select an application from the list or click NEW APPLICATION and enter a
meaningful name.
Creating a New Rule
Figure 12-4 Create New Rule
2. Click the NEW RULE button. A new rule is added under the newly created
application.
Defining Properties for the New Rule
Figure 12-5 Define Properties for the New Rule
In the Actions option, instruct VuGen where to search for the criteria. Select an ACTION from the list.
Parameter Prefix: Uses a prefix in all automatically generated parameters based on this rule. Prefixes prevent you from overwriting existing user parameters. In addition, prefixes allow you to recognize the parameter in your script more easily.
For example, in Siebel-Web, one of the built-in rules searches for Siebel_row_id prefix.
Match Case: Matches the case when looking for boundaries.
Use "#" for any digit: Replaces all digits with a hash sign. This will allow you to find text strings that match everything except for numerical digits. For example, if the left boundary is HP193, it will be matched with HP284. In the left boundary box, specify HP###.
Regenerating the Script
Figure 12-6 Regenerate the Script
If you have already recorded the script, then created the rules, simply regenerate the script to apply the newly created correlation rule to the script.
Note: This will overwrite ANY custom changes that have been made. It is
recommended to save the script as a different name before regenerating the script.
1. Select TOOLS ? REGENERATE SCRIPT and click OK on the pop-up message.
2. The rule will automatically add the correlation to the script.
Recording a New Script
Figure 12-7 Correlation Rule Auto Applies to the New Script
After creating the rule when you record a new script, the rule is automatically applied and correlates the script.
Note: Make sure to check ENABLE CORRELATION DURING RECORDING in the
RECORDING OPTIONS.
Creating Rules after Correlation Scan
Figure 12-8 Another Approach to Creating Correlation Rules
The second method of creating a rule allows you to create a rule from one of the detected correlation results. You can create a rule directly from the CORRELATED RESULTS tab.
1. Play back the script at least once.
2. Once the script fails, determine if the error needs to be correlated.
3. Scan the script for dynamic values.
4. Select the correlation result and click CREATE RULE. This is also available from the right-click menu. VuGen displays the properties based on which rule will be created. VuGen adds this rule to the list of Correlation Studio rules. You can view this rule in the RECORDING OPTIONS - CORRELATION node.
Adding the rule will not correlate the current script. You will still have to correlate the script by clicking on the correlate button. The rule will be applied the next time you record a script.
Exporting and Importing Rules
Figure 12-9 Export Then Import Correlation Rules
Once custom rules are created, they can be exported from the machine where the rules were created and imported onto the machine that needs the custom rules.
As a best practice, make sure that one person creates the custom correlation rule and that other script writers import the rule to their machine.
Exporting Rules
Figure 12-10 Select the Rule to Export
To export a set of correlation rules, or just one rule, do the following:
1. Select TOOLS ? RECORDING OPTIONS and select the INTERNET PROTOCOL:
CORRELATION node in the Recording Options tree.
2. Select the ENABLE CORRELATION DURING RECORDING option.
3. Click the EXPORT button. The CHOOSE APPLICATION TO EXPORT window opens.
4. Select the rules to export.
5. Click EXPORT on the CHOOSE APPLICATION TO EXPORT window.
6. Choose a desired location and file name. The file will be saved with the .cor
extension.
Importing Rules
Figure 12-11 Import Rule
To import a set of correlation rules:
1. Click the IMPORT button. The IMPORT CORRELATION SETTINGS FROM A FILE
window opens.
2. Select the file and click on the OPEN button. This will import the custom correlation rules to the new machine.
Summary
how to:
• Create correlation rules to auto correlate during recording.
• Import and export correlation rules.
Advanced Manual Correlation
Objectives
After completing this you should be able to:
• Correlate a script by manually using WDiff.
• Manually insert the web_reg_save_param correlation function.
• Parameterize the dynamic value in a script.
Manual Correlation – Procedure
To perform manual correlation, there are six steps. Each step will be discussed in detail.
1. Play back the script with ‘Data returned by server’ and determine if error is due to correlation.
2. Determine which dynamic values to correlate using WDiff.
3. Find the left boundary, right boundary and occurrence of the dynamic value.
4. Add web_reg_save_param function to the script.
5. Parameterize the dynamic value in the script every time it occurs.
6. Verify correct execution.
Playing Back the Script with ‘Data returned by server’
Figure A-12 Enable Data Returned by Server in RTS
Correlation issues are best understood by choosing ‘Data returned by server’ within Run-time Settings and playing back the script. This allows you to see the dynamic value in the REPLAY LOG sent out by the server. Viewing the source code in the REPLAY LOG also helps in drilling down to the issue.
If the ‘Data returned by server’ was not selected before playback, select the option and replay the script again.
Investigating Errors in the Replay Log
Figure A-13 Investigate the Error
Play back the script at least once with ‘Data returned by server’ RUN-TIME SETTINGS. If there is an error, troubleshoot to confirm that the error is not due to buggy code or another application error. Investigate the REPLAY LOG - make a note of the step name that failed.
In the example, the checkpoint failed, indicating that the text you were looking for could NOT be found on the replay. So the next step is to investigate the TEST RESULTS.
Investigating the Test Results
Figure A-14 Test Results Reveal the Problem
In the example above, the TEST RESULTS reveal the Checkpoint failed because the page was reached incorrectly (probably a bad user session value).
This indicates that the recorded script contains hard-coded data that is dynamic. The solution will be to capture the server-generated number every time the script is run and input it where needed. You can accomplish this by correlating the script.
Comparing two Scripts Using WDiff
Figure A-15 Wdiff Highlights the Differences
Once it is established that the error requires correlation, the next step is to find the dynamic values to be correlated.
This lesson will show you how to use the WDiff utility to find the values. To determine which values are dynamic in the application but hard-coded in the script, complete the following steps:
1. Create two scripts with the same user steps. The idea is to compare the contents of two nearly identical scripts to see which recorded values are different.
2. Select TOOLS ? COMPARE WITH VUSER. This opens the OPEN VUSER USER
dialog. Select the twin script from the dialog. VuGen compares the script currently visible with the script you selected. All the differences are highlighted in yellow.
Identifying Values for Correlation
Figure A-16 Values to Correlate
Wdiff highlighted differences between the two scripts.
If you see many lines highlighted in yellow, correlate only the values to be reused. At this point, help from developer, DBA, or functional expert may be useful to identify which values are reused by the business process.
Copying Dynamic Value
Figure A-17 Copy the Value into Notepad
Copy the dynamic value from WDiff original script to Notepad. This value can then be used to search in the GENERATION LOG to find the left and right boundaries.
Crtl+C does not work in WDiff, so highlight the line you want copied, then select
EDIT ? COPY. Paste the line in Notepad and make sure to copy only the dynamic value from Notepad.
In this example, the dynamic value is 96447.9789388416tQzVAtcpQVzzzzzHcQHzzpcfHf.
Identifying Left and Right Boundaries
Figure A-18 Search for the Dynamic String in Generation Log
1. Click the GENERATION LOG.
2. Open the FIND dialog (Ctrl+F).
3. Paste the copied value 96447.9789388416tQzVAtcpQVzzzzzHcQHzzpcfHf into
the FIND WHAT box.
4. Click the FIND button.
Once the value is found, note the right and left boundaries. Include the use space as part of the boundaries, if necessary.
Try to keep one of the boundaries long enough to be uniquely identified. The longer one of the boundaries, the less likely that you will have to use the ORD (occurrence) argument.
Note: In SCRIPT VIEW, certain characters (such as the double quotation marks) have special functions. To include such a character as part of boundary text, precede that character with a backslash. In script functions, the backslash indicates that the character which follows is meant to be processed as text.
Determining Location of Correlation Function
Figure A-19 Determine the Step
1. Double-click on the error in the REPLAY LOG. This will take you to the failed step.
2. The snapshot for the failed step: Submit Data: login.pl. This shows the action
that happened after the value was entered. This proves it is not the step that needs correlation.
3. Click on the previous step: Url: WebTours. This step retrieves the dynamic value.
This is the step that needs to be correlated so that the dynamic value returned by the server can be captured in a parameter and supply the dynamic value to the failed step.
Adding the web_reg_save_param Function
Figure A-20 Parameter Added in Script View
To add the function to the script:
1. First, position the cursor above the step that retrieves the dynamic data. In our
example, Url: WebTours.
2. Select INSERT ? NEW STEP. Enter web_reg_save_param in the FIND FUNCTION
field. Complete the PARAMETER NAME, LEFT BOUNDARY, and RIGHT BOUNDARY
fields. Click the OK button.
Note: Provide a meaningful name to the PARAMETER NAME value.
Parameterizing the Dynamic Value in the Script
Figure A-21 Parameter Added
Replace the hard-coded value of the parameter name you created in the
web_reg_save_param.
web_reg_save_param is a registration function. It registers a request to find and save a text string in a parameter within the web page that retrieves the value.
The operation is performed only after executing the next action function. This parameter then supplies the dynamic value to the required step.
Note: When a parameter has been inserted in this way, it appears in green - just like the surrounding text - rather than in purple as used when inserted via VuGen’s Insert Parameter feature.
Verifying Correct Execution
Figure A-22 Replay Log
To verify correct execution, ensure that EXTENDED LOG, PARAMETER SUBSTITUTION,
and DATA RETURNED BY SERVER are checked in the RUN-TIME SETTINGS.
During playback, when VuGen executes the web_reg_save_param
statement, three things occur (using the example in the above figure):
1. VuGen creates the parameter "SessionValue" specified in the first argument of the
web_reg_save_param function.
2. When the next step is executed, VuGen searches the HTML source of that page for
the text specified by the left and right boundary arguments in the statement.
3. When the value is found, VuGen places it into the defined parameter
{SessionValue}.
Thereafter, whenever VuGen encounters a reference to {SessionValue} in the script, it
will replace the value with the parameter’s value.
Summary
How to:
• Correlate a script by manually using WDiff.
• Manually insert the web_reg_save_param correlation function.
• Parameterize the dynamic value in a script.
No comments:
Post a Comment