Formating Date in Load statement from excel file.

[leale1997]

Good day!

I have an excel file with several Date fields that QV can not recognize as a date and I can't figure out why.  I have tried the following on the load script:

Date([Field Name],'MM/DD/YYYY') as DateCreated,

(the result is it doesn't load any values for the field at all)

I have also tried using the 'Number' tab in the 'Properties' box after import.

(The result of this is it doesn't visually change the format of the date field at all).

Any input or advise is greatly appreciated.  I am attaching a sample from the excel file I am trying to load.

[vishsaggi · Accepted solution]

Can you share your script you are running. It works for me.

Try below:

DateField:

LOAD [Record ID],

     Project,

     [Originating Site],

     Supply,

     Date(Date#([Date Created], 'MM/DD/YYYY'),'MM/DD/YYYY') AS DateCreated,

     [Date Due],

     DateClosed

FROM

(ooxml, embedded labels, table is PQC);

[leale1997]

Wow so simple!

You might be my new best friend

Many thanks!

[vishsaggi]

Haha! No problem.

[leale1997]

Might I bother you with one follow-up question?

I am creating a calendar in the load statement using one of the fields I just formatted according to the code you created.  It looks like this:

TempCalendar: 
LOAD 
               $(varMinDate) + Iterno()-1 As Num, 
               Date($(varMinDate) + IterNo() - 1) as TempDate 
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
 
DateClosed_MasterCalendar: 
Load 
               TempDate AS DateClosed, 
               week(TempDate) As ClosedWeek, 
               Year(TempDate) As ClosedYear, 
               Month(TempDate) As ClosedMonth, 
               Day(TempDate) As ClosedDay, 
               YeartoDate(TempDate)*-1 as ClosedCurYTDFlag, 
               YeartoDate(TempDate,-1)*-1 as ClosedLastYTDFlag, 
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as ClosedRC12, 
               date(monthstart(TempDate), 'MMM-YYYY') as ClosedMonthYear, 
               ApplyMap('QuartersMap', month(TempDate), Null()) as ClosedQuarter, 
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as ClosedWeekYear, 
               WeekDay(TempDate) as ClosedWeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

Every field is being created EXCEPT  the 'ClosedQuarter' field.  It is coming up blank.  Very strange?  Any ideas?

[shiveshsingh]

What are the values in your mapping table?

QuartersMap

[leale1997]

It's the same data sample I attached to the last question.  Is that what you are asking?  I just gave that data load a name in the QV file and called it 'All Project Table'

[vishsaggi]

No in your script you should have a Load statement starting like

QuarterMap:

Mapping Load ...

[leale1997]

Ok.  turns out it wasn't in the script i copied you on because i forgot to put it in the script and that is in fact the problem.....

No wonder i couldn't get it to work. 

[vishsaggi]

No worries. Let us know if it worked.