Exporting CSV or outputted data from Virtuoso

``` #!/bin/bash /usr/local/virtuoso-opensource/bin/isql-v 1111 dba dba <<'EOF' exec=" sparql select ?s ?p ?o { ?s ?p ?o. } limit 10;" > /download/output.txt EOF ``` ``` #!/bin/bash /usr/local/virtuoso-opensource/bin/isql-v 1111 dba dba <<'EOF' exec=" set blobs on; sparql define output:format 'CSV' select ?s ?p ?o { ?s ?p ?o. } limit 1000 " > /download/output.txt EOF ``` ### How to output from isql the result of a sql select query to a file in Virtuoso? ``` #!/bin/bash /usr/local/virtuoso-opensource/bin/isql-v 1111 dba dba exec="set blobs on; sparql define output:format 'CSV' select ?s ?p ?o {?s ?p ?o} limit 10;" ``` * [https://stackoverflow.com/questions/37784338/virtuoso-isql-result-dump-…](https://stackoverflow.com/questions/37784338/virtuoso-isql-result-dump-…) * [http://vos.openlinksw.com/owiki/wiki/VOS/VirtTipsAndTricksGuideISQLQuer…](http://vos.openlinksw.com/owiki/wiki/VOS/VirtTipsAndTricksGuideISQLQuer…) * [https://stackoverflow.com/questions/23868370/shell-select-data-with-spa…](https://stackoverflow.com/questions/23868370/shell-select-data-with-spa…) * [https://stackoverflow.com/questions/37784338/virtuoso-isql-result-dump-…](https://stackoverflow.com/questions/37784338/virtuoso-isql-result-dump-…) ### To export csv ```` custom.DBA.csvExport('SELECT * FROM cbw.DBA.mytable', '/Users/asanchez75/Trash/instances.csv'); ```` ### To import csv ```` csv_load_file('/Users/asanchez75/Trash/myfile.csv',0, null, 'cbw.DBA.mytable'); ```` You must create the procedure before ``` create procedure custom.DBA.csvExport( IN in_query VARCHAR, IN filename VARCHAR ) { declare query varchar; query := split_and_decode(in_query,0,'%'); file_delete(filename,1); declare meta, _dt any; declare errorCode, errorMessage varchar; declare inx integer; exec (query, errorCode, errorMessage, vector(), 0, meta, _dt); declare linestr varchar; declare flag integer; linestr := ''; if (meta <> 0) { flag := 0; FOREACH (varchar col in meta[0]) DO { if (flag = 0) flag := 1; else linestr:=concat(linestr,','); linestr := concat(linestr,'"',col[0],'"'); } string_to_file(filename,linestr,-2); string_to_file(filename,'\n',-1); -- Header is now complete inx := 0; if (_dt <> 0) { while (inx < length (_dt)) { linestr := ''; flag := 0; FOREACH (any col in _dt[inx]) DO { if (flag = 0) flag := 1; else linestr:=concat(linestr,','); linestr := concat(linestr,'"',col,'"'); } string_to_file(filename,linestr,-1); string_to_file(filename,'\n',-1); inx := inx + 1; } } } else return 'No records or some error'; } ``` ### References * [https://www.mail-archive.com/virtuoso-users@lists.sourceforge.net/msg06…](https://www.mail-archive.com/virtuoso-users@lists.sourceforge.net/msg06…) * [http://docs.openlinksw.com/virtuoso/fn_csv_load_file.html](http://docs.openlinksw.com/virtuoso/fn_csv_load_file.html)
Etiquetas