9 Replies Latest reply: Nov 21, 2014 5:38 AM by Murali Srithar RSS

    DATE field not reading QV when I load multiple excel sheet method.

      Hi QV Team,

       

      Here is my code to load multiple excel sheets from Sales folder. It worked fine but only problem is DATE field is not reading properly.I'm getting 01/01/1900 or 01/01/1899 format. Pls someone help me out. If there is anyother way to ready all the Excel Sheets and getting correct DATE field.

       

      TEMP:

      LOAD

      filetime()                                           as file_time,

      filepath()                                           as file_path,

      filebasename()                                       as z_file,

      timestamp(filetime())                                as time_stamp

      FROM [U:\Common\SAPReports\Sales\SALESREPORT_11*.xls]

      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

       

      left join (TEMP)

      load

      max(timestamp(file_time))                             as max_time

      resident TEMP;

       

      FINAL:

      LOAD *, 1 as flag

      resident TEMP where time_stamp=max_time;

      drop table TEMP;

       

       

      SAPsalesreport:

      LOAD

      [Sales Doc.]as [Sales Order #],

        DATE(Req.dlv.dt, 'MM/DD/YYYY') as [Request Delivery DATE] ,

        DATE(Req.dlv.dt, 'MM/DD/YYYY') as [Delivery Testing Date] ,

        DATE(Dlv.Date, 'MM/DD/YYYY') as [Delivery DATE] ,

        DATE(Dlv.Date, 'MM/DD/YYYY') as [Delivery Test Date2] ,

        [Prod order]

      FROM

      [D:\SAPReports\Sales\SALESREPORT_111*.xls]

      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);