topic Check if datasource is available (.csv) in QlikView

Check if datasource is available (.csv)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

I am trying to create a check if this CSV Datasource is available or not.

If its available it should run the script and import the data, if not it should move on to the next file.

Something seems to be wrong here? Would be very nice if you could help me....

thanks

oliver

IF len(FileSize('File_2016-11.CSV')) > 0 THEN

LOAD

Auftragskonto as [KontoNr.],

Buchungstag,

Valutadatum,

Buchungstext,

Verwendungszweck,

"Glaeubiger ID",

Mandatsreferenz,

"Kundenreferenz (End-to-End)",

Sammlerreferenz,

"Lastschrift Ursprungsbetrag",

"Auslagenersatz Ruecklastschrift",

"Beguenstigter/Zahlungspflichtiger",

"Kontonummer/IBAN",

"BIC (SWIFT-Code)",

Betrag,

Waehrung,

"Info",

month(Valutadatum) as Monat,

year(Valutadatum) as Jahr,

Week(Valutadatum)as Woche

FROM [lib://SPK/File_2016-11.CSV]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

END IF

Re: Check if datasource is available (.csv)

lironbaram — Tue, 29 Nov 2016 12:20:57 GMT

you need to change the error mode

to error mode that doesn't stop the reload process on error

add thus before your if statement

set errormode=0;

and add this after the "end if"

set errormode=1;

also change the if line to this

IF len(FileSize('[lib://SPK/File_2016-11.CSV]')) > 0 THEN

Re: Check if datasource is available (.csv)

trdandamudi — Tue, 29 Nov 2016 12:25:20 GMT

You can try as below:

If not isnull(filetime('C:\File_2016-11.CSV')) then <-- make sure you update the correct path

LOAD

Auftragskonto as [KontoNr.],

Buchungstag,

Valutadatum,

Buchungstext,

Verwendungszweck,

"Glaeubiger ID",

Mandatsreferenz,

"Kundenreferenz (End-to-End)",

Sammlerreferenz,

"Lastschrift Ursprungsbetrag",

"Auslagenersatz Ruecklastschrift",

"Beguenstigter/Zahlungspflichtiger",

"Kontonummer/IBAN",

"BIC (SWIFT-Code)",

Betrag,

Waehrung,

"Info",

month(Valutadatum) as Monat,

year(Valutadatum) as Jahr,

Week(Valutadatum)as Woche

FROM [lib://SPK/File_2016-11.CSV]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

END IF

Re: Check if datasource is available (.csv)

marcus_sommer — Tue, 29 Nov 2016 12:30:51 GMT

An alternative to your single-load approach could be to use a dirlist/filelist within a loop maybe extended with further checks on filetime/filesize and/or parts from the filename:

Loops in the Script

- Marcus

Re: Check if datasource is available (.csv)

Anonymous — Tue, 29 Nov 2016 14:35:27 GMT

Thank you very much, this is working.

But unfortunately I got the next error message

Looks that there is something missing between the 2 loading tables?

....

month(Valutadatum) as Monat,

year(Valutadatum) as Jahr,

Week(Valutadatum)as Woche

FROM [lib://SPK/XYZ_2017-12.CSV]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

END IF

set errormode=1;

[Konto_ABC]:

// >>>>>>>>>>>>>>>>>>>>>>>>>> Jahr 2015

LOAD

Filename() as Quelle,

Auftragskonto as [KontoNr.],

Buchungstag,

Valutadatum,

Buchungstext,

Verwendungszweck,

"Glaeubiger ID",

Mandatsreferenz,

"Kundenreferenz (End-to-End)",

Sammlerreferenz,

"Lastschrift Ursprungsbetrag",

"Auslagenersatz Ruecklastschrift",

"Beguenstigter/Zahlungspflichtiger",

"Kontonummer/IBAN",

"BIC (SWIFT-Code)",

Betrag,

Waehrung,

"Info",

month(Valutadatum) as Monat,

year(Valutadatum) as Jahr,

Week(Valutadatum)as Woche

FROM [lib://SPK/ABC_2015.CSV]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);