Hey yall, Hopefully this is the last entry in my saga against .csv and SQL commands.
I have a method where I am pulling info from a .csv worklist. After using this information to run my method, I want to update the worklist to show what plate wells have been used. However, alongside the normal schema issues when working with .csv files in venus I am running into a particularly frustrating issue with calling a Update SQL command.
FilePath:
Filename:
SQL command:
Upload file:
Method:
Would really appreciate any advice on overcoming this issue as there is very little example content out about using the SQL Update command in venus only mentions of how the syntax is set up.
Hi @Gellator,
To start, the error you’re receiving is because you don’t have any columns selected in your File:Open command. The “missing parameters” are calls to the columns listed in your SQL Command. I recognize this is a bit confusing since there are other SQL Commands that require a different column setup.
The second item is your File:Open Mode, which is set to Write. This mode will create a new file, overwriting the currently existing file of the same path. You’ll need Mode set to Append for this to work correctly.
Lastly, the UPDATE command cannot be used with .csv or .txt files (linked tables . You will need to switch to using either a .xls or .mdb file to allow the UPDATE functionality.
Let me know if you have any questions.
Thank you,
Dan
1 Like
Ignoring my other errors involving using the Update SQL command. Is there any other commands that do not work with .csv files? As my main goal is to update the .csv column “RunDate” selectively. While I can use “SELECT * FROM” with conditions to filter to the exact row using “Append” in File: Open only allows me to write an additional row with that rows information and the RunDate but leaves behind a duplicate.
Is there a way to overcome this besides changing file from a .csv?
Hi @Gellator,
Unfortunately, structured text files in general are limited in terms of SQL functionality available to them. I do not have a list of which SQL commands do or don’t inherently work with .csv files.
I would suggest using and updating either .xls or .mdb files during the run, with code at the end of the run/in the OnAbort tab to read through and write that data to a new .csv file. Alternatively, you could read through and rewrite the .csv into a new file each time you need an update, but that is a lengthier process.
Thank you,
Dan
