All Forums UDA
MikeA 1 post Joined 04/06
11 Apr 2006
Mload delimited file with delimiter embedded in field

HiTrying 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?ThanksMike

11 Apr 2006

Mike,If the file is not huge I would do the follwing stepsif 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

Pervez Ahmed 19 posts Joined 05/06
03 May 2006

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

j355ga 100 posts Joined 12/05
17 May 2006

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;

Jeff

You must sign in to leave a comment.