Search Mailing List Archives
[protege-owl] An issue after importing "date time" values with DataMaster-causes problem to Pellet
aristeidis.matsokis at epfl.ch
Wed Mar 25 02:25:18 PDT 2009
Thank you for your suggestions. The "ODBC data source that refers directly to your CSV file" seems more promising, although I have to find a way to auto-transform the csv files (because they could be too big to do it manually) to the right format.
Date: Mon, 23 Mar 2009 13:25:29 -0700
From: Csongor Nyulas <csongor.nyulas at stanford.edu>
Subject: Re: [protege-owl] An issue after importing "date time" values
with DataMaster-causes problem to Pellet
To: User support for the Protege-OWL editor
<protege-owl at lists.stanford.edu>
Message-ID: <49C7F039.1070201 at stanford.edu>
Content-Type: text/plain; charset="windows-1252"
Following up on this, I thought I will test my recommendation of using
MS Access and ODBC data sources, and I realized that there is an even
more straightforward solution. You can create an ODBC data source that
refers directly to your CSV file, and_ if you format your csv file
properly_ the ODBC driver will recognize the file as a database table
and the type of the Birth_Date and End_Date columns as datetime, and
DataMaster can import the instances properly directly from your CSV file.
See attached the modified file that DataMaster can import correctly on
my machine (i.e. with my regional settings)
You can make a diff between your CSV file and mine to see what you
should be careful about when you create your CSV files in the future
(e.g. don't leave space after "Birth_Date" and before the comma; do not
use quotation marks and the separator 'T' in the timestamps, etc)
Csongor Nyulas wrote:
> Hi Aristeidis,
> Looking more deeply into this issue I have discovered that my earlier
> statement was not fully accurate.
> DataMaster can deal with the JDBC DATE, TIME and TIMESTAMP column
> types which it translates to xsd:date, xsd:time and xsd:datetime RDF
> types. I also extended xlSQL to be able to deal with Date and Time,
> but unfortunately it is not able to deal with timestamp/datetime. This
> is a restriction of the jExcel driver or maybe Excel itself, so I
> can't do much about that.
> Anyway you can check Sheet5 and Sheet6 for solutions of how to format
> Excel columns to be importad as dates and times. If you really need
> datetime you have to use a database that has a JDBC driver that can
> produce TIMESTAMP column types. Just as an example for alternative
> solution (though not the most powerful) is to convert your Excel table
> into an Access database and import the content of that Access database
> with DataMaster through an ODBC driver. The ODBC driver can produce
> xsd:datetime properties (but no xsd:date or xsd:time).
> Matsokis Aristeidis wrote:
>> Dear Csongor,
>> In your previous mail you mentioned: ?XlSQL-Y8 can deal also with
>> correctly formatted date and time fields!?.
>> Is there a way you could guide me to make the right format?(original
>> data in csv file)
>> The case is that I have already instances with Birth_Date and
>> End_Date dateTime properties.
>> Step 1
>> Thus, prior to importing the instances of the xls file attached, I
>> change the Birth_Date and End_Date properties Range of the ontology
>> from ?date time? to ?string?, then I import the data through
>> DataMaster. The result is the following:
>> ? Sheets 1,4 work with the Pellet 1.5.1 or 1.5.2 BUT do not work with
>> SWRL (swrl doesn?t see them as date-times)
>> ? Sheets 2,3 work with the Pellet 1.5.1 or 1.5.2 AND do work with SWRL
>> From the results, I chose sheets 2 and 3, since I am using SWRL to
>> make queries.
>> Step 2
>> Then, I change the Birth_Date and End_Date properties Range of the
>> ontology from ?string? to ?date time?, because it makes the interface
>> more user friendly!
>> ? After changing datatype (Birth_Date and End_Date) properties to
>> date time (and their widget). All of them (sheets 1,2,3,4) make
>> pellet create the exception shown in doc file.
>> ? When I change back Birth_Date and End_Date properties Range of the
>> ontology from ?date time? to ?string?, it is consistent!
>> Of course this is not a solution because when there will be numerous
>> datatype properties to change, this will be impossible.
>> Is there a suggestion?
>> I thought that making the right format in xcel, since ?XlSQL-Y8 can
>> deal also with correctly formatted date and time fields!?, may
>> provide a solution. Since it will be imported with the right xml
>> format? Is there a way you could guide me to make the right format so
>> that datamaster would recognise it as date time and not as
>> string?(original data in csv file)
>> Please find attached: the excel file, the exception doc and the
>> original data csv file.
>> Thank you very much in advance,
>> protege-owl mailing list
>> protege-owl at lists.stanford.edu
>> Instructions for unsubscribing:
> protege-owl mailing list
> protege-owl at lists.stanford.edu
> Instructions for unsubscribing: http://protege.stanford.edu/doc/faq.html#01a.03
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 2042 bytes
Desc: not available
Url : http://mailman.stanford.edu/pipermail/protege-owl/attachments/20090323/46f9eb50/attachment-0001.csv
More information about the protege-owl