I have several TXT files and would like to open a table by downloading all the columns and once they have been replicated in a new column where the key is the name of the file.I need to get the due date for each file.
If I do a left join, it works, but it will have two tables and I want to consolidate all the content into just one table.When I use only LOAD, it brings information only from the first file it finds.
Can someone help me?
[PRELOAD1]:
LOAD
FileBaseName() as PK_FileBaseName,
[A],
[B],
[C]
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
;
/////////////////////////////////////////////////////////////////////////////////////////////
[PRELOAD2]:
LOAD DISTINCT
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
;
I tried to use something described below in the PRELOAD2 table, but the result allowed the same.
vPath = 'lib://AMIL/';
For each File in FileList('$(vPath)/DM*.txt')
Left join
[PRELOAD2]:
LOAD DISTINCT FileBaseName() as PK_FileBaseName, date_pay, date_shipping FROM $(File) (txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof) Where WildMatch([@3],'Emissão*') ;
Next File;
Expected result:
the problem is just loading all records into the dates (date_pay | date_shipping)
PK_FileBaseName | A | B | C | date_pay | date_shipping 1 2 3 4 5
PreLoad1:
Load
[Código],
[Beneficiário],
[Matrícula],
[CPF],
[Plano],
[Tipo],
[Idade],
if(IsNull([Dependência]) or [Dependência]='','Titular',[Dependência]) as [Dependência],
Date([Data Limite] ) AS [Data Limite],
[Data Inclusão],
Date([Data Exclusão] ) AS [Data Exclusão],
[Lotacao],
[Rubrica],
if(WildMatch(Rubrica,'Mens.*'),'1',
if(WildMatch(Rubrica,'Co-participação*'),'2',
if(WildMatch(Rubrica,'Cobrança*'),'3',
if(WildMatch(Rubrica,'Devolução*'),'4','Outros')))
) as [Rubrica_Custom],
[Co-Participacao],
[Outros],
[Mensalidade],
[Total Família],
/*Informações Adicionais*/
SubField(FileDir(),'/',3) as Operadora_Grupo,
SubField(FileBaseName(),'_',2) &REPEAT('0', 15 - LEN(SubField(FileBaseName(),'_',2))) as Subfatura_Custom,
Date(Date#('01' &'/' &SubField(FileBaseName(),'_',4) &'/' &SubField(FileBaseName(),'_',3),'DD/MM/YYYY')) as Competencia_Vencimento,
/*Informações do Arquivo*/
FileDir() as FileDir,
FileExtension() as FileExtension,
FileName() as FileName,
FilePath() as FilePath,
FileSize() as FileSize,
FileTime() as FileTime,
FileBaseName() as PK_FileBaseName
FROM [lib://AMIL/DM_*.txt]
//FROM $(File)
(txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
Where WildMatch([Rubrica],'Mens.*');
PreLoad2:
Load Distinct
FileBaseName() as PK_FileBaseName,
date(SubField([@3],' ',-1)) AS EMISSAO,
date(SubField([@4],' ',-1)) AS VENCIMENTO
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
Where WildMatch([@3],'Emissão*')
;
Left join (PreLoad1)
Load * Resident PreLoad2;
Drop Table PreLoad2;
