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
