Solved: Excel Import More info - Qlik Community

Report Inappropriate Content · ‎2015-07-16

Hello:

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.

Regards,

Frank

ogster1974 · ‎2015-07-27

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.

Regards

Andy

View solution in original post

ogster1974 · ‎2015-07-16

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.

Regards

Andy

Report Inappropriate Content · ‎2015-07-16

Hi Andy:

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).

ogster1974 · ‎2015-07-16

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.

ogster1974 · ‎2015-07-17

How did you get on. Did you manage to get your data in the right format?

Report Inappropriate Content · ‎2015-07-17

Hi Andy:

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.

Regards,

Frank

Report Inappropriate Content · ‎2015-07-17

Hi Andy:

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.

Regards,

Frank

ogster1974 · ‎2015-07-20

very strange can you send me over a sample file with a few rows in so I can see what's going on.

Report Inappropriate Content · ‎2015-07-23

Hi Andy;

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.

Regards,

Frank

ogster1974 · ‎2015-07-24

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.

Regards

Andy