When porting over some of my protocols from Venus 4 → Venus 6 some of my SQL statements for sorting the files had to be updated. I am wondering if this is a known issue? I vaguely remember that Venus 6 uses a newer Microsoft DB interface.
I specifically was having issues with: Venus 4 SQL: "SELECT DISTINCT [Priority] FROM [Sheet1] ORDER BY [Priority] ASC"
Modified to work in Venus 6 SQL: "SELECT DISTINCT *, [Priority] FROM [Sheet1] ORDER BY [Priority] ASC"
I can write up an example script to test fully tomorrow, but when using the Venus 4 SQL command in Venus 6 it could not find the ordinal, aka the column.
Ok, after doing some testing I do not think this is Venus6 related.
Oddly my SQL command seems to pickup Sheet1 on Hamilton created .xls files, but not manually created ones. On manually or “hand created” .xls it cannot find “Sheet1”
For example I am ok to loop over a Hamilton generated file (I write the file with Hamilton method editor then loop over and read the same file) with the SQL command:
“SELECT DISTINCT [Priority] FROM [Sheet1] ORDER BY [Priority] ASC”
Venus 6 updated JET, to allow XLSX etc support, so the change might be due to that. But it also may be due to the difference between .XLS and .XLSX?
So not entirely surprising some changes may be needed… Would be interested to see what the guys from Hamilton say as its these sort of upgrade issues that worry us.
For any file that is manually created a $ is required when specifying the sheet. Updating the SQL command to “SELECT DISTINCT [Priority] FROM [Sheet1$] ORDER BY [Priority] ASC” should correct your issue. This will also work for files that were generated by VENUS.
This is due to how the Microsoft Jet database engine interacts with files and to my knowledge this requirement has been around since before VENUS 4.
You can double check this requirement by using the Column Specification Helper.
Thanks Brandon! I either completely forgot, or didn’t know about the difference in the sheet name for manually generated sheet. I have exclusively been doing SQL look ups on Hamilton created excel sheets, so this took me off guard when doing some testing to isolate the my other protocol that I ported from Venus4 → Venus 6.
This was actually a secondary error that I ran into when trying to isolate the first SQL error. Let me try to isolate the Venus4 vs Venus6 SQL today.
I was able to isolate my SQL issue, it does not seem to be Venus6 related. I can change the title of this thread accordingly.
It seems that when I read with an SQL statement I can only access the column variables defined in that statement, even though they do in fact exist in the .xls file. Is there a work around for this?
For example I am trying to read a .xls in order of priority then print distinct priorities and sample names in ascending order. I can access the priority but once I try to add the “Sample Name” column, Venus returns “Ordinal not found”
I am now realizing I am going about this wrong, and the SQL is returning the correct values. That there is no “sample name” associated with the distinct priorities.
