RSS
 

Posts Tagged ‘import data’

#17: Reformat data in Excel (sheet A to Array x to Array y to sheet B)

25 Jul



This post is an example for you to practice post#9 and post#16. The 2 images on the right hand side are illustrations to show what I am tyring to do in this post:

In the excel file “MeasurementTab”, I created 2 sheets, one is called “Raw” and the other is called “ModeCategoryUnit”. I copied all the protocol tables from our Software Specifications, and paste them one by one to the “Raw” sheet. There are less than 50 Protocols, so it is okay to copy them manually. However, each Protocol table contains 2 to 20 measurements, so there are about 500 measurements in-total, and I want to rearrange all these measurements in another format/order and save them in the “ModeCategoryUnit” sheet, which will take days if I do it manually.

I decide to automate the data process. I will export the data from “Raw” sheet to an array called Raw(). By using the array Raw() and another small array ModeArray(1 To 9), I will rearrange the data in a new order, and save the data to an array called ModeCategoryUnit(). Finally, data in array ModeCategoryUnit() will be exported to sheet “ModeCategoryUnit” in the Excel file.

'Convert "Raw" sheet (populated directed from software specification - word version) to "ModeCategoryUnit" sheet in "Measurement.xlsx"
'If handle all the Packages/Protocols/Measurments, then need to increase ModeCategoryUnit's size and size parameters in Function_SaveArrayAsExcelSheet

'$TPinclude "Declaration_GlobalConstants"

Sub Main()

'Store Data from the "Raw" Sheet to array Raw()
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim Raw() As String

RowCount = Function_ExcelSheetRowCount.ExcelSheetRowCount(ExcelFilePath_Measurement, "Raw")
ColumnCount = Function_ExcelSheetColumnCount.ExcelSheetColumnCount(ExcelFilePath_Measurement, "Raw")

ReDim Raw(1 To RowCount, 1 To ColumnCount) As String
Raw() = Function_GetExcelSheetData.GetExcelSheetData(ExcelFilePath_Measurement, "Raw")

'Delete extra space (if any) for each cell of array Raw()
Dim TrimRaw() As String
ReDim TrimRaw(1 To RowCount, 1 To ColumnCount) As String
Dim i As Integer    'row number of array Raw()
Dim j As Integer    'column number of array Raw()

For j = 1 To ColumnCount
    For i = 1 To RowCount
    TrimRaw(i, j) = Trim(Raw(i, j))
    Next
Next

'Convert data from array TrimRaw() to array ModeCategoryUnit()
Dim ModeCategoryUnit(1 To 15, 1 To 60) As String    'Size should be increased if handle all the Packages/Protocols/Measurments

'First, write the header section (top 3 rows) of array ModeCategoryUnit()
'Including Package (1st row), Protocol (2nd row) and Mode (3rd row)
Dim Package As String
Dim Protocol As String
Dim ModeArray(1 To 9) As String
Dim ModeList As String
Dim Counter As Integer
Dim Column As Integer

Column = 1
For j = 1 To ColumnCount
    If TrimRaw(1, j) <> "" Then
        Package = TrimRaw(1, j)
        If TrimRaw(2, j) <> "" Then
            Protocol = TrimRaw(2, j)
            'Reset Counter, Clearn ModeList and ModeArray for the FOR loop
            Counter = 1
            ModeList = ""
            Erase ModeArray
            For i = 3 To RowCount
                If TrimRaw(i, j + 3) <> "" Then
                    'If TrimRaw(i, j + 3) doesn't exist in ModeList
                    If InStr(ModeList, TrimRaw(i, j + 3)) = 0 Then
                        ModeArray(Counter) = TrimRaw(i, j + 3)
                        'ModeList looks like "ModeArray(Counter),ModeArray(Counter+1),,,,,,,,"
                        ModeList = Join(ModeArray, ", ")
                        Counter = Counter + 1
                    End If
                Else: Exit For
                End If
            Next
            'Reset Counter for the DO loop
            Counter = 1
            Do While Counter <= UBound(ModeArray) And ModeArray(Counter) <> ""
                ModeCategoryUnit(1, Column) = Package
                ModeCategoryUnit(2, Column) = Protocol
                ModeCategoryUnit(3, Column) = ModeArray(Counter)
                Counter = Counter + 1
                Column = Column + 3
            Loop
        End If
    End If
