Re: Loading SPARQL 1.1 Query Results CSV Format to Oracle

Hi Miguel,
 

> On January 15, 2016 at 8:30 PM Miguel <miguel.ceriani@gmail.com> wrote:
> 
>  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 :)
> 

Will give it a shot. Although perhaps the extra variable needs to have the same
name as  is used for the separator otherwise the parser might see an extra
column in header row that is not in the other rows.

SELECT ?foo ?bar ?baz ('__' AS ?__)

>   
>  Regards,
>  Miguel
> 
>  Il giorno ven 15 gen 2016 alle ore 18:33 John Walker <john.walker@semaku.com
> <mailto: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 <mailto:john.walker@semaku.com>
> >    Skype: jaw111
> > 
> >    KvK: 58031405
> >    BTW: NL852842156B01
> >    IBAN: NL94 INGB 0008 3219 95
> > 
> > 
> >  >