I noticed that there was a previous entry for importing .XLSX files. The circumvention was to save the file in the older format, which in my case I can not because of loss of functionality. The previously reported problem was specific to date/time formatting however, it is more extensive then just those mentioned.
First the data that I am attempting to import is in a table, which was not previously mentioned. Sometimes data which is currency gets formatted as dates, sometimes it just the opposite. Percentages are incorrectly formatted and at times whole numbers are formatted as percentages and other times the percentages ar formatted as whole numbers. I've attached screenshots of both the Excel (1 SS) and Qlik (2- SS). The differences should be obvious.
Solved! Go to Solution.
I'm sorry you are still having issues with bringing your data into the Qlik Sense environment.
From your screen shots it looks like you left the blank column and blank row at the top of your sheet. I am unsure as to its purpose but I think removing these rows and columns leaving just a sheet of dedicated data will resolve your import issues. It is what I did with your test file and the import formatted correctly.
It is best to try and source data for reports whether they be Excel, Qlik or other reporting tool from raw data lists be they held in other excel sheets, csv files or databases. The issues you are experiencing are caused by trying to source your data from a report with merged columns embedded in a sheet with other reports below it not a dedicated data source.
As a test try importing your data from your sheet into MS Access as a table for example I believe you will come across similar issues.
The solution I use is to create a sheet in my excel file that contains the raw data which you can use for both additional excel charts and tables plus tools like Qlik Sense. I either hide the sheet in my workbook or make it visible so other users can use it if they want to create new reports from it.
Have you tried setting your header to line correctly?
With a lot of data imports a sample of the data contained within it is taken to determine field formats. As your excel headers look highly formatted probably with merged cells although cannot be sure.
I'm thinking that is what's confusing Qlik Sense on load.
Try loading the data again but removing the 3 rows above the header either in excel or using the header line feature. the filed names will become defined i.e. not F1,F2etc.. and hopefully your data formatting issues will resolve itself.
Let me know how you get on.
I'm not sure what you mean by "setting your header line correctly". You are correct in that some of the header lines in sheet are merged, however those lines are not in the table (but appear to be because of coloring". I had a similar problem with importing from a different sheet and managed to get it imported correctly by applying the correct format at a column level. I tried the same thing on this sheet but that didn't work as you can see.
I will try both of the things you mentioned (once I determine what the header line feature is).
When you were importing your sheet Qlik was calling your columns F1, F2, F3 and your actual headers were being included as data to be imported not being shown as column headers. This I think is the cause of your formatting issues.
You can solve this by having one row at the top of your excel sheet with the correct field names in or by telling Qlik Sense which row in your excel sheet to use as a header row. You do this by identifying the header line.
Thank you for following up. I was engrossed (actually over whelmed but
won't admit that in public) in another problem yesterday. I will be
attempting your solution today, the software gods allowing. I will
report back to you.
I tried deleting the rows above the table except for 1 blank row. The column headings now have the
correct name but some of the column formatting is still incorrect. See attached screenshot.
As requested, I've attached a mini version of the spreadsheet. I tested it and the same thing
occurs. The worksheet you want to test with is "Statistical WS". As before the labels are
correct but the data is not. There may be some errors in it as I deleted several of the other
worksheets to keep the size down. I'm using Excel 2007.
Please let me know is you need more info.
Please find attached a reformatted sheet called copy statistical data. This will load in the correct format.
I removed some data you had at the bottom of your data set that might have been messing your formatting up.
Please mark as correct/helpful any answers that have help resolved your issue.