Quantcast

feed a CSV file into a IN statement

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

feed a CSV file into a IN statement

Federico Bruni-2
Hi all

I wonder if I can tell Squirrel to take a list of items from a CSV file.
I'd like to have a query which works like this (pseudocode):

SELECT pasta FROM menu WHERE pasta IN :csvfile

and the CSV file may contain the items I want to use to filter the
query, such as:

pesto;tomato;butter

Perhaps there's a plugin for this purpose?

I don't want to type each item interactively, as I could do with
sqlparam plugin.
And I don't want to edit my saved query (under version control) each
time I want to run a new query.

Thanks in advance
Federico





------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: feed a CSV file into a IN statement

John Hardin
On Fri, 24 Feb 2017, Federico Bruni wrote:

> I wonder if I can tell Squirrel to take a list of items from a CSV file.
> I'd like to have a query which works like this (pseudocode):
>
> SELECT pasta FROM menu WHERE pasta IN :csvfile
>
> and the CSV file may contain the items I want to use to filter the
> query, such as:
>
> pesto;tomato;butter
>
> Perhaps there's a plugin for this purpose?
>
> I don't want to type each item interactively, as I could do with
> sqlparam plugin.
> And I don't want to edit my saved query (under version control) each
> time I want to run a new query.
>
> Thanks in advance
> Federico

There is a data import plugin but it's not scriptable (as far as I know -
I don't use it myself).

Is it acceptable to pop a UI and type in the filename and some other
details every time you want to do that?

If that's acceptable then how about this:

(1) create your data file with one value per line rather than multiple
delimited values on one line.

(2) write some SQL to create a work table (e.g. t_pasta) with one column
of the correct datatype. A session-temporary table (e.g. MSSQL #t_pasta)
may or may not work depending on JDBC connection pooling, so you might
need to use a "permanent" temporary table (i.e. one you have to explicitly
drop when you're finished with it).

(3) run the data import plugin to populate that table with your data file.

(4) write your query to do this:

SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta_file);

(5) remember to drop t_pasta when you're finished with it if it's not
a true session-temporary table.

You wouldn't have to type in the data (you do have to type in the filename
and some other info to load the data, but that shouldn't change from
session to session assuming whatever is generating the data file puts it
in the same place every time), and your SQL script can be read-only.


You might want to go to the Squirrel-SQL bug tracker at SourceForge and
create a feature request for adding scriptability to the data import
plugin so that the interactive part of the above could be eliminated.



--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Insofar as the police deter by their presence, they are very, very
   good. Criminals take great pains not to commit a crime in front of
   them.                                             -- Jeffrey Snyder
-----------------------------------------------------------------------
  432 days since the first successful real return to launch site (SpaceX)

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: feed a CSV file into a IN statement

John Hardin
On Sat, 25 Feb 2017, John Hardin wrote:

> (2) write some SQL to create a work table (e.g. t_pasta) with one column of
>
> SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta_file);

Oop - apologies for the editing error there, it should of course be:

   SELECT pasta FROM menu WHERE pasta IN (SELECT pasta FROM t_pasta);


--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Insofar as the police deter by their presence, they are very, very
   good. Criminals take great pains not to commit a crime in front of
   them.                                             -- Jeffrey Snyder
-----------------------------------------------------------------------
  432 days since the first successful real return to launch site (SpaceX)

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Loading...