topic Excel import wizard dialog shows certain rows' numbers as dates in QlikView

Excel import wizard dialog shows certain rows' numbers as dates

Mon, 20 Jun 2011 04:18:27 GMT

I imported data from an Excel 2003 file and saw strange behavior in the import file wizard. This is the wizard that appears when you click Table Files in the script editor, and open a spreadsheet.

What happens, is numbers in a certain row are displayed as dates. For example:

In this case, any numeric fields in the 6th row are always formatted as dates. If I delete the 6th row, so that the 7th row becomes the 6th row, and reopen the file wizard, the new 6th row of data gets the same treatment.

After testing some other data out, it turns out that for every nth column that it recognizes as a date field, the wizard is formatting any numeric data in the nth row, as dates. Meanwhile, the dates in the other rows are formatted incorrectly as numbers, and not dates.

In this picture, columns 2 and 4 were formatted as dates:

Fortunately, I think it's just a glitch in the File Wizard results browser, as once the data gets into qlikview, everything seems to work fine, as seen here, using the same data as in the last screenshot:

Weird, right? Not a big problem since the data seems to get in the program ok, but its still disconcerting.

Also, I do not see the same error when importing the same data as a CSV file.

Excel import wizard dialog shows certain rows' numbers as dates

nagaiank — Mon, 20 Jun 2011 04:51:54 GMT

I tried in QV version 10SR1 with Microsoft Excel 2010. The table wizard showed all dates as numbers, nothing wierd! I will keep an eye on loading tables from Excel and report if I come across anything wierd.

Excel import wizard dialog shows certain rows' numbers as dates

Mon, 20 Jun 2011 05:11:09 GMT

Thanks for your reply, krishna.

Since spreadsheets containing date fields are very common, my guess is that the only way this behavior could have gone unnoticed is if it only happens with older files, as most users probably have migrated to Excel 2010 by now.

Did you have a chance to try loading the Excel 2003 file that I attached to my post?

Meanwhile, I will look into upgrading my office suite.

Excel import wizard dialog shows certain rows' numbers as dates

nagaiank — Mon, 20 Jun 2011 05:18:19 GMT

No. All my PCs have Office 2010. I tried to save it as Excel 2003 (*.xls) file. It seems to load all date fields as numbers.

Excel import wizard dialog shows certain rows' numbers as dates

Mon, 20 Jun 2011 05:48:38 GMT

But you don't have to open Excel to load the file. Sorry, I must have not been clear. Simply open Qlikview, Edit Script, Table Files, and open the attached spreadsheet from there. Hopefully you will be able to reproduce the problem.