W3C home > Mailing lists > Public > public-sparql-dev@w3.org > January to March 2016

Re: Loading SPARQL 1.1 Query Results CSV Format to Oracle

From: Miguel <miguel.ceriani@gmail.com>
Date: Fri, 15 Jan 2016 19:30:54 +0000
Message-ID: <CALWU=Rs7-=L_L6nJ+RpzPjZrVEfV115h9Rx-RV5-MyBMf1qwwg@mail.gmail.com>
To: John Walker <john.walker@semaku.com>, public-sparql-dev@w3.org
Hi John,
Oracle SQL*Loader seems to be unnecessary "peeky", because a newline inside
a block surrounded by quotation marks could be distinguished from the
newline at the end of the record.

I thought about a possible workaround that does not involve creating
scripts but I am not 100% sure that it works Oracle-side.

1) Add a dummy output variable to the SPARQL query, after all the other
variables, and giving it a fixed value.

SELECT ?foo ?bar ?baz ('||' AS ?recordSep)
WHERE { ... }

(actually I guess any chosen fixed string should create an unambiguous end
of record, even the empty string)

2) Change accordingly the separator used in SQL*Loader control file

In this case the record separator became ,"||" followed by newline.
The new control file should be something like this.

....
infile myfile.csv        "STR ',\"||\"\r\n'"
...

I hope it helps.
Please let me know if it worked :)

Regards,
Miguel

Il giorno ven 15 gen 2016 alle ore 18:33 John Walker <john.walker@semaku.com>
ha scritto:

> Hi SPARQL fans,
>
> For a project I'm currently working on we are using SPARQL to provide
> tabular
> views of an RDF dataset.
> These views need to be loaded to Oracle and we are planning to use
> SQL*Loader to
> load SPARQL query results (in SPARQL 1.1 Query Results CSV Format) to the
> Oracle
> DB.
>
> However we run into problems where the RDF literals contains newline
> characters.
>
> So in the CSV results we see:
>
> foo,bar,baz
> 1,2,3
> a,b,c
> d,"some
> multi
> line
> text",f
>
> The control file we use is something like this:
>
> OPTIONS ( SKIP=1 )
> load data
> infile myfile.csv        "STR '\r\n'"
> TRUNCATE
> into table my_table
> FIELDS TERMINATED BY ','
> OPTIONALLY ENCLOSED BY '"'
> TRAILING NULLCOLS
>
> However this treats each new line as a new record.
>
> I guess this is not a SPARQL thing directly, but hopefully someone on the
> list
> has attempted something similar and can provide some sage advice :-)
>
> Regards,
>
> John Walker
> Principal Consultant & co-founder
> Semaku B.V.
> SFJ 4.009, Torenallee 20, 5617 BC Eindhoven
> Mobile: +31 6 475 22030
> Email: john.walker@semaku.com
> Skype: jaw111
>
> KvK: 58031405
> BTW: NL852842156B01
> IBAN: NL94 INGB 0008 3219 95
>
>
>
>
Received on Friday, 15 January 2016 19:31:37 UTC

This archive was generated by hypermail 2.3.1 : Friday, 15 January 2016 19:31:38 UTC