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

[Anil_Babu_Samineni]

I have not seen the field from this excel

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]

"Field names must be unique within table"

this is the error now.

[sunny_talwar]

How about 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 *

FROM

(ooxml, embedded labels);

[Anonymous]

Item code

Item name

Date timestamp

User

From

To

Quantity

Comments

From Container

To Container

Attribute 3

Attribute 4

Attribute 5

Attribute 6

139922

FOX GLACIER MINTS 195G

'17/03/2017 15:02'

Malik

GI1B

1932

Stock registered

CON-7384

1

FOOD VAT

1

SweetsBags-Confectionery

12362

CHUPA CHUPS 20 MINI LOLLIES

'09/02/2017 17:40'

AUGUSTO

GI1B

1944

Stock registered

CON-5647

1

FOOD VAT

1

SweetsBags-Confectionery

[Anonymous]

I need [Item code] as REF

[sunny_talwar]

This worked for me

Table:

LOAD *,

    Year(PVX_DATE) as PVX_YEAR,

    Month(PVX_DATE)as PVX_MONTH,

    Day(PVX_DATE) as PVX_DAY;

LOAD *,

  [Item code] as REF,

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

LOAD * INLINE [

    Item code, Item name, Date timestamp, User, From, To, Quantity, Comments, From Container, To Container, Attribute 3, Attribute 4, Attribute 5, Attribute 6,

    139922, FOX GLACIER MINTS 195G, "'17/03/2017 15:02'", Malik, , GI1B, 1932, Stock registered, , CON-7384, 1, FOOD VAT, 1, SweetsBags-Confectionery,

    12362, CHUPA CHUPS 20 MINI LOLLIES, "'09/02/2017 17:40'", AUGUSTO, , GI1B, 1944, Stock registered, , CON-5647, 1, FOOD VAT, 1, SweetsBags-Confectionery,

];

Just replace inline with your Excel load here

[Anonymous · Accepted solution]

Hi Everyone,

Thank you so much for trying really appreciate your time and effort:

Finally the following code has worked,

GI_PVX:

LOAD [Item code] As REF,

     [Item name],

   //  date(Date#([Date timestamp],dd/mm/yyyy)),

    Replace( PurgeChar([Date timestamp],chr(39) ),',',';') as PVX_DATE,

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

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

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

     User,

     From,

     To,

     Quantity,

     Comments,

     [From Container],

     [To Container],

     [Attribute 3],

     [Attribute 4],

     [Attribute 5],

     [Attribute 6]

FROM

(ooxml, embedded labels);

I took this code =Replace( PurgeChar( vVariable ,chr(39) ),',',';') from stephan1983‌.

Regards,

M Talal