Mload delimited file with delimiter embedded in field

UDA
N/A

Mload delimited file with delimiter embedded in field

Hi

Trying to load a (tab) delimited file into Teradata (V2R5) using Mload. The supplier of the file has stated that any character may appear in the text fields, including the delimiter. In this case any field containing the delimiter character(s) will be enclosed in double quotes.

Does anyone know whether Mload can deal with this without resorting to an Inmod?

Thanks

Mike
3 REPLIES
Enthusiast

Re: Mload delimited file with delimiter embedded in field

Mike,
If the file is not huge I would do the follwing steps

if you are on a unix env.
assume the delimiter is comma , and all the actual text commas are specified as ","

replace all the "," s with a char that is not available in the text file assume tilde ~
then replace all the commas (the actual delimiters) with a pipe |
then replace all the ~ 's with ","

By doing this we are creating pipe delimited file.
But make sure we dont have any pipes in our data to start with.
All you have to do is find a char that cannot exist in the file and use that as delimiter.
In some cases we had perl scripts that did all these modifications.
(well if you tried all these steps earlier and if that does not work then inmod is the way i guess)

i am not sure if an inmod will slow the process.
Lets hear from the GURUS

Enthusiast

Re: Mload delimited file with delimiter embedded in field

Can pipe delemited file be read by the MLOAD utility. If you can please send the sample .LAYOUT and FIELD definitions.

Thanks
Teradata Employee

Re: Mload delimited file with delimiter embedded in field

Example of MLOAD using pipe delimiters

.LOGTABLE ML_FOO;

.LOGON .....

.BEGIN IMPORT MLOAD
SESSIONS 4
TABLES FOO
WORKTABLES WT_FOO
ERRORTABLES ET_FOO
UV_FOO
ERRLIMIT 100;

.LAYOUT DATAIN_LAYOUT;
.FIELD FOO_ID * VARCHAR(12);
.FIELD FOO_DT * VARCHAR(10);
.FIELD FOO_NAME * VARCHAR(100);

.DML LABEL INSERT_DML;

INSERT INTO FOO
(
:FOO_ID,
:FOO_DT,
:FOO_NAME
);

.IMPORT INFILE foo.dat
FORMAT VARTEXT
LAYOUT DATAIN_LAYOUT
APPLY INSERT_DML;

.END MLOAD;

.LOGOFF &SYSUVCNT + &SYSRJCTCNT + &SYSETCNT + &SYSRC;