0

What is the Derby SQL command to load a tab delimited text file into a blank table?

  1. Text file is tab delimited (column)
  2. I am working in Eclipse's DATA SOURCE EXPLORER with an embedded Derby Database
  3. I can load the data (this works as intended) manually by connecting to the database, running the .sql file, navigating to the table it creates, selecting load data, setting the location of the .txt file, and specifying the Column delimiter as Tab.

Here is the .sql file:

DROP TABLE app.TC11_cert;

 CREATE TABLE app.TC11_cert (
  ID int not null,
  producttitle VARCHAR(600),
  product VARCHAR(600),
  platform VARCHAR(600),
  row1 VARCHAR(600),
  row2 VARCHAR(600),
  row3 VARCHAR(600), 
  row4 VARCHAR(600),
  row5 VARCHAR(600),
  row6 VARCHAR(600),
  row7 VARCHAR(600),
  row8 VARCHAR(600),
  row9 VARCHAR(600),
  row10 VARCHAR(600),
  row11 VARCHAR(600),
  row12 VARCHAR(600),
  row13 VARCHAR(600),
  row14 VARCHAR(600),
  row15 VARCHAR(600),
  row16 VARCHAR(600)
  );

As you can see, the file deletes the old table and creates a new blank one.

Ideally, I'd like to place this command directly after the create TABLE section

Here are 4 commands I have tried with no success:

/*
    CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE 
    ('APP','TC11_CERT','MasterDatabase.txt', '', '\t', null, 0);

LOAD DATA INFILE '\MasterDatabase.txt' INTO TABLE TC11_CERT
FIELDS TERMINATED BY '\t' ESCAPED BY '\b';

CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
   (null,'app.TC11_cert','\MasterDatabase.txt','\b','\t',null,0);

   CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
    (null, 'TC11_CERT', 'ID', '1', '\MasterDatabase.txt', '\b','\t','UTF-8',  0);

 */

If you have any questions please ask :)

EDIT 3/4/2019: in the meantime I am going to work on using AutoIt in a virtual machine to see if I can automate this process that way (obviously I would prefer to code this instead)

JakeTM
  • 1
  • 3
  • 1
    http://db.apache.org/derby/docs/10.5/tools/ctoolsimport16245.html – Usagi Miyamoto Mar 04 '19 at 16:32
  • Perhaps you could amplify on "tried with no success": did you get an exception? (if so, what exception?) did you get any data loaded? (if so, what data got loaded?). Also, it doesn't seem like your table contains blobs or clobs, so I'm not sure why you are trying to use the "lobs" versions of the import procedures? – Bryan Pendleton Mar 04 '19 at 17:29

0 Answers0