How load date from file?

[vchuprina]

Hi all,

I received files and need to load data from them, but have some problems:

1. Data located in different columns and columns(A-C) and lines (3-4)

I use following code for line 3 and the same for line 4 (WHERE recno() = 2; )

How check two lines by one load?

LOAD

    num(subfield(rtrim(A & ' ' & B & ' ' & C & ' ' & D ), ' ', -1)) AS Week1,

FROM [$(vFile)]  (ooxml, no labels, header is 2 lines, table is [$(vSheetName)])

WHERE recno() = 1;

2. From time to time data has different format (see screenshot), for example

05/02/2015 09:44:35

WE 6.06.15

I tried use following code to load data to variable

LET vWEEK1 = replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/');

.

.

.

weekstart(date('$(vWEEK1)','YYYYMMDD'),0,-2) AS Week

But in this case for some reason I lose data in date format (05/02/2015 09:44:35) and load only WE 6.06.15,

if I use

LET vWEEK1 =(peek('Week1', -1, 'Date1') 

I load only 05/02/2015 09:44:35 and lose WE 6.06.15,

Also I tried use 'IF' , but it doesn't work correctly and return only WE 6.06.15 data  :

LET vWEEK1 = IF (Wildmatch(peek('Week1', -1, 'Date1'), '*WE*'), replace(replace(peek('Week1', -1, 'Date1'),'WE',''),'.','/'),peek('Week1', -1, 'Date1'));

Thanks,

Vitaliy

[sunny_talwar]

May be try it with Alt:

LET vWEEK1 = Alt(Date#(Replace(Replace(Peek('Week1', -1, 'Date1'),'WE',''),'.','/'), 'M.DD.YY'), Date#(Peek('Week1', -1, 'Date1'), 'MM/DD/YYYY hh:mm:ss'))

[vchuprina]

I tried to use you code, but still have the same result

[sunny_talwar]

So you are using variable because you are checking the format for a single date, right? or do you have a column with different date formats and you are trying to force them into a date format???

[vchuprina]

I'm using variable because date under main data and I can't load all necessary information by one load.

Date in 3 or 4 lines and data start from 9 line.

Date comes in different format from week to week (randomly)  and I'm trying load date as Date each week without any manual work.

05/02/2015 09:44:35

05/09/2015 09:40:27

WE 6.06.15

[sunny_talwar]

Tried this script, seems to work:

Table1:

LOAD Date as Date1

FROM

Community_168133.xlsx

(ooxml, embedded labels, table is Sheet1);

LET vWEEK1 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));

Table2:

LOAD Date as Date2

FROM

Community_168133.xlsx

(ooxml, embedded labels, table is Sheet2);

LET vWEEK2 = Alt(Date(Date#(Trim(Replace(Peek('Date2', -1, 'Table2'),'WE','')), 'M.DD.YY'), 'MM/DD/YYYY'), Date(Peek('Date1', -1, 'Table1'), 'MM/DD/YYYY'));

[vchuprina]

Thank you for you help, but this doesn't work too(

[sunny_talwar]

Would you be able to share a sample where it doesn't work?

[vchuprina]

I want to load date with different format as Date.

For example I want to see following

And you script shows

[sunny_talwar]

That's what I asked you intially. I never touched the field itself. I was working on the variable. If you check the variable overview, you will see the correct format. So you need the field to be fixed.

Let me get you that.

Best,

Sunny