CSV: Unable to Retrieve Columns for Table [csv]

If you are getting the error "unable to retrieve columns for table [csv]" and the tablename is exactly "[csv]," this is likely caused by incorrectly escaping the tablenames in the query issued to the CData Driver.

Background Information and Identifying the Issue

The CData CSV Driver allows the user to connect to CSV data hosted on a variety of sources: Google Drive, Dropbox, Amazon S3, a local file, and more. If a user connects to a file named "persons.csv" for example, the CData Driver will expose a table with the name "persons.csv." In some cases, a user or an application might attempt to query the table with SELECT * FROM schemaname.tablename, which, in this example, is: SELECT * FROM persons.csv However, the period (.) character is a separator for identifying parts of a fully qualified table name. In the case of persons.csv, "persons" would incorrectly identify a schema name, and "csv" the tablename. In the CData logfile, the issue would appear like the following: 2020-01-01T11:54:30.122-04:00 0 [Connection: 3] Query Failed: [SELECT * FROM persons.csv]. Error: Unable to retrieve columns for table [csv]. 2020-01-01T11:54:30.123-04:00 0 Unable to retrieve columns for table [csv].

Resolving the Error

• Escape the table name in the query
The table name and schemaname may be escaped with either a double quote (") or square brackets ([]) to identify the separation: SELECT * FROM "persons.csv" SELECT * FROM [persons.csv]
• Set ExcludeFileExtensions=True
The CData Driver supports a connection string property ExcludeFileExtensions, which will remove .csv from the tablename - thus listing a filename "persons.csv" as a table named "persons" rather than "persons.csv." This avoids the potential case where the tablename is unescaped in queries to CData.