How to load plain text file in teradata varbyte column using multiload utility?

Question

I have plain text (test.txt) file as follows:

335262416f8b09b7a64bd52ac05a4bf1,2083e7c817f6a6cf50047ab75dc6cef2
ccbd23e11bd592c0cdd50216a56f6522,9655676953354901390972dbf825d8a4
b0659cd7a10369142365b2e9400dc308,67b246f96011f42844cb738c5dbfb674
fbbcbb6082ddf2e9a61023622292581e,a00db01bd320026f5d8e6780826eb3b0
a61ae8a7eacb95c05fe1a91a0da4eddf,838a9a2ac37becea7274c7e824c2b924
7fac7e224f8af6595a8b751d8b94e4e2,90a4b915adb2b72354ef5644943ed518
1ca801f1e72bf45ea0fb132d10fd0c60,02d3838e229fa8570969fb0ad2ec4b02
77eca7430aefb28eb52068a03383f80a,164a52c803ab86ed6e1a37852b882db9
1454ce0a395e8fe7a0139ec4a959d6ea,c7f0d5b1fdbe91b46cf3c70e1e87b62d
715b37ef39005289576bb8690354ee19,b806e4833ca5bd409f88e75b962c7746

I am trying to load this data in teradata table with multiload utility. Target column datatype is varbyte. and mload script is as follow:

.LOGTABLE dbname.log_table;

.LOGON username,password;

DATABASE dbname;

CREATE MULTISET TABLE dbname.Table_1 (id VARBYTE(50),
                name VARBYTE(50));

.BEGIN IMPORT MLOAD  TABLES Table_1  WORKTABLES WT_Table  ERRORTABLES ET_Table UV_Table;

.LAYOUT InputLayOut;

.FIELD in_id * VARBYTE(100);
.FIELD in_name * VARBYTE(100);

.DML LABEL INSERTS IGNORE DUPLICATE INSERT ROWS;

INSERT INTO dbname.Table_1 (id,name) VALUES (
                :in_id,:in_name);

.IMPORT INFILE test.txt FORMAT VARTEXT ',' LAYOUT InputLayOut APPLY INSERTS;

.END MLOAD;

.LOGOFF;
~

After running this script successfully values in table are as follow:

id                                                                                                    namename
----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------
3766616337653232346638616636353935613862373531643862393465346532                                      3930613462393135616462326237323335346566353634343934336564353138
3333353236323431366638623039623761363462643532616330356134626631                                      3230383365376338313766366136636635303034376162373564633663656632
3163613830316631653732626634356561306662313332643130666430633630                                      3032643338333865323239666138353730393639666230616432656334623032
3731356233376566333930303532383935373662623836393033353465653139                                      6238303665343833336361356264343039663838653735623936326337373436
6662626362623630383264646632653961363130323336323232393235383165                                      6130306462303162643332303032366635643865363738303832366562336230
3737656361373433306165666232386562353230363861303333383366383061                                      3136346135326338303361623836656436653161333738353262383832646239
6136316165386137656163623935633035666531613931613064613465646466                                      3833386139613261633337626563656137323734633765383234633262393234
3134353463653061333935653866653761303133396563346139353964366561                                      6337663064356231666462653931623436636633633730653165383762363264
6363626432336531316264353932633063646435303231366135366636353232                                      3936353536373639353333353439303133393039373264626638323564386134
6230363539636437613130333639313432333635623265393430306463333038                                      3637623234366639363031316634323834346362373338633564626662363734

I understand there is implicit data type conversion is happening, but how to tackle it. I am trying to get same string as in input file in table. Thanks for the help.

BYTE / VARBYTE fields are displayed as hex values when output as character strings. In other words, there is no conversion happening on input here, but when you query and (perhaps implicitly, due to field mode) say the output should be character then a conversion happens (similar to what is done with internal numeric or date/time values). Is the real question how to treat the input character string as hex representation and store the corresponding byte values? — Fred, Mar 09 '20 at 15:37

score 0 · Answer 1 · answered Mar 09 '20 at 16:45

Assuming you are trying to convert external hex representation to corresponding internal byte values, use

SUBSTRING(TO_BYTES('00'||:in_id,'base16') FROM 2)

TO_BYTES will add a leading zero byte if the high-order bit of the input value is set. Unconditionally adding and then removing a leading zero byte is a simple way to ensure the final result will never have this extra byte in front.

How to load plain text file in teradata varbyte column using multiload utility?

1 Answers1