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"'