Please reference the screen shot. I have includes 2 VERY simple SQL expressions. The bottom one works perfectly, the top one is the one that is returning the error in RED. I am actually using a Variable to pull the 2 values highlighted, but chose for testing purposes to just enter the values directly in the statement. The issue is the fact that the one producing the error is Alpha Numeric and the one working is Numeric. The .CSV file seems to be correct but that i am not sure what else to check in that file . ,CSV files are pretty basic! Planet Press SQL does not seem to like the CAST command ( I tried defining the J208 as char). NOTE. I am NOT running both expressions at the same time. I am including both as a side by side comparison only.
Not sure where to go from here.
Most likely, your ODBC definition is incomplete because the CSV file does not use string delimiters on all fields.
The following CSV (named myFile.csv) produces the exact same results as yours by default:
first,last,email,id
John,Doe,jdoe@example.com,123
Jane,Smith,jsmith@example.com,j204
With the above file, the following statement works:
SELECT * from [myFile.csv] where id=123
because the id field is considered numeric since the file does not use delimiters. And that’s why the statement:
SELECT * from [myFile.csv] where id='j204'
does not work.
So you have two alternatives: either make sure all fields use string delimiters or change the ODBC definition so that each field in the file is considered as type Char by default:
This will create a schema.ini file in the folder where your csv is located. You can actually edit that file manually to modify the definition (although that’s not recommended). The content will look something like:
[myfile.csv]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=25
CharacterSet=OEM
Col1=FIRST Char Width 255
Col2=LAST Char Width 255
Col3=EMAIL Char Width 255
Col4=ID Char Width 255
With this definition in place, all fields are now considered character strings and your queries should work just fine.
Thanks Phil.thanks for confirming that the data type was incorrect. First, I did not know that I could apply a string delimiter to a CSV file that was different from the normal delimiters of , or tab or | (I assumed that all fields in a CSV file were default assigned as characters since there is really no way to define them in the file itself) . so I went straight to your second recommendation. I set up the options as you suggested, and received the following error. Please note, the Character Width WAS specified as 255 but the dialog changed it when it wrote the Schema.ini file. manually modifying the character length in that file gets me back to the original error.
Given the above settings in your ODBC connection, your syntax should read something like this:
SELECT * from [360Salespeople.csv] where [DDMS] = '338'
Make sure you use single quotes, just like the above example does. The bracket notation is only required if there are spaces in the names of the files and fields, but I included it to showcase a more generic syntax.
Obviously, you must make sure the schema.ini specifies Char Width 255 (or some other reasonable value!) for each field.
Thanks Phil. The SELECT * (all ) statement was not the issue. I finally got the Schema file edited so it liked the Char Width 255 lines. and that fixed the issue. I am not sure why the ODBC form would not write the correct value to the schema file.
Thanks again for your help. Any reason that you know of that Planet Press does not recognize the CAST command in the SQL statements? That is what I usually use if I need to change a data type.
The SQL statement is validated by the ODBC driver, not by PlanetPress.
As far as I know, the CAST statement is not supported by the Text/CSV ODBC driver as it treats all values as strings. But I could be wrong, don’t take my word for it. Perhaps someone else can give a working example but I couldn’t find one anywhere.
I even tried asking ChatGPT - hey, doesn’t everyone these days?!? 
- and it pretty much confirmed my suspicions.
The CAST keyword belongs to Transact-SQL, which is what SQL Server and MySQL use. The Microsoft Access Text driver uses Jet SQL, a different dialect with no support for CAST.
Jet SQL does have other type conversion functions although I don’t think they will help here.