topic Re: Loading Excel Files and check in QlikView

Loading Excel Files and check

prees959 — Wed, 24 Oct 2018 08:10:28 GMT

Hi,

I'm loading a number of excel files from a folder that should have the same number of columns and then output a final QVD.

So Load the excel into TEMP, carry out some formatting, save it as a TEMP_filedate.qvd and drop TEMP .. and repeat

But, sometimes there is the odd excel file with differing columns that causes a seperate TEMP-1 table to be created and throws the rest of the script out.

Is there a way to test that there are no Tables left in the qvw at the end of this load process and if there are create a small excel file with the name of the excel file that is causing the issue and then drop all tables?

Thanks for any help.

Phil

Re: Loading Excel Files and check

atoz1158 — Wed, 24 Oct 2018 08:22:44 GMT

You could try adapting this to suit your needs

//This script counts the number of tables loaded and outputs the names and the number of rows and columns in each.

//This tab should be at the end of the entire script.

FOR k = 0 TO NoOfTables()-1

LET vTableName = TableName($(k));

LET vNoOfRecords = NoOfRows('$(vTableName)');

LET vNoofColumns = NoOfFields('$(vTableName)');

Tables:

LOAD

'$(vTableName)' AS Table,

$(vNoOfRecords) AS [No Of Rows],

$(vNoofColumns) AS [No Of Columns]

AUTOGENERATE 1;

NEXT k;

Re: Loading Excel Files and check

avkeep01 — Wed, 24 Oct 2018 10:55:50 GMT

Hi Phil,

You can use a loop trough all the file and get them one by one. Then you can add FILENAME($(vFile)) to get the specific name for the file causing your extra columns.

FOR EACH vFile IN FILELIST('path\*.xlsx')

LOAD

FILENAME($(vFile)) AS Filename

FROM $(vFile);

NEXT vFile