import txt file

[Anonymous]

I have a txt file this with this line:

18140030015-201607221410378132029382-0246RECIFE                    RCE RCE 0110000000032666300E      000000000000000000

Must be this:

22/07/2016 14:10:37  8132029382 24,6 8140030015 3,26663

I tried this:

  LOAD

mid(@1,33,4) as RAMAL,
mid(@1,13,8) as DATA,
mid(@1,13,4) as ANO,
mid(@1,17,2) as MES,
mid(@1,19,2) as DIA,
mid(@1,39,3) as TMP_LIGACAO,
mid(@1,87,6) as VLR_S_IMPOSTO,
mid(@1,21,6) as HORA_LIGACAO,
mid(@1,2,10) as FONE_DESTINO,

@1
FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

[fkeuroglian]

And the result?

Would be easier if you pass the txt file to help you

Fernando K.

[Anonymous]

the result I need is:

22/07/2016 14:10:37  8132029382 24,6 8140030015 3,26663

[Anonymous]

It does not show the option to include the .txt file but it has the first line of interaction.

18140030015-201607221410378132029382-0246RECIFE                    RCE RCE 0110000000032666300E      000000000000000000

[robert_mika]

T1:

load * inline [

@1

18140030015-201607221410378132029382-0246RECIFE                    RCE RCE 0110000000032666300E      0000000000000000000];

load

Date(Date#(mid(@1,13,8),'YYYYMMDD'),'DD-MM-YYYY') as DATA,

num(mid(@1,87,6),'0,00000') as RAMAL,

mid(@1,27,10) as TMP_LIGACAO,

num(mid(@1,39,3),'0,00') as VLR_S_IMPOSTO,

TIME(time#(mid(@1,21,6),'hhmmss'),'hh:mm:ss') as HORA_LIGACAO,

mid(@1,2,10) as FONE_DESTINO,

@1

Resident T1;

change name of the fields(my Spanish?) is not so good...

[johnca]

Another way is with preceding loads...

Result is your string:

22/07/2016 14:10:37 8132029382 2,46 8140030015 3,26663

DATA:

Load *,

  DATA & ' ' &

  HORA_LIGACAO & ' ' &

  first_number & ' ' &

  TMP_LIGACAO & ' ' &

  FONE_DESTINO & ' ' &

  VLR_S_IMPOSTO as Result;

Load

  mid(@1,33,4) as RAMAL,

  Date(Date#(mid(@1,13,8),'YYYYMMDD'),'DD/MM/YYYY') as DATA,

  mid(@1,13,4) as ANO,

  mid(@1,17,2) as MES,

  mid(@1,19,2) as DIA,

  Num(mid(@1,39,3),'0,00') as TMP_LIGACAO,

  Num(mid(@1,87,6),'0,00000') as VLR_S_IMPOSTO,

  Time(Time#(mid(@1,21,6),'hhmmss'),'hh:mm:ss') as HORA_LIGACAO,

  mid(@1,27,10) as first_number,

  mid(@1,2,10) as FONE_DESTINO;

Load * Inline [@1

18140030015-201607221410378132029382-0246RECIFE                    RCE RCE 0110000000032666300E      000000000000000000

];

HTH,

John

[Anonymous]

Thank you all, it worked.

[johnca]

Glad it worked for you. Please close the thread by marking one answer as Correct.