topic Insert Excel Sheet name into a Field in Integration, Extension & APIs

Insert Excel Sheet name into a Field

Fri, 07 Apr 2017 14:08:54 GMT

Hi everybody,

I'm uploading several Excel files in Qlik Sense. I know their name and I don't want to loop over all files, but just to read the name of the sheets (I don't know if I've been clear, for file I mean test.xlsx and the sheets are within the file like Sheet1, Sheet2,...) in order to put them in a field.

So I've got this situation:

[Table1]:

LOAD *

FROM [lib://Desktop\test.xlsx]

(ooxml, embedded labels, table is Sheet1);

and I want to read "Sheet1" and put it in a field.

How could I do it? I tried with different macro using the ActiveDocument.ActiveSheet.GetProperties.Name method, but I did'n get what I wanted.

Thanks a lot for your help,

best regards

Nicolò Cogno

Re: Insert Excel Sheet name into a Field

vlad_komarov — Fri, 07 Apr 2017 14:45:40 GMT

Nicolò,

Your question is a bit confusing: Are you trying to extract the Sheet Name during the load and create a special field with it? Or are you trying to modify your script to load all sheets into different tables (named by each Excel sheet) in one script?

Re: Insert Excel Sheet name into a Field

Fri, 07 Apr 2017 14:55:26 GMT

I'm sorry Vladimir,

you're right. I'm trying to do the first of the things you said, I'd like to extract the Sheet Name during the load and create a special field with it.

I hope this helps,

thanks a lot in advance

Re: Insert Excel Sheet name into a Field

vlad_komarov — Fri, 07 Apr 2017 19:28:39 GMT

I think the best way in your case is to use the following:

LOAD

...

TableName(n) as TableName

FROM .....;

The only problem is that Qlik requires a 'n' to be defined "as number of the specified table". The first table has number 0. This number should be based on number of tables currently in memory (based on the sequence of the load, I believe)...

I did not have chance to test it, but you should be able to drop each table after each loading statement (since you can save it into QVD anyway), so in this case the 'n' could always be 0 (since only one table will be loaded at a time).

Hopefully, this solution will work for you.

Regards,

Vladimir

P.S. When applicable, please mark this answer Correct or Helpful.

Re: Insert Excel Sheet name into a Field

Mon, 10 Apr 2017 07:33:05 GMT

Hi Vladimir,

thanks a lot for your help!! I used your code, but I'm actually not getting any data into the TableName field.

Do you have any other suggestion?

Thanks again,

Best regards

Nicolò Cogno

Re: Insert Excel Sheet name into a Field

Mon, 10 Apr 2017 16:16:03 GMT

Hi Vladimir,

I found out that the only way to make that works is to use the TableName(i) after loading that table. Is there a way to achieve this within the load script of the table that I need the name of?

Thanks again

Best regards

Nicolò Cogno

swuehl‌ stalwar1‌ vlad.komarov‌

Re: Insert Excel Sheet name into a Field

vlad_komarov — Mon, 10 Apr 2017 17:02:26 GMT

Nicolò,

I am surprised that TableName(i) call does not work inside the load statement.

But your approach is correct too.

You can do a data load first, get the Table name after that and do a JOIN with Resident load to add your new field after that... Couple more steps, but it should not take much more time...

Regards,

Vlad

Re: Insert Excel Sheet name into a Field

neelamsaroha157 — Mon, 10 Apr 2017 17:04:26 GMT

Try this:

This script loads data from all the sheets in excel and variable 'sheetName' creates a fields with all the sheet names.

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables; // Loads all sheet names in the Excel file.

DISCONNECT;

SystemTable:

NoConcatenate

LOAD *

Resident SheetNames

Where Not Match(Upper(TABLE_TYPE), 'TABLE');

DROP Table SheetNames;

FOR index = 0 to NoOfRows('SystemTable')-1 // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SystemTable'), Chr(39)), Chr(36));

Load * ,

FileName() as File_Name,

'$(sheetName)' as Sheet_Name

From [$(file)]

(biff, embedded labels, table is [$(sheetName)$]);

NEXT index

DROP TABLE SystemTable;

Hope this helps.

Re: Insert Excel Sheet name into a Field

Tue, 11 Apr 2017 07:34:29 GMT

Hi Neelam,

first of all thanks for your help.

I tried with your help, but it gives me this error

CONNECTs other than LIB CONNECT are not available in this script mode.

Do you know which could be the problem?

Best regards

Nicolò Cogno

Re: Insert Excel Sheet name into a Field

neelamsaroha157 — Tue, 11 Apr 2017 12:40:30 GMT

I am not sure of the error nicolocogno‌ but instead of using the connect string directly you can try to open your excel via ODBC connection. It might resolve the issue, but again not sure.

Re: Insert Excel Sheet name into a Field

pentaxadmin — Tue, 14 Nov 2017 15:13:48 GMT

I am having similar issue. When trying to use TableName fx, only first sheet is populated. I checked table name on the load and do not see any values. Almost seems like there is an issue with the format. I see you guys are using ODBC to connect to excel. Do you think that connecting directly to *.xlsx may be causing this issue?