Forum Discussion

fayrehouse's avatar
fayrehouse
Frequent Contributor
11 years ago
Solved

Working with CSV files - but NOT DDT

Hi all.



Has anyone got any experience / suggestions for the following....



I have a script that runs fine - producing a CSV file as part of it's processing. Now, I need to perform various checks on the CONTENT of this CSV. Note that the CSV is NOT the driver for the test, so as far as I know, I cannot use the DDT object.



I'm hoping there's a way I can:



1. Read the whole CSV file, treating the top row as column names

2 Do comparisons on the remaining data, in the form of "check field_x has value_y where field_a has value_b" - and then testcomplete would find the correct row (where field_a had a value of "value_b" - and validate the required value of "field_x".



Does this make sense? Does anyone have any suggestions?



Cheers



Steve



  • aLostDawg's avatar
    aLostDawg
    11 years ago
    You should be able to use the DDT object without using the CSV as a Driver of the test. In my experience it's a little clunky, but doable. You just have to create a new DDT driver and name it something different than the test driver DDT. Then you can iterate through it and find your information. 



    The challenge in using the DDT object is that it thinks it HAS TO be a driver, so inherent in that attribute it defaults to read row by row. This means it's predictable, but can be tedius when looking up the last row of the CSV. In other words you can't just ask for the value of Column_D, Row_P you have to go iterate through the file and find it. 



    What I do is something like this (I use Excel and VBScript for this, so change it to a CSV driver and your language of choice and you should be good):



    'varible for the excel or csv file.

    validationFileDriver = [LOCATION TO FILE]

    'this next line sets the driver file object [TabName] is the name of the Excel sheet... this is for Excel 2010

    Set valFileDriver = DDT.ExcelDriver(validationFileDriver, [TabName], True)

    'This line names the driver, I just put Validation driver in there, you can call it what you want.

    valFileDriver.Name = [VALIDATION DRIVER]





    Then you'll have to iterate through the rows and find the row you need. Since TC doesn't have a clue which column is which and I want my code to be robust I've written a little function called FindColumn where 'header' is the header text you're looking for and 'dataSheetDriver' is the [VALIDATION DRIVER] from above.




    Function FindColumn(header, dataSheetDriver)


      


      colCount = DDT.DriverByName(dataSheetDriver).ColumnCount


     


      For i = 1 to colCount


        currentColumn = DDT.DriverByName(dataSheetDriver).ColumnName(i-1)


        If currentColumn = header Then


          FindColumn = i-1


          'Log.Message header & " column value = " & FindColumn


          Exit For


        End If


      Next      


     


    End Function



    And then I use that to find a value in a specific column ( and row. 



    If DDT.DriverByName([VALIDATION DRIVER]).Value(FindColumn(field_a, [VALIDATION DRIVER])) = "value_b" Then

      'Do whatever you want with the rest of this row

      'say you have a field_x column as well you could ...

      field_x_value = DDT.DriverByName([VALIDATION DRIVER]).Value(FindColumn(field_x, [VALIDATION DRIVER]))



    Else

      Log.Message "Didn't find '" & field_a & "'."

    End If





    You'll probably have to wrap that If-Then statement in a For-Next loop that iterates through the whole sheet, but I hope this gets you headed toward a solution. 





    JC

6 Replies

Sort By
  • hlalumiere's avatar
    hlalumiere
    Regular Contributor
    11 years ago
    To follow up on ADO, it is probably the simplest and most clutter-free way to work with CSV and XLS files... In your case you could modify this to use custom queries, and query your CSV directly:



    Query = "SELECT * FROM " & objCsvFile.Name & "WHERE field_a = value_b AND NOT field_x = value_y;"



    The resulting rows will be the ones in violation, if any. You only need to check that the query result has no rows to pass.



    Function ReadCsvAsRecordSet(strPathToCSV, bFirstRowIsHeader)

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objConn = CreateObject("ADODB.Connection")

        Set ReadCsvAsRecordSet = CreateObject("ADODB.Recordset")



        If Not objFSO.FileExists(strPathToCSV) Then

            Exit Function

        Else

            Set objCsvFile = objFSO.GetFile(strPathToCSV)

        End If

        

        If bFirstRowIsHeader Then

            HDR = "Yes"

        Else

            HDR = "No"

        End If

        

        objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                     "Data Source=""" & objCsvFile.ParentFolder & """" & _

                     ";Extended Properties='text;HDR=" & HDR & ";FMT=Delimited(,)';"

        

        ReadCsvAsRecordSet.Open "SELECT * FROM " & objCsvFile.Name & ";" , objConn



    End Function




    And you can use it like this:



        Set objResultSet = ReadCsvAsRecordSet("C:\Users\hlalumie\Documents\SampleCSV.csv", True)



        Do Until objResultSet.EOF

            For i = 0 To objResultSet.Fields.Count - 1

                strOut = strOut & objResultSet.Fields.Item(i) & vbTab

            Next

            strOut = strOut & vbCrLf

            objResultSet.MoveNext

        Loop
  • aLostDawg's avatar
    aLostDawg
    Occasional Contributor
    11 years ago
    You should be able to use the DDT object without using the CSV as a Driver of the test. In my experience it's a little clunky, but doable. You just have to create a new DDT driver and name it something different than the test driver DDT. Then you can iterate through it and find your information. 



    The challenge in using the DDT object is that it thinks it HAS TO be a driver, so inherent in that attribute it defaults to read row by row. This means it's predictable, but can be tedius when looking up the last row of the CSV. In other words you can't just ask for the value of Column_D, Row_P you have to go iterate through the file and find it. 



    What I do is something like this (I use Excel and VBScript for this, so change it to a CSV driver and your language of choice and you should be good):



    'varible for the excel or csv file.

    validationFileDriver = [LOCATION TO FILE]

    'this next line sets the driver file object [TabName] is the name of the Excel sheet... this is for Excel 2010

    Set valFileDriver = DDT.ExcelDriver(validationFileDriver, [TabName], True)

    'This line names the driver, I just put Validation driver in there, you can call it what you want.

    valFileDriver.Name = [VALIDATION DRIVER]





    Then you'll have to iterate through the rows and find the row you need. Since TC doesn't have a clue which column is which and I want my code to be robust I've written a little function called FindColumn where 'header' is the header text you're looking for and 'dataSheetDriver' is the [VALIDATION DRIVER] from above.




    Function FindColumn(header, dataSheetDriver)


      


      colCount = DDT.DriverByName(dataSheetDriver).ColumnCount


     


      For i = 1 to colCount


        currentColumn = DDT.DriverByName(dataSheetDriver).ColumnName(i-1)


        If currentColumn = header Then


          FindColumn = i-1


          'Log.Message header & " column value = " & FindColumn


          Exit For


        End If


      Next      


     


    End Function



    And then I use that to find a value in a specific column ( and row. 



    If DDT.DriverByName([VALIDATION DRIVER]).Value(FindColumn(field_a, [VALIDATION DRIVER])) = "value_b" Then

      'Do whatever you want with the rest of this row

      'say you have a field_x column as well you could ...

      field_x_value = DDT.DriverByName([VALIDATION DRIVER]).Value(FindColumn(field_x, [VALIDATION DRIVER]))



    Else

      Log.Message "Didn't find '" & field_a & "'."

    End If





    You'll probably have to wrap that If-Then statement in a For-Next loop that iterates through the whole sheet, but I hope this gets you headed toward a solution. 





    JC

  • paul_scroce's avatar
    paul_scroce
    Frequent Contributor
    11 years ago
    Hi Steve



    To solve these problems I have sometimes found it quicker to create ADODB recordsets that are easy to filter and sort when checking the content of CSV or XLS files.



    There is an example in this article but you need to change the connection string to use a text CSV file as the data source.

    http://support.smartbear.com/viewarticle/32734/
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    11 years ago
    Hi,



    In addition to the great sample by Hugo: ADO recordset also supports the .Filter property. This makes it possible, after getting the data with the SQL query, filter the obtained data additionally, without the necessity to execute another query.
  • fayrehouse's avatar
    fayrehouse
    Frequent Contributor
    11 years ago
    Thanks Paul - Unfortunately your response came in after I'd marked JC's response as the answer.... That said, I don't think I was wrong to do so - I think both his, AND your response are valid answers!  :)



    At the moment the precise requirements of what I'll need to do with the CSV contents are a bit lacking! When I know more of what I need to do in detail, I can choose one of your two solutions :)