Timestamp has got single courses ' '

[Anonymous]

Hi Everyone,

I am trying to use a timestamp and trying to extract the month, year and day but for some reason, I am not successful

one thing I have noticed is the Time stamp is showing commas in the text field

this is the following code i am using

GI_PVX:

LOAD [Item code] As REF,

        [Item name],

       [Date timestamp] as PVX_DATE,

      Year(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_YEAR,

       Month(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_MONTH,

       Day(TimeStamp#([Date timestamp],'DD/MM/YYYY hh:mm')) As PVX_DAY,

FROM

(ooxml, embedded labels);

Please help me on this.

Regards,

M Talal

[Anonymous]

Hi Sunny,

Thank you so much, Its seem we are ver close,I am doing this and getting some error when trying to reload.

Please let me know where  am  I going wrong.

[sunny_talwar]

The order of the load is not right, Year, Month and Day needs to go above the place where you create the PVX_DATE field

[Anonymous]

Hi Sunny,

I did that but now its showing this error, What can be the problem now.

[sunny_talwar]

May be get rid of this * from your first load

[Anonymous]

I removed that now its giving errror "Field not found - <Date timestamp>"

[sunny_talwar]

Can you post your script as text rather than image?

[Anonymous]

GI_PVX:

LOAD *,

     Year(PVX_DATE) as PVX_YEAR,

     Month(PVX_DATE)as PVX_MONTH,

     Day(PVX_DATE) as PVX_DAY;

LOAD *,

  Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;

LOAD

  [Item code] As REF,

     [Item name],

     User,

     From,

     To,

     Quantity,

     Comments,

     [From Container],

     [To Container],

     [Attribute 3],

     [Attribute 4],

     [Attribute 5],

     [Attribute 6]

FROM

(ooxml, embedded labels);

[sunny_talwar]

Try this:

GI_PVX:

LOAD *,

    Year(PVX_DATE) as PVX_YEAR,

    Month(PVX_DATE)as PVX_MONTH,

    Day(PVX_DATE) as PVX_DAY;

LOAD *,

  Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;

LOAD *,

  [Item code] As REF

FROM

(ooxml, embedded labels);

[Anil_Babu_Samineni]

Muhammad Talal Uddin wrote:

GI_PVX:

LOAD *,

     Year(PVX_DATE) as PVX_YEAR,

     Month(PVX_DATE)as PVX_MONTH,

     Day(PVX_DATE) as PVX_DAY;

LOAD *,

  Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;

LOAD

  [Item code] As REF,

     [Item name],

     User,

     From,

     To,

     Quantity,

     Comments,

     [From Container],

     [To Container],

     [Attribute 3],

     [Attribute 4],

     [Attribute 5],

     [Attribute 6]

FROM

(ooxml, embedded labels);

From here, You don't have field [Date timestamp]. Where this field coming from.

LOAD *,

  Date(Floor(TimeStamp#(PurgeChar([Date timestamp], Chr(39)), 'DD/MM/YYYY hh:mm'))) as PVX_DATE;

LOAD

  [Item code] As REF,

     [Item name],

     User,

     From,

     To,

     Quantity,

     Comments,

     [From Container],

     [To Container],

     [Attribute 3],

     [Attribute 4],

     [Attribute 5],

     [Attribute 6]

FROM

(ooxml, embedded labels);

[Anonymous]

Hi its is coming from this xlsx file