Qlik Sense and Excel xlsb files

[mstoler]

Hello,

What is the best approach to reading in a directory of excel XLSB (binary) files.

I have not worked with XLSB flies in the past.

Thank You,

Michael

[Saravanan_Desingh]

You can use OLEDB to read the XLSB file

[mstoler]

Which OLEDB Driver?

Can I read all the files in a directory?

Thanks,

Michael

[Saravanan_Desingh]

Please try this.

ODBC CONNECT32 TO[Excel Files;DBQ=C:\Users\sara\Downloads\ScoreCalculation.xlsb];

tabs:
SQLtables;

[Saravanan_Desingh]

Also please try this.

ODBC CONNECT TO[Excel Files;DBQ=C:\Users\sarav\Downloads\QV_ScoreCalculation.xlsb];

[marcus_sommer]

In addition to the above you could replace the content of DBQ parameter with a variable like:

for each file in filelist('C:\Users\sara\Downloads\*.xlsb')
   ODBC CONNECT32 TO[Excel Files;DBQ=$(file)];
   ...

 - Marcus

[mstoler]

This works in Qlik Sense?

[marcus_sommer]

I think within the leagcy-mode it should work in this way. If this isn't a possibility you could create another kind of loop, maybe something like this:

t: first load filepath() as FilePath, filename() as FileName from Excel.xlsb (fix, codepage is 1252, no labels);

and then looping through FilePath or FileName embedded within the lib-logic.

- Marcus

[mstoler · Accepted solution]

Hello,

I had the user convert to xlsx files and then read them all in with a wildcard.

I would recommend using xlsx files.

Thanks,

Michael