The TPT documentation explains the different record formats we support. Look in the Reference Manual in Chapter 4 for the Data Connector operator (that operator is the file reader).
You will probably be interested in the "TEXT" format. For "TEXT" format, the input data must be in character format, and so the schema must be made up of all fixed length CHAR data types. If the target table on Teradata has non-CHAR columns, Teradata will do the conversion.
If you also read the manuals, you will see that the keyword OFFSET is not part of your language. Please just use the syntax we provide.
You will also need to look at our data types. We do not have "DATE". That is because Teradata stores date values internally as integers. But we can also support the ANSI style date.
So, we have ANSIDATE and INTDATE.
What you are trying to do will probably not work for Teradata date fields.
My bad,
I was unfortunate to create this example...
Actually, I had not created the script yet, I was studying how I could do this.
I put the OFFSET in the schema only to all have a sense of what I expect to do.
Anyway,
with your tip I managed to do the load, I did not know it was so simple :)
My sample data:
<2334ANA MARIA ><20111212 102030> <3435JACKIE CHAN ><20111212 102031> <4536MARCELA SILVA ><20111212 102032> <5637RITA BARBOSA ><20111212 102033> <6738LARS ULRICH ><20111212 102034> <7839FELIPE MASSA ><20111212 102035>
My test script above:
DEFINE JOB CARREGA_TESTE_TPT_TAM_FIX
DESCRIPTION 'POPULA TABELA DE TESTE'
(
DEFINE SCHEMA TESTE_TPT_TAM_FIX_SCHEMA
DESCRIPTION 'SCHEMA DA TABELA DE TESTE'
(
DISPOSAL1 CHAR(1)
, CPID CHAR(4)
, CNAME CHAR(15)
, DISPOSAL2 CHAR(2)
, CData CHAR(15)
, DISPOSAL3 CHAR(1)
);
DEFINE OPERATOR FILE_READER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA TESTE_TPT_TAM_FIX_SCHEMA
ATTRIBUTES
(
VARCHAR DirectoryPath = 'D:\Programacao\BV-R2\detraf\validos',
VARCHAR FileName = 'dados.txt',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Read',
VARCHAR Format = 'TEXT'
);
DEFINE OPERATOR CARREGA_DADOS()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE UPDATE
SCHEMA TESTE_TPT_TAM_FIX_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId = 'MyCon',
VARCHAR UserName = 'MyUser',
VARCHAR UserPassword = 'MyPassword',
VARCHAR TargetTable = 'DCT_TEMP.TESTE_TPT_TAM_FIX',
VARCHAR LogTable = 'DCT_TEMP.TESTE_TPT_TAM_FIX_LG',
VARCHAR ErrorTable1 = 'DCT_TEMP.TESTE_TPT_TAM_FIX_ET',
VARCHAR ErrorTable2 = 'DCT_TEMP.TESTE_TPT_TAM_FIX_UV',
VARCHAR WorkTable = 'DCT_TEMP.TESTE_TPT_TAM_FIX_WT'
);
STEP CARREGA_TESTE
(
APPLY
(
'INSERT INTO DCT_TEMP.TESTE_TPT_TAM_FIX (
CPID
, CNAME
, CData
) VALUES (
: CPID
, : CNAME
, : CData (TIMESTAMP(0), FORMAT ''YYYYMMDDBHHMISS'')
) ;'
)
TO OPERATOR (CARREGA_DADOS () )
SELECT CPID
, CNAME
, CData
FROM OPERATOR
(
FILE_READER() [1]
);
);
);
"DISPOSALs" in my Schema is necessary to match the other fields, due to "<e>" I'll ignore.
Sorry my adapted "Google English"
Thanks for all.
Hi all,
I have to load some data using Teradata Parallel Transporter...
I have a record like:
1234ANA MARIA 20111209
I need to load something like:
PID: 1234
NAME: ANA MARIA
DATE: 2011-11-09
I expected to do something like:
DEFINE SCHEMA T001_FIELD_DEFINITION_SCHEMA
DESCRIPTION 'SCHEMA TESTE'
(
PID OFFSET(1) LENGTH(4)
, NAME OFFSET(4) LENGTH(20)
, DATE OFFSET(20) LENGTH(28)
);
In my DATACONNECTOR PRODUCER operator I'm trying use:
VARCHAR Format='Formatted'
That's rigth?
I found nothing that describes how to do it anywhere
or I do not know how to search...
I do not really understand the attribute 'Format' in the TPT documentation...
Thanks in advance.