Excel Import More info

[#1]

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

[#2]

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

[ogster1974 · Accepted 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.

Regards

Andy

[ogster1974]

your message didn't come through.

[#5]

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

[#6]

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

[ogster1974]

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

[#8]

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

[ogster1974]

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.

[#10]

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