All Forums UDA
sparan 19 posts Joined 05/06
31 Aug 2006
Importing delimiter text file!!!!!!!!

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

leo.issac 184 posts Joined 07/06
14 Sep 2006

From the BTEQ reference manual I found the following syntax. I hope it works..IMPORT VARTEXT 'C' FILE = FILENAMEUSING (: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

GogulM 32 posts Joined 08/06
18 Sep 2006

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 .

~Gogul

klnsreenivas 12 posts Joined 12/11
26 Nov 2012

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?
 

K.L.N.Sreenivas

klnsreenivas 12 posts Joined 12/11
26 Nov 2012

If i am using like the below, i got error
.IMPORT VARTEXT ','  FILE = FILENAME, Skip=2;
Error: No such file or directory

K.L.N.Sreenivas

Harpreet Singh 101 posts Joined 10/11
28 Nov 2012

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

klnsreenivas 12 posts Joined 12/11
05 Dec 2012

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

K.L.N.Sreenivas

klnsreenivas 12 posts Joined 12/11
05 Dec 2012

Hi All,
Please guide me how to handle this " text qualifier

K.L.N.Sreenivas

MA255022 1 post Joined 07/11
22 Jan 2013

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.

Adeel Chaudhry 773 posts Joined 04/08
02 Mar 2013

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!

 

-- If you are stuck at something .... consider it an opportunity to think anew.

ramesh_td 9 posts Joined 01/14
17 Jun 2014
.IMPORT VARTEXT ','  FILE=D:\ABC.txt;

from the above example it is clear that the file abc.txt has , as delimited character. how can I mention tab as delimited? can anyone please answer for this question?
 
 

M.Saeed Khurram 544 posts Joined 09/12
17 Jun 2014

Ramesh,
You can use TAB as {Tab}

.IMPORT VARTEXT '{Tab}'  FILE=D:\ABC.txt;

 

Khurram

You must sign in to leave a comment.