Importing delimiter text file!!!!!!!!

UDA
Enthusiast

Importing delimiter text file!!!!!!!!

can any one please give me a sample script to load a table using bteq import?

Thanks in adv.
11 REPLIES
Enthusiast

Re: Importing delimiter text file!!!!!!!!

From the BTEQ reference manual I found the following syntax. I hope it works.

.IMPORT VARTEXT 'C' FILE = FILENAME

USING (:FIELD1,:FIELD2,....,:FIELDn)
INSERT INTO TABLENAME (COLUMN LIST)
VALUES
(:FIELD1,...,:FIELDn);

Refer BTEQ reference Manual for more information.I hope there are some restrictions and implication regarding the Vartext
Enthusiast

Re: Importing delimiter text file!!!!!!!!

Check this sample script this is for delimiter file....
sample script:

.LOGON userdsn/user,user;
.IMPORT vartext '|' file= E:\delimitfile\emp.txt
.REPEAT *
USING FNAME (VARCHAR(20)),
LNAME (VARCHAR(20)),
EMPNO (VARCHAR(5)),
DEPTNO (VARCHAR(2)),
JOB (VARCHAR(20)),
SAL (VARCHAR(9)),
HIREDATE (VARCHAR(11))

INSERT INTO db.EMP(:fname,
:lname,
:empno,
:deptno,
:job,
:sal,
:hiredate);
.QUIT;

Vartext will deal with varchar datatypes only
repeat * is used to fetch all the records in the file .
Enthusiast

Re: Importing delimiter text file!!!!!!!!

Can we use SKIP commend in importing delimiter file like

.IMPORT VARTEXT ','  FILE = FILENAME, Skip=2;

I need to skip 3 lines while importing, is there any other procedure if skip won`t work?

Enthusiast

Re: Importing delimiter text file!!!!!!!!

If i am using like the below, i got error

.IMPORT VARTEXT ','  FILE = FILENAME, Skip=2;

Error: No such file or directory

Enthusiast

Re: Importing delimiter text file!!!!!!!!

error you are recieving because filename is going as FILENAME, Skip=2;

Use quotes for filename when doing skip .

try

.IMPORT VARTEXT ','  FILE = 'FILENAME', Skip=2;

HTH

Enthusiast

Re: Importing delimiter text file!!!!!!!!

Thanks Harpreet, that one i got it.

BUt i am facing another problem like

my file data is like

"12345","ABCD","STU,GHj","01/01/2012"

Here

by using VARTEXT ',' we can use this Delimited file but

this  " is Text Qualifier. How to handle this while import

Enthusiast

Re: Importing delimiter text file!!!!!!!!

Hi All,

Please guide me how to handle this " text qualifier

Teradata Employee

Re: Importing delimiter text file!!!!!!!!

I'm not aware of any text qualifier in the BTEQ utility, you may need to substitue the delimiters within data with something else.

On a UNIX/Linux machine, I would do this like:

.OS mv fileName fileName.tmp && sed ':loop s:"\([0-9a-zA-Z/]\+\)|:"\1^:g; t loop' fileName.tmp > fileName

This will replace the delimiters within double quotes with a ^ sign. Google "man 7 regex" and "man sed" for detailed explanations.

The .OS command in BTEQ runs an operating system command from within the BTEQ utility. On Windows, you can write a fragment of C code for this and call the executable with .OS command.

Teradata Employee

Re: Importing delimiter text file!!!!!!!!

This is a typical case of complex delimited file scenario:

If you take " as your delimiter, you will have following columns:

null

12345

,

ABCD

,

STU,GHj

,

01/01/2012

null

and , will also be incorrect .... hence .... you need to come up with a pre-processing script to:

- remove " in case its at the start of row, or at the end just before new-line character

- replace "," with may be || or $$ (any unique character which is not supposed to come in data .... " and , are wrong options)

Only then you will be able to process such file.

HTH!