FastExport script and output delimiters

Tools & Utilities
Teradata Employee

FastExport script and output delimiters

Howdy folks,

I just had a query from a user where he is using FastExport and wants to state what delimiter the output file should use. I have had a look on-line, consulted the FastExport user guide, and it appears that you cannot specify this, like you can in FastLoad.

Is the only way to achieve this to use an AXSMOD or INMOD procedure?

Many thanks!

Andrew Livingston
9 REPLIES

Re: FastExport script and output delimiters

Here is what I've done, just add the delimiter in my query, not elegant but it's work!

select first_col(char(0)), ';' (char(1)) etc... from my_table

Teradata Employee

Re: FastExport script and output delimiters

Zebulon,

Interestingly enough, that is exactly what one of the DBA suggested.

It is a much simpler method that writing an OUTMOD function. The only drawback I can see is that it still inserts the tab, but as long as the application you are importing to is set to recognise another delimiter it should be fine. Also, depending on the size of the txt file, you can always do a bulk find/replace.

Many thanks for your post!

Andrew
Enthusiast

Re: FastExport script and output delimiters

I typically do the following, in order to eliminate the TABs:

select CAST(first_col AS CHAR(N)) || ';' || CAST (second_col AS CHAR(N)) || etc... from my_table

where "||" is the concatenation operator.

Teradata Employee

Re: FastExport script and output delimiters

Fabio,

Excellent! I don't think I would ever have even thought of that. Looking at the code, yes, that would work a treat.

Many thanks for your suggestion!

Andrew
Highlighted
Enthusiast

Re: FastExport script and output delimiters

select CAST(first_col AS CHAR(N)) || ';' || CAST (second_col AS CHAR(N)) || etc... from my_table

We can also do like this

select CAST(first_col AS CHAR(N))
,cast(';' as char(1))
,CAST (second_col AS CHAR(N))
, etc...
from my_table
Teradata Employee

Re: FastExport script and output delimiters

Sushant!

Arguably a better approach, as this would give you absolute control over the placement not only of delimiters, but of the actual data as well. Using this FE script would help write the FastLoad script.

Thanks!

Andrew
Enthusiast

Re: FastExport script and output delimiters

Andrew & all,

I am trying to get a pipe delimited file using a similar query as suggested above using fastexport.  However i am getting some tabs or spaces before each row in the file generated.Here is the query i am using . 

select

TRIM(cast(stid as varchar(15)))||'|'||

TRIM(cast(stname as varchar(15)))||'|'||

TRIM(cast(stclass as varchar(15)))||'|'||

TRIM(cast(stmarks as varchar(15))) from school_record;

Can you please suggest on how to over come the spaces/tabs in front of each row in file generated.

Thanks,

Prashant

Junior Contributor

Re: FastExport script and output delimiters

Hi Prashant,

this has been discussed several times, the best solution is to switch to TPT, the latest versions can directly export delimited data without need for TRIM/concat.

Otherwise the "tabs or spaces" are a two-byte word indication the lebgth of the VarChar, you can get rid of it using an OutMod or a Unix tool like awk to simply strip of the first two bytes

Enthusiast

Re: FastExport script and output delimiters

Yes thats true Dieter. I was also believing the same way. Thanks for confirming on it.