Unexpected preview or query results for virtualized tables over flat files in Data Virtualization

In Data Virtualization, if you virtualize a flat file that delimits strings, a preview of the table or queries against the table might not work as expected.

Symptoms

If you virtualize a table over a file that contains data that is similar to the following example, a data preview or queries against the table might not work as expected because there is no way to specify that string values are delimited by double quotation marks.

"spain","madrid"
"france","paris"

Query results include the double quotation marks that are used to delimit the string data, as shown in the following example.


NAME                                               CITY
-------------------------------------------------- --------------------------------------------------
"spain"                                            "madrid"
"france"                                           "paris"

Resolving the problem

You cannot use an option, such as a quoteChar option, to specify that strings in the file are delimited. There are two ways to resolve the issue.

  • Remove the string delimiters before you virtualize the table.
  • Modify your queries to recognize that string values are delimited.

For example, to return all rows in which the CITY column contains the value paris, run the following query.

select * from t1 where city = '"paris"'