Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Andy:
Sorry to disappoint but on the real Excel file even after removing all of the extra data at both the
top and bottom it did not resolve the problem (see attached screenshots).
I even went as far as deleting the rows where the data was instead of just deleting the cell
contents. That too failed. I think this import process needs a bit more work.an d suggest (if I
may) the following, or something like it:
1. When importing data that is contained in a table the field definitions should be taken only from
a header (or first row below it), when present. Any other definitions beyond the table should not
cause the field (columns) that are in the table to be redefined as what appears to happen now. In
other words anything (the rows) above or below a table are ignored.
2. If the data is not in a table or the table does not have a header then a special template (not
unlike what can be used when importing a ..cvs file) that 1. defines each field, 2, indicates the
start of the data, and 3, indicates the end of the data. This would mean that the user can
positively define the boundaries of the data to be imported and the exact definitions of each
field. This would also allow the user to keep the things like additional headers, column totals,
other calculations, etc. that currently "mess up" (once it's fixed) the import process. These
special headers can be hidden when not in use.
While it's nice that the software attempts to make the import process easy however, I think there is
an erroneous assumption that the data in the spreadsheet (or just a sheet) exists solely for the
purpose of being imported to Qlik, which in my case at least it is not. This means that to use Qlik
that I either have to maintain different spreadsheets or totally revise the one that I have so that
the sheet(s) where there is data to be imported to Qlik it is "clean" a la Qlik's requirements.
Regards,
Frank
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
your message didn't come through.
Hi Andy:
Not sure why that happened but the original text is below.
**********************************************************************************************************************************************
Hi Andy:
Thanks for your response however, some things still do not make a lot of sense to me. As you can
see I removed the extra header rows (with merged cells) and all of the rows at the bottom that did
any calculations on the data that was in the table, but not the blank row at the top or column to
the left (there are no merged cells in the sheet). I fail to see why they cause import problems.
How can an unused column "A" cause problems in other columns in a table (note that it is there
because I prefer to have space between the Excel row numbers and the data table and also use that
when I want to insert page breaks) ?
Concerning the row at the top, I checked the format of the cells in the columns in the blank row
that were incorrectly imported and they are exactly the same as the format of the cells in that
column in the table (note that I also have that row at the top because I prefer to not have the
table smashed against the excel column labels, it's a visual thing that makes it easier on my aging
eyes). I also fail to see why cells that are not formatted correctly that are below the table can
change the formatting as determined somewhere at the top.
The table that I am attempting to import is a modified version of a raw data table (that would get
refreshed frequently) and has additional columns with various calculations which I want to import.
The modified sheet was created by copying and pasting the raw data table into a separate sheet and
adding other columns with formulas as necessary. _I was able to correctly import the raw data table
even though it has extra rows at the top and an extra column on the left side_ which I mentioned
earlier in the thread.
All of the data I want is in a table so my question is why is it not possible to implement a way to
specifically indicate to Qlik what data is to be imported (an enhancement I suggested in my previous
email) and get the formats from the table itself and ignore the rest?. It seems to me that having
to dedicate a sheet(s) just to be used for just importing to Qlik defeats part of the purpose of
using excel to begin with and potentially causes me more work to reformat my worksheets to use Qlik
then what I get out of using it. Note, the calculations that were previously at the bottom of the
table I would not consider "reports" but were there solely to verify the contents of the table,
which IMO belong where they were.
I will try what you suggested just to see if it solves the problem, but even if it does I will
probably forgo using Qlik because of the necessary rework. I can't try your recommendation to test
using Access as I do not have it.
Sorry, but IMO it's not a very friendly piece of software, at least when it comes to importing from
Excel.
Regards,
Frank
Hi Andy:
I ran the test removing the blank row and column as suggested and the import worked correctly. I
also imported the RawData sheet with the blank column and row at the top and the merged headers and
it imported the column formats correctly. As somewhat expected it did not label the columns
correctly and put the headers in as data. (ss attached)
I can turn off the 1st column (F1) and correct the labels in the other columns but unlike the
columns there doesn't seem to be a way to eliminate the extra rows. Is there?
This import process would work so much better (for me anyway) if there were a way to put boundaries
around the import data. Other than add the necessary boundary markers into the spreadsheet at an
appropriate place (which I could easily hide when not in use) nothing on the sheet would need to be
changed.
Regards,
Frank
I'm glad you resolved the formatting issues and can now start using your data with the array of charts Qlik Sense has to offer. From my experience its worth the effort.
The reason you have multiple rows is because you have used multiple cells to build your header. Use one identified row and a cell for each column header.
Note you can always wrap text in a cell to make column headers take up less space in your spread sheet unfortunately the merged cells will as you have seen impact your formatting.
At least now you can use Qlik Sense with your data and evaluate it as to whether the overhead of formatting your data in a simpler way in excel will be worth the effort going forward.
Please mark any replies that are helpful or correct so other users can see.
Regards
Andy
Hi Andy:
Thanks however, I know why they were there and how to correct it if I choose to eliminate the
headers in the excel sheet or some other such thing however, my question was can I eliminate the
incorrect rows from the Qlik database like I can the columns?
Regards,
Frank
Use header size to remove the unnecessary rows.
see screen shots and my test file for detail. As I increase the header size I lose the unnecessary rows from my test file.
Hi Andy:
I think you missed one other point in my previous email.
I noted that even though I had blank row and columns and header information on one of the sheets
that I was able to import without getting any incorrect formats on the cells, unlike the other sheet
that only had a blank row and a blank column and some of the cells were still incorrectly formatted.
Seems strange that on one sheet even with extra columns, rows, and merged cells that from a cell
formatting perspective the import was done correctly, whereas another sheet in the same workbook
under the same conditions would not import correctly.
Seems strange, if not inconsistent.
Regards,
Frank