I’m trying to use a SQL command to reorder the columns in a csv I’m opening. I’ve verified that the SQL command will do what I want, however when I hit the File:Open command, I get “No value given for one or more required parameters.” It was working before I added the command string, is there additional information I have to add somewhere?
Context:
(Also, can anyone explain what’s up with the leading characters in the table preview here?)
And the command string is:
SELECT *, RIGHT(SourcePlate, 1) AS RightChar, LEN(SourcePlate) AS Length FROM [random#csv] ORDER BY SourcePlate, Length, RightChar
@mgildemeister I don’t know if this will solve the problem, however here are two things that I learned over time: 1) Long file paths like the one I see here can be an issue, including file names with spaces in it 2) Characters like #, $, % etc. in the SQL statement make it fail.
Hello,
A couple of points here:
• In your first screenshot, Column Name row 1 is 1 rather than “SourcePlate”, so this might cause an error; I believe the column name also needs to be a string.
• The weird leading characters are Byte Order Marks (BOM), which I’ve seen before when converting Excel files to .csv. There might be a specific way to convert the file or a specific csv format which prevents this from happening, or you can open the .csv in a text editor and delete them manually.
• Lastly, for your command string, I’d recommend starting with the most simple command possible and then incrementally adding to it and testing throughout. This helps a lot with troubleshooting as any error messages are more informative!
Hope that helps,
Claire
1 Like
Hi @mgildemeister ,
The SQL Command works as written, however the File: Open command needs to be modified so that the schema.ini generated when the file is opened contains the column headers for the two temporary columns listed in the SQL command (RightChar and Length), see below:
After the appropriate schema file is generated, the columns don’t need to be listed in the File: Open command, though it doesn’t hurt anything to have them there. The schema.ini being accurate is required to use SQL commands on Structured Text Files in VENUS.
Thank you,
Dan
3 Likes
