CSVDriver returns null or empty strings

[pkudrys]

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.

[rraghvani]

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

[pkudrys]

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 · Accepted solution]

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-serve...

[pkudrys]

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]

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.