CODEWITHSUNDEEP
db2db2-luw
Trex
Trex

Reputation: 15

Load period-separated text file into db2

I need to load an entire text file (contains only ASCII text) into the database (DB2 Express ed.). The table has only two columns EXAMPLE_TABLE (ID, TEXT). The ID column is PK, with auto generated data, whereas the text is VARCHAR(50).

Now I need to use the load/import utility to save each sentence in the text face into the EXAMPLE_TABLE, that is, we have a row for each sentence. The row-id should be auto generated, but that is already specified in table creation time. The import utility should consider the period '.' as delimiter (otherwise I don't know how to extract sentences).

How can this be done in DB2?

Thanks in advance!

Upvotes: 0

Views: 717

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

When using delimited files, the standard DB2 import and load utilities do not have the ability to specify a row record terminator. The LF character (or CRLF on Windows) is the only record terminator you can use.

So, you would need to pre-process your file (to either replace each period (.) with a newline or insert a newline after each period) before you can use import or load, resulting in a file with each sentence on a separate line.

You can do this with tr:

cat file | tr '.' '\n' > file.load
db2 "import from file.load of del insert into example_table (text)"

Keep in mind that you will probably also need to account for spaces after the period, so you don't end up with leading spaces at the beginning of each "sentence" in your table, and you may also want to account for additional whitespace (i.e. empty lines between each paragraph).

Upvotes: 1

Related Questions