Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! I have several TXT files and do the consolidation of these files in a QVD. When one of these files is not in the default or corrupted layout, the loading process is interrupted. Is there any way to make the process continue by just considering the files that are in the default and generating a list of the files that were not loaded?
Thank you very much for your attention.
🙂
Look at this link
Same will work in Qlik Sense as well
Hello @sunny_talwar
Can you give me an example of how to use it? I wanted to ignore the errors, but to generate a table with the errors and the name of the file from which the error is made. I believe you need to use the ScriptErrorList.
This example is an example load, for example, where these variables are variable for error information?
What should I put before LOAD?
Tabela1 Load [Campo 1] as [A], [Campo 2] as [B], [Campo 3] as [C], FileBaseName() as FileBaseName FROM [C:\@FTP_FATURAMENTO\DM_*.txt] (txt, codepage is 1252, embedded labels, delimiter is '#', msq, header is 5 lines);
What should I put after LOAD?
Thanks for the help and attention
🙂
Should be something like this
SET ErrorMode = 0; Tabela1: LOAD [Campo 1] as [A], [Campo 2] as [B], [Campo 3] as [C], FileBaseName() as FileBaseName FROM [C:\@FTP_FATURAMENTO\DM_*.txt] (txt, codepage is 1252, embedded labels, delimiter is '#', msq, header is 5 lines); IF ScriptError > 0 THEN LOAD FileName() as ErrorFile FROM [C:\@FTP_FATURAMENTO\DM_*.txt] (txt, codepage is 1252, embedded labels, delimiter is '#', msq, header is 5 lines); ENDIF; SET ErrorMode = 1;
I applied the script and did not get the return of files that have "error". Attached is two files that meet the load pattern and two with error. One is out of standard layout and the other is corrupted. How do I proceed to get the information that these two files were not loaded and the reason?
Thanks again!
🙂
Archive
Try something like this
SET ErrorMode = 0; DM: LOAD Código, Beneficiário, Matrícula, CPF, Plano, Tipo, Idade, Dependência, [Data Limite], [Data Inclusão], [Data Exclusão], Lotacao, Rubrica, [Co-Participacao], Outros, Mensalidade, [Total Família], FileBaseName() as FileBaseName FROM [DM_*.txt] (txt, codepage is 1252, embedded labels, delimiter is '#', msq, header is 5 lines); LET vScriptError = ScriptError; LET vScriptErrorDetails = ScriptErrorDetails; LET vScriptErroCount = ScriptErrorCount; LET vScriptErrorList = ScriptErrorList; TRACE $(vScriptErroCount); IF $(vScriptErroCount) > 0 THEN TRACE 'I am inside if statement now'; TESTE: LOAD FileName() as ErrorFile, '$(vScriptErroCount)' as ERROR_SEQ_NUMBER, '$(vScriptError)' as ERROR_NAME, '$(vScriptErrorDetails)' as ERROR_DETAIL FROM [DM_*.txt] (txt, codepage is 1252, embedded labels, delimiter is '#', msq, header is 5 lines); ENDIF; SET ErrorMode = 1;
I get this information
Hi @sunny_talwar 🙂
Can you help me get to the dead end of this issue? Using your tip, I realized that it is difficult to adjust, for example, I am not able to solve. I used the script below:
SET ErrorMode = 0; DM: LOAD Código, Beneficiário, Matrícula, CPF, Plano, Tipo, Idade, Dependência, "Data Limite", "Data Inclusão", "Data Exclusão", Lotacao, Rubrica, "Co-Participacao", Outros, Mensalidade, "Total Família", FileBaseName() as PK_FileBaseName FROM [lib://FTP_CF/DM_*.txt] (txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines); LET vScriptError = ScriptError; LET vScriptErroCount = ScriptErrorCount; LET vScriptErrorDetails = ScriptErrorDetails; LET vScriptErrorList = ScriptErrorList; TRACE $(vScriptErroCount); IF $(vScriptErroCount) > 0 THEN TRACE 'I am inside if statement now'; TESTE: LOAD FileBaseName() as PK_FileBaseName, '$(vScriptError)' as ScriptError, '$(vScriptErroCount)' as ErrorCount, '$(vScriptErrorList)' as ScriptErrorList, '$(vvScriptErrorDetails)' as ScriptErrorDetails FROM [lib://FTP_CF/DM_*.txt] (txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines); ENDIF; SET ErrorMode = 1;
* The file that in fact is in error is not appended and the file that is correct is listed with code 1 (General error)
I understand that in the DM_103342000_2018_10 file there should not be any code or description and in the DM_128088000_2018_11X file that is not listed the code 11 Field Not Found should be displayed.
Can you help me solve it so that the expected information for the file that actually is in trouble is displayed?
Thanks a lot for the help!
Att