CSV: Unable to Retrieve Columns for Table [csv]
In this entry you will find the resolution for the error: unable to retrieve columns for table [csv].
Date Entered: 3/10/2020 Last Updated: 3/10/2020 Author: James Baggs
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:
- 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.
SELECT * FROM "persons.csv" SELECT * FROM [persons.csv]
We appreciate your feedback. If you have any questions, comments, or suggestions about this entry, please contact our support team at email@example.com.