Proper path to query CSV files

So I'm querying a CSV file to build tables in a wiki app. I couldn't get it to work at all until I added it to the list of databases in configure, including the full path to the CSV file as the DSN attribute. Then it still wouldn't work until I also provided the full file path in the FROM clause, like below:

%SQL{"select distinct userid, name from /full/path/to/file.csv where status = 'ACTIVE'" database="odsmetadata"}%

Is there some way to define the database connection such that I can use some other, shorter token in the FROM clause? That, or maybe we should update the SqlPlugin docs to include a working CSV example.

-- AaronFuleki - 31 Jul 2014

Never been playing with it. Or I did once and found it as refreshing of an experience as you did :/

Best would be to have some way to create database definitions for %SQL based on csv (or excel) files being attached to a topic ... which is definitely a missing feature.

Any help welcome.

-- MichaelDaum - 31 Jul 2014

Yes, it's possible to use a shorter token in the FROM clause. I think that your problem was defining the database path as the full path to a CSV file. In the perl dbi:CSV driver the parent dir is the database and each file inside of it are the tables.

You have to define the CSV database using the parent directory of the CSV file(s):

{SqlPlugin}{Databases} =
[
  {
    'id' => 'test-csv',
     'dsn' => 'dbi:CSV:f_dir=/path/to/csv-database/'
  },
];

Adjust the permissions in {SqlPlugin}{AccessControl} and then create some test data:

$ cat > /path/to/csv-database/people << DATOS_CSV
  id,name
  1,Pepe Perez
  2,Manolo Garcia
DATOS_CSV

After that you should be able to make queries like this:

%SQL{database="test-csv" query="SELECT id,name FROM people"}% 

By the way, it would be very nice to have databases attached to topics...

-- RafaelVarela - 02 Aug 2017

Commenting is disabled while not logged in