Extracting data from an excel

[tsionalush]

Hi all,

I`m receiving a daily excel file which contains the date of the report.
When loading the script for the first time it gives an error that the date is not found, but once opening the excel file and saving it with no changes the script loads succesfully. 

Any idea for why is it happining?

tnx

[Andrei_Cusnir · Accepted solution]

Hello,

This issue is most probably related to EXCEL and how the EXCEL file was generated. For example here is my attempt to reproduce an similar unexpected behavior:

1. I have created a Python code that generates an excel file and takes the current date as value:

2. When the file is generated and I first load the data into Qlik Sense, I have the outcome:

This is incorrect as the current date, can't be 0

3. Now I have opened EXCEL, clicked on save button and reloaded the data again.

4. The new outcome is:

As you can see the correct timestamp value was loaded now. However, this is not an issue with Qlik Sense, but it is an issue with how the values are stored in EXCEL. Most probably, you have a similar use case scenario where the values are not yet ready if the excel is not opened at least one time to generate them. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[tsionalush]

Hi Andrei,

Yes this is a similar case scenario. 
Are you familiar with a way to generate an automatic script that will open the file and save it automaticly before loading the Qlik file?

Tnx

[Andrei_Cusnir · Accepted solution]

Hello,

I think this might not be so easy to achieve. I have used the following code:

This code will open the file right after it gets created. However as you can see:

The value is wrong and the "Editing" mode is not enabled due to security feature in MS Office. I have to manually click on "Enable Editing" to see that the value is properly generated. Example:

I assume that you might want to find a way to open the generated file, enable edit mode and save it again, before running the Qlik Sense reload task, but if you need further help, it is better to post this question in Stack Overflow [1], as it doesn't have to do with Qlik Sense and you will probably get a much faster response regarding this issue there.

I hope that this information was helpful.

---

[1] https://stackoverflow.com/

[tsionalush]

Thanks again Andrei,

Could you please tell me which editor tool you used to write the script above?

[Andrei_Cusnir]

Hello,

The editor that I am using is Visual Studio Code [1], which is also known as VS Code.

---

[1] https://code.visualstudio.com/