|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/18/2006 4:01:00 AM
Posts: 1,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 12:54:51 PM
Posts: 116,
Visits: 20
|
|
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
Feroz Shaik
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/24/2008 2:58:55 AM
Posts: 19,
Visits: 6
|
|
Can pipe delemited file be read by the MLOAD utility. If you can please send the sample .LAYOUT and FIELD definitions.
Thanks
Pervez Ahmed
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/25/2008 1:20:39 PM
Posts: 91,
Visits: 119
|
|
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;
|
|
|
|