Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I would like to request help from you.
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;
PK_FileBaseName | A | B | C | date_pay | date_shipping
1
2
3
4
5
Hello! I want to thank you for your help.
It worked. Good job.
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;
Thats great news.
Can you mark the appropriate response as correct and any others as helpful to close the ticket please?