Exporting CSV data from Virtuoso

Submitted by admin on Mon, 02/08/2016 - 15:39

To export csv

cbw.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

<code>
create procedure
exmo.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';

}
</code>

References
https://www.mail-archive.com/virtuoso-users@lists.sourceforge.net/msg06…
http://docs.openlinksw.com/virtuoso/fn_csv_load_file.html

Etiquetas