Next

'Second, write the rest section of array ModeCategoryUnit()
'including Measurement, and its Category and Unit (same row)
Dim Measurement As String
Dim Category As String
Dim Unit As String
Dim Mode As String
Dim k As Integer    'row number of array ModeCategoryUnit()
Dim l As Integer    'column number of array ModeCategoryUnit()

For j = 1 To ColumnCount
    If TrimRaw(1, j) <> "" Then
        Package = TrimRaw(1, j)
        If TrimRaw(2, j) <> "" Then
            Protocol = TrimRaw(2, j)
            For i = 3 To RowCount
                If TrimRaw(i, j) <> "" Then
                    Measurement = TrimRaw(i, j)
                    Unit = TrimRaw(i, j + 1)
                    Catergory = TrimRaw(i, j + 2)
                    Mode = TrimRaw(i, j + 3)
                    'Search all the columns of array ModeCategoryUnit()
                    For l = LBound(ModeCategoryUnit, 2) To UBound(ModeCategoryUnit, 2)
                        If ModeCategoryUnit(1, l) = Package And ModeCategoryUnit(2, l) = Protocol And ModeCategoryUnit(3, l) = Mode Then
                            For k = 4 To UBound(ModeCategoryUnit)
                                If ModeCategoryUnit(k, l) = "" Then
                                    ModeCategoryUnit(k, l) = Measurement
                                    ModeCategoryUnit(k, l + 1) = Catergory
                                    ModeCategoryUnit(k, l + 2) = Unit
                                    Exit For
                                End If
                            Next
                            Exit For
                        End If
                    Next
                Else
                    If TrimRaw(i, j + 3) <> "" Then
                        Mode = TrimRaw(i, j + 3)
                        For l = LBound(ModeCategoryUnit, 2) To UBound(ModeCategoryUnit, 2)
                            If ModeCategoryUnit(1, l) = Package And ModeCategoryUnit(2, l) = Protocol And ModeCategoryUnit(3, l) = Mode Then
                                For k = 4 To UBound(ModeCategoryUnit)
                                    If ModeCategoryUnit(k, l) = "" Then
                                        ModeCategoryUnit(k, l) = Measurement
                                        ModeCategoryUnit(k, l + 1) = Catergory
                                        ModeCategoryUnit(k, l + 2) = Unit
                                        Exit For
                                    End If
                                Next
                                Exit For
                            End If
                        Next
                    Else: Exit For
                    End If
                End If
            Next
        End If
    End If
Next

'Use shared module "Function_SaveArrayAsExcelSheet" to write data from array to Excel
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(ModeCategoryUnit, 15, 60, ExcelFilePath_Measurement, "ModeCategoryUnit")

'Erase arrays
Erase ModeCategoryUnit
Erase TrimRaw
Erase Raw

End Sub

From the above script, we practiced the following:
1, Use a module “”Declaration_GlobalConstants”" to define a globe constants “ExcelFilePath_MeasurementTab”. (for more details see post#10)
2, Call Function_GetExcelSheetData to transfer data from the “Raw” sheet to array Raw(). (for more details see post#9)
3, Use Trim function to remove spaces on both sides of a string.
4, Join array elements to a string (ModeList = Join(ModeArray, “, “))
5, Call Function_SaveArrayAsExcelSheet to transfer data from array ModeCategoryUnit() to “ModeCategoryUnit” sheet. (for more details see post#16)
6, Erase array (e.g., Erase Raw)

There are an potential improvement for the above script. Currently, I hard coded the size of array “ModeCategoryUnit”, so when there are more measurements added, then I need to change the size in the script. However, there is a solution: “Adding New Elements to array on the Fly” . I didn’t do this because that the logic of the script is already complected, and I do not want to confuse you, but you can try this out by yourself!

 
1 Comment

Posted by Jia Qi in TestPartner (TP)

 

#16: Export data from Two-Dimensional Array (2D array) to an Excel Sheet

25 Jul

In Post#9, we talked about how to import data from Excel (with multiple sheets) to an Array.

In this post, we will discuss that how to export data from a Two-Dimensional Array (2D array) to an Excel sheet.

Function SaveArrayAsExcelSheet(ArrayData() As String, ArrayRowCount As Integer, ArrayColumnCount As Integer, ExcelFilePath As String, SheetName As String)

'On error jump to "Error Handler:" line
On Error GoTo ErrorHandler

'Variable declaration
Dim SheetCount As Integer

'Open the Excel file
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkBook = ObjExcel.Workbooks.Open(ExcelFilePath)

'Count how many sheets in the Excel file
SheetCount = ObjWorkBook.WorkSheets.Count

'If the sheet named "SheetName" exists, delete it
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        ObjExcel.DisplayAlerts = False  'Disable the delete confirmation message
        ObjWorkBook.WorkSheets(i).Delete
        ObjExcel.DisplayAlerts = True   'Enable the delte confirmation message
        Exit For
    End If
Next

'Create a new sheet in the Excel file and name this sheet as "SheetName"
ObjWorkBook.WorkSheets.Add.Name = SheetName

'Transfer data from an array to a Excel sheet
Dim ObjRange As Excel.Range
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        Set ObjRange = ObjWorkBook.WorkSheets(i).Range(ObjWorkBook.WorkSheets(i).Cells(1, 1), ObjWorkBook.WorkSheets(i).Cells(ArrayRowCount, ArrayColumnCount))
        ObjRange.Value = ArrayData()
    End If
Next

'Save and Close the Excel file
ObjWorkBook.Save
ObjWorkBook.Close

Exit Function

ErrorHandler: ' This is a normal VBA line label
    MsgBox Err.Description
    ObjWorkBook.Close

End Function

The VBA script above solves the problem (it will add or overwrite a sheet in the Excel file to receive data). The script should be saved as an shared module in TestPartner. To make the script work, you need to go to Tools->Reference to check the reference for Microsoft Excel, otherwise, TestPartner will give you an error on line “Dim ObjRange As Excel.Range”. (see Chapter 8 page 96 of “TestPartner Advanced Training Guide.pdf for Test Partner 5.2” listed in post#8 for more details.)

If you want to apply the script in QTP which uses VBScript instead of VBA, you need to make some minor modifications:

A, The function script needs to be created in the QTP Function Library (File – New – Function Library);

B, In the function script, delete anything related to the ErrorHandler (2 paragraphs);

C, In the function script, delete any definition for variable type, e.g., modify “SheetName As String” to “SheetName”, and change “Dim SheetCount As Integer” to “Dim SheetCount”.

D, Create a Test/Action to execute the function script, and the test/action will look like this (compare to TestPartner test script, no “Sub” nor “End Sub”, no need to assign the function to a variable, and no function file name nor brackets, i.e.,
in Testpartner (In TestPartner, if the function does not have a return value, but it has more than 1 input values, then in order to execute the function, you still need to define a variable in your test script, and assign the function to the variable.):

Sub Main()
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")
End Sub

in QTP:

SaveArrayAsExcelSheet ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")

E, Associate the Function file with the Test/Action file (right click on the Function file script and choose “Associate…” on the pop up menu), then you can run the function by running the test/action.

The script above should be fast enough to handle 10,000 data, but if your data is more than 50,000, then you may experience some slowness (depends on your computer speed too). There are two ways to solve the speed problem:
1, export the data from array to csv, then convert csv to Excel.
2, use ExcelCreator.NET instead of Excel Object, which is more efficient.
For more details, please refer to this website (you need to understand Chinese) .

 
No Comments

Posted by Jia Qi in Quick Test Professional (QTP), TestPartner (TP)