Forum Discussion

pkudrys's avatar
pkudrys
Contributor
2 years ago

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 o...
  • 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