I have a long list of values in a single column csv file which I want to use as SELECT queries. Looping through the list looks like a slow way and something along the lines of SELECT * from table WHERE x IN csv approach looks quicker. However, I don't see how to use the IN command unless referring to another table. Is it possible to pass the csv as an argument, i.e. use the filename to pass the values?
1
-
This might be a better question for superuser.comJames A Mohler– James A Mohler2013-10-12 18:20:26 +00:00Commented Oct 12, 2013 at 18:20
Add a comment
|
2 Answers
Yes you can do it. A little copy and paste and you are good to go.
SELECT *
FROM SOME_TABLE
WHERE SOME_TABLE.ID IN
(
5,
10,
31,
72
)
4 Comments
James
I appreciate the question wasn't well phrased. I'll be getting a lot of these files and the one I have is hundreds of lines. Future ones could be much larger so I was looking for a way of using the filename to pass the values
EvilTeach
Ah ok. If you are using Oracle, you can work with your DBA to create an area to store the file. The file can then be read as an external table. You can then Use a select statement to pull data from that table, and do a JOIN or an IN or whatever your heart desires.
EvilTeach
Another possibility is to use Perl, and the DBI module to do the sql piece. I am sure there are several Perl modules on CPAN that read csvs nicely.
James
I'm using postgresql which some googling tells me doesn't have external tables. It suggested just making a temporary table for the csv so I did that and then did an IN on that table. Problem solved
If ok to do manually, bring it into a text editor and replace newline characters with ", ". I've used both MS Word and Notepad++ for this, but any editor that can specify special characters like linefeed should be able to do it. Then you can cut and paste the resulting string into an IN statement.
Even putting in editor and appending a "," in front of each line can work, if a bit lengthy and ugly.
