You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Tim Troup <Ti...@ed.ac.uk> on 2007/01/12 13:07:24 UTC
Data found after the stop delimiter
Hi,
I am trying to load a tab delimited text file into a database table
in derby 10.2.1.6 using the following command in ij:
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'pg_synonyms','/Users/troup/
data/biomint/pg_synonyms',' ',null,null,0);
However I keep getting the following error:
ERROR 38000: The exception 'java.sql.SQLException: Data found on line
8,238,195 for column 2 after the stop delimiter.' was thrown while
evaluating an expression. SQLSTATE: XIE03: Data found on line 8238195
for column 2 after the stop delimiter.
What is the default stop delimiter?
I have analysed the file contents and this line has double quotes
round a word in the column two data:
"Gu" protein
no other line preceding this one has double quotes round any word so
this made me suspect this was what is causing the load error.
Any advice would be greatly appreciated.
Best regards,
Tim Troup
Re: Data found after the stop delimiter
Posted by Suresh Thalamati <su...@gmail.com>.
Jean T. Anderson wrote:
> Tim Troup wrote:
>
>>Hi,
>>
>>I am trying to load a tab delimited text file into a database table in
>>derby 10.2.1.6 using the following command in ij:
>>
>>CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'pg_synonyms','/Users/troup/
>>data/biomint/pg_synonyms',' ',null,null,0);
>>
>>However I keep getting the following error:
>>
>>ERROR 38000: The exception 'java.sql.SQLException: Data found on line
>>8,238,195 for column 2 after the stop delimiter.' was thrown while
>>evaluating an expression. SQLSTATE: XIE03: Data found on line 8238195
>>for column 2 after the stop delimiter.
>>
>>What is the default stop delimiter?
>
>
> Double quotes are the default delimiter to enclose a character string in
> a load file [1].
>
>
>>I have analysed the file contents and this line has double quotes round
>>a word in the column two data:
>>
>>"Gu" protein
>>
>>no other line preceding this one has double quotes round any word so
>>this made me suspect this was what is causing the load error.
>
>
> One option might be to specify the characterDelimiter to be a character
> that isn't anywhere else in your data, for example @. If I remember
> right, then your entry in the load file that is currently this:
>
> <tab>"Gu" protein<tab>
>
> would look like this:
>
> <tab>@"Gu" protein@<tab>
>
> -jean
>
>
> [1] http://db.apache.org/derby/docs/10.2/tools/rtoolsimport64241.html
>
If you don't know in advance what are the characters in the character
data will be, then another approach that might work is if you can make
the input data into a double-delimiter format. i.e if the character
data delimiter is a " , them make all the " inside the data to be two
double quotes. For example to import Gu " protein into a table, data
in the input file should be: "Gu "" protein"
you can find more info about this approach at :
http://db.apache.org/derby/docs/10.2/tools/ctoolsimportdefaultformat.html
hope that helps
-suresh
Re: Data found after the stop delimiter
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Tim Troup wrote:
> Hi,
>
> I am trying to load a tab delimited text file into a database table in
> derby 10.2.1.6 using the following command in ij:
>
> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'pg_synonyms','/Users/troup/
> data/biomint/pg_synonyms',' ',null,null,0);
>
> However I keep getting the following error:
>
> ERROR 38000: The exception 'java.sql.SQLException: Data found on line
> 8,238,195 for column 2 after the stop delimiter.' was thrown while
> evaluating an expression. SQLSTATE: XIE03: Data found on line 8238195
> for column 2 after the stop delimiter.
>
> What is the default stop delimiter?
Double quotes are the default delimiter to enclose a character string in
a load file [1].
> I have analysed the file contents and this line has double quotes round
> a word in the column two data:
>
> "Gu" protein
>
> no other line preceding this one has double quotes round any word so
> this made me suspect this was what is causing the load error.
One option might be to specify the characterDelimiter to be a character
that isn't anywhere else in your data, for example @. If I remember
right, then your entry in the load file that is currently this:
<tab>"Gu" protein<tab>
would look like this:
<tab>@"Gu" protein@<tab>
-jean
[1] http://db.apache.org/derby/docs/10.2/tools/rtoolsimport64241.html
Re: Data found after the stop delimiter
Posted by Tim Troup <Ti...@ed.ac.uk>.
Hi,
Thanks I will give your suggestion a try.
Do you propose I edit the tab delimited text file and escape all
double quote characters by inserting a backslash prior to trying to
load the data into the database?
Cheers, Tim
On 12 Jan 2007, at 14:22, Paul J DeCoursey wrote:
> I believe that Quotes are used to define a field that has a
> delimeter in it. This is the case with CSV files. In CSV you can
> escape a quote using a slash like this \". Give that a try, I have
> not worked much with Tab Delimited files so I can't say for sure
> without pouring over the source.
>
> Tim Troup wrote:
>> Hi,
>>
>> I removed the first double quote character and the data got
>> inserted without error.
>> From this experimentation I assume the double quote character is
>> the default stop delimiter?
>>
>> Any suggestions as to the best way to get data such as this inserted?
>>
>> I could parse the tab delimited data and remove all double quote
>> characters then somehow insert them again once they are in the
>> database but this seems rather clumsy and error prone.
>>
>> Any advice would be greatly appreciated.
>>
>> Cheers, Tim
>>
>> On 12 Jan 2007, at 12:07, Tim Troup wrote:
>>
>>> Hi,
>>>
>>> I am trying to load a tab delimited text file into a database
>>> table in derby 10.2.1.6 using the following command in ij:
>>>
>>> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'pg_synonyms','/Users/
>>> troup/data/biomint/pg_synonyms',' ',null,null,0);
>>>
>>> However I keep getting the following error:
>>>
>>> ERROR 38000: The exception 'java.sql.SQLException: Data found on
>>> line 8,238,195 for column 2 after the stop delimiter.' was thrown
>>> while evaluating an expression. SQLSTATE: XIE03: Data found on
>>> line 8238195 for column 2 after the stop delimiter.
>>>
>>> What is the default stop delimiter?
>>>
>>> I have analysed the file contents and this line has double quotes
>>> round a word in the column two data:
>>>
>>> "Gu" protein
>>>
>>> no other line preceding this one has double quotes round any word
>>> so this made me suspect this was what is causing the load error.
>>>
>>> Any advice would be greatly appreciated.
>>>
>>> Best regards,
>>>
>>> Tim Troup
>>>
>>
>>
>
>
Re: Data found after the stop delimiter
Posted by Paul J DeCoursey <pa...@decoursey.net>.
I believe that Quotes are used to define a field that has a delimeter in
it. This is the case with CSV files. In CSV you can escape a quote
using a slash like this \". Give that a try, I have not worked much
with Tab Delimited files so I can't say for sure without pouring over
the source.
Tim Troup wrote:
> Hi,
>
> I removed the first double quote character and the data got inserted
> without error.
> From this experimentation I assume the double quote character is the
> default stop delimiter?
>
> Any suggestions as to the best way to get data such as this inserted?
>
> I could parse the tab delimited data and remove all double quote
> characters then somehow insert them again once they are in the
> database but this seems rather clumsy and error prone.
>
> Any advice would be greatly appreciated.
>
> Cheers, Tim
>
> On 12 Jan 2007, at 12:07, Tim Troup wrote:
>
>> Hi,
>>
>> I am trying to load a tab delimited text file into a database table
>> in derby 10.2.1.6 using the following command in ij:
>>
>> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
>> (null,'pg_synonyms','/Users/troup/data/biomint/pg_synonyms','
>> ',null,null,0);
>>
>> However I keep getting the following error:
>>
>> ERROR 38000: The exception 'java.sql.SQLException: Data found on line
>> 8,238,195 for column 2 after the stop delimiter.' was thrown while
>> evaluating an expression. SQLSTATE: XIE03: Data found on line 8238195
>> for column 2 after the stop delimiter.
>>
>> What is the default stop delimiter?
>>
>> I have analysed the file contents and this line has double quotes
>> round a word in the column two data:
>>
>> "Gu" protein
>>
>> no other line preceding this one has double quotes round any word so
>> this made me suspect this was what is causing the load error.
>>
>> Any advice would be greatly appreciated.
>>
>> Best regards,
>>
>> Tim Troup
>>
>
>
Re: Data found after the stop delimiter
Posted by Tim Troup <Ti...@ed.ac.uk>.
Hi,
I removed the first double quote character and the data got inserted
without error.
From this experimentation I assume the double quote character is the
default stop delimiter?
Any suggestions as to the best way to get data such as this inserted?
I could parse the tab delimited data and remove all double quote
characters then somehow insert them again once they are in the
database but this seems rather clumsy and error prone.
Any advice would be greatly appreciated.
Cheers, Tim
On 12 Jan 2007, at 12:07, Tim Troup wrote:
> Hi,
>
> I am trying to load a tab delimited text file into a database table
> in derby 10.2.1.6 using the following command in ij:
>
> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'pg_synonyms','/Users/
> troup/data/biomint/pg_synonyms',' ',null,null,0);
>
> However I keep getting the following error:
>
> ERROR 38000: The exception 'java.sql.SQLException: Data found on
> line 8,238,195 for column 2 after the stop delimiter.' was thrown
> while evaluating an expression. SQLSTATE: XIE03: Data found on line
> 8238195 for column 2 after the stop delimiter.
>
> What is the default stop delimiter?
>
> I have analysed the file contents and this line has double quotes
> round a word in the column two data:
>
> "Gu" protein
>
> no other line preceding this one has double quotes round any word
> so this made me suspect this was what is causing the load error.
>
> Any advice would be greatly appreciated.
>
> Best regards,
>
> Tim Troup
>