Forum Discussion

pkudrys's avatar
pkudrys
Contributor
2 years ago
Solved

CSVDriver returns null or empty strings

Hi folks,

 

I'm experiencing a problem with CSVDriver. While looping CSV file, CSVDriver returns null/empty string for some values. Please see the attached screenshot and sample project. I see no obvious reason for this behavior. Am I using the CSVDriver wrong or I found a bug? Thanks.

 

Debugging
Keyword Tests
Script Tests
Test Results
Web Testing
  • tristaanogre's avatar
    tristaanogre
    2 years ago

    Generally speaking, when using the CSVDriver, you're using a version of Microsoft JET.  So, it's trying to treat the CSV file as a data table and most data tables have columns designated as specific "types".  Column 1 is string, column 2 is integer, column 3 is boolean, etc.  You get the point?

     

    So, your second column is a mixed column.  And that's kinda confusing for the JET engine.  So, what I would suggest is to standardize the column.  Probably the easiest way is to force it to be string/text and then, where you're using the values, use the appropriate conversion code to convert the string to boolean, integer, etc.  

     

    EASIEST way to force a column to all be recognized as text is to wrap all the values in double quotes (").  Give this a try and see if that works.  Alternatively, you could create a schema.ini file in the directory containing your CSV file to dictate the format of the columns, column names, etc.

    https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver16

     

5 Replies

Sort By
  • pkudrys's avatar
    pkudrys
    Contributor
    2 years ago

    Thank you Robert. Wrapping values in double quotes helped. So I will update also my CSV writing method, to enclose all values in double quotes.

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    The second column contains strings and integers. It needs to be of a specific type.

  • pkudrys's avatar
    pkudrys
    Contributor
    2 years ago

    Hi,

     

    Thank you for the reply. Hm, there is nothing about such limitation mentioned in the CSVDriver description. And if there is such limitation, it makes the CSVDriver basically useless. It's a pretty common behavior that the values in CSV file are of a mixed type! Limiting CSV reading to just single value type is a major drawback of CSVDriver.

     

    BTW, the first CSV value from my example is string 'xxx', second value is 'true' and the rest of values are integers. So why the script returns only numbers? I mean, logical behavior would be, that the script would returns first value correctly and the rest of values null/empty?

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      2 years ago

      Generally speaking, when using the CSVDriver, you're using a version of Microsoft JET.  So, it's trying to treat the CSV file as a data table and most data tables have columns designated as specific "types".  Column 1 is string, column 2 is integer, column 3 is boolean, etc.  You get the point?

       

      So, your second column is a mixed column.  And that's kinda confusing for the JET engine.  So, what I would suggest is to standardize the column.  Probably the easiest way is to force it to be string/text and then, where you're using the values, use the appropriate conversion code to convert the string to boolean, integer, etc.  

       

      EASIEST way to force a column to all be recognized as text is to wrap all the values in double quotes (").  Give this a try and see if that works.  Alternatively, you could create a schema.ini file in the directory containing your CSV file to dictate the format of the columns, column names, etc.

      https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver16

       

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    A CSV (comma-separated values) file is a text file that has a specific format which allows data to be saved in a table-structured format. It's function as designed.