While read excel file, qvw reading in number format

[jagannalla]

Hello,

Please see the below image:

- When i'm reading this file, I need to get the data from 4th row. So, I'm reading the file through File Wizard:Type and i'm choosing HeaderSize as Lines & it's value as 3, Labels-> Embedded Lables. See the below image:

- Now you can see the values what i had in excel. Leave the first 3 columns. If you see from 4th column it is reading the dates in number format.

1. I need this as what i had in excel date format.

2. If i load below script for the above excel, instead of values of dates it shows me dates in number format values only.

CrossTable(Month,Value)

LOAD

     [Mach #] as MachineNo,

     40909,

     40940,

     40969,

     41000,

     41030,

     41061,

     41091,

     41122,

     41153,

     41183,

     41214,

     41244

FROM

[Document\KP\CFPM 2012-08 V2 NL.xlsm]

(ooxml, embedded labels, header is 3 lines, table is Production);

- The output of this script is as show below

- I need in month column the dates which i have and in Value column i need the values of respective dates.

Please let me know what is the wrong i'm doing.

Cheers!!

Jagan

[Miguel_Angel_Baeyens]

Hi Jagan,

After the Crosstable load, do the following to represent the numeric values as literals:

Cleanup:
LOAD MachineNo,
     MonthName(Month) AS Month,
     Value
RESIDENT Table;

Hope that helps.

Miguel

[jagannalla]

no use miguel, same values i'm getting. I think while reading the file itself it gives me problem. In resident load we can load already load columns with data.

but when i read the excel directly it looks like this. I don't know why it shows dates instead of values.

-  The excel sheet data which i'm reading is getting there data based on formulas.

[jagannalla]

Right now the excel type is Microsoft Excel Macro-Enabled Worksheet. If i convert the type of excel to Microsoft Excel 97-2003 worksheet (manually i open the file and i save to 97-2003). No the above code which we did is working perfectly.

I don't know how to push the MsExcel Macro-Enabled worksheet data to either SQL Server or  into flat files. If we can push those data exaclty what we have in excel. We can get data form those database or flat files.

Let me know if I'm wrong.

[Miguel_Angel_Baeyens]

Hi Jagan,

If the table is created by macros, they are unlikely to run neither using the table wizard nor using an ODBC to the Excel spreadsheet. QlikView can read Excel file from all versions of Microsoft Office with no issues, but the table needs to be there (as it might happen with some tables in Navision that are not stored into the SQL Server, such flowfields).

You can read from the SQL Server directly, of course, and do al necessary transformations in the script.

Hope that helps.

Miguel