topic Re: Ignore empty excel columns in QlikView

Ignore empty excel columns

ericdelaqua — Fri, 09 Nov 2018 04:54:41 GMT

Hello,

I have an excel table which sometimes come with some empty columns at the start like below and has no header.

- I want to ignore the leading blank columns while loading the data

- I want to dynamically name the remaining column from F1, F2 ,F3 and so on in order.

so in this case Column A and column B WILL BE IGNORED and C ,D,E,F,G will be renamed F1,F2,F3,F4,F5

I need you expertise with this please.

Thanks

Re: Ignore empty excel columns

jonathandienst — Fri, 09 Nov 2018 05:09:20 GMT

Like this?

LOAD C as F1,

D as F2,

E as F3,

F as sF4

FROM [..\Desktop\dyanmic header.xlsx]

(ooxml, explicit labels, table is Sheet1);

Re: Ignore empty excel columns

ericdelaqua — Fri, 09 Nov 2018 10:35:54 GMT

Hi Jonathan,

I think I didn't explain properly.. The issue is that I don't know at what position the first column starts. The excel files are dropped In a location and I need Qlik to identify the leading blank columns and ignore them then start the heading from the first data column in my example C.

Thank you

Re: Ignore empty excel columns

Frank_Hartmann — Fri, 09 Nov 2018 12:19:39 GMT

You can execute a vbs function in your load script for deleting the blank columns.

with execution of this macro the data of the resulting excel will always start with field A !

then you can load the formatted excel afterwards in your load script.

add this to your module and adapt the path to your excelfile:

function ExcelExport
Set excelFile = CreateObject("Excel.Application")    
excelFile.Visible = False    
Set objWorkbook = excelFile.Workbooks.Open("C:\Users\admin\Desktop\dyanmic header.xlsx")    
Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1)
strCode = _
"Sub Delete_Columns()" & vbCr & _
"Dim C As Integer" & vbCr & _
"C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column" & vbCr & _
"Do Until C = 0" & vbCr & _
"If WorksheetFunction.CountA(Columns(C)) = 0 Then" & vbCr & _
"Columns(C).Delete" & vbCr & _
"End If" & vbCr & _
"C = C - 1" & vbCr & _
"Loop" & vbCr & _
"End Sub"
xlmodule.CodeModule.AddFromString strCode
excelFile.Run "Delete_Columns"
excelFile.DisplayAlerts = False
excelFile.ActiveWorkbook.Save
excelFile.Visible = False
excelFile.Quit   
Set objWorkbook = nothing
Set objWorkbook = nothing
End function

Your Loadscript should look like this:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';


let VBS_Macro = ExcelExport();


LOAD A as F1,
     B as F2,
     C as F3,
     D as F4
FROM

(ooxml, no labels, table is Sheet1);

if you have further questions feel free to ask.

hope this helps!

Re: Ignore empty excel columns

ericdelaqua — Fri, 09 Nov 2018 17:58:01 GMT

Thank you Frank I will be testing your approach I just have a question.

Is the vb storing the fixed excel file back to the location?

Is column C hard codes? I mean will it work for any excel file removing the blank columns

Thank you

Re: Ignore empty excel columns

Frank_Hartmann — Fri, 09 Nov 2018 18:12:13 GMT

Yes it will work for every excel containing blank columns.

And yes, the macro is saving the file back without any blank columns