19 Replies Latest reply: Jul 27, 2015 12:46 PM by Frank Gemma RSS

    Excel Import More info

      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

        • Re: Excel Import More info
          Andy Weir

          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

            • Re: Excel Import More info

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

                • Re: Excel Import More info
                  Andy Weir

                  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.

                  • Re: Excel Import More info
                    Andy Weir

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

                      • Re: Excel Import More info

                        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

                        • Re: Excel Import More info

                          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

                            • Re: Excel Import More info
                              Andy Weir

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

                                • Re: Excel Import More info

                                  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

                                    • Re: Excel Import More info
                                      Andy Weir

                                      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

                                        • Re: Excel Import More info

                                          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

                                            • Re: Excel Import More info
                                              Andy Weir

                                              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

                                                • Re: Excel Import More info
                                                    • Re: Excel Import More info
                                                      Andy Weir

                                                      your message didn't come through.

                                                        • Re: Excel Import More info

                                                          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

                                                          • Re: Excel Import More info

                                                            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

                                                              • Re: Excel Import More info
                                                                Andy Weir

                                                                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