4 Replies Latest reply: Dec 30, 2014 1:02 AM by jagan mohan rao appala RSS

    Date formate

      Hi All,

      I have many tables among which i have  data by name transaction date  in a folder which has 6 months of data (that is there are again 6 folders from Jan to jun ) and each folder has 30 excel sheets for that particular month  iam attaching the screen shot so that u can understand it clearly and the date formate is like 02-12-2014.

      What i want to know is how do i fetch all 30 exzcel sheets at a time and convert the dtae formate as Jan-02-2014 for all the months and days .

      I hope iam clear  .

      Thanks In Advance .

        • Re: Date formate
          Anand Chouhan

          Hi,

           

          Load all csv file and in the load script mention FileName() for get which is the file you loaded data and use date function for convert file name into date format.

           

          LOAD

          FileName() as FileName,

          Date(Date#(Left(FileName(),10),'DD.MM.YYYY'),'MMM-DD-YYYY') as Date

          FROM

          *.csv

          (txt, utf8, embedded labels, delimiter is ',', msq);

           

          Regards

          Anand

          • Re: Date formate
            Gysbert Wassenaar

            You can use the date() function to change the format of a date: date(MyDateField, 'MMM-DD-YYYY') will format dates like you want. If your date field contains text values instead of date values you first have to use the date#() function to change the text value into a date value. For example date(date#('02-12-2014','DD-MM-YYYY'),'MMM-DD-YYYY')

            • Re: Date formate
              Serhan Celebi

              Hello,

               

              You can achieve this using special character '*'.

               

              Such as :

               

              test:

              LOAD

                  A

              FROM [lib://TEST/*.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

              As long as the formats match (columnames etc), there is no problem.

               

              BR

               

              Serhan

              • Re: Date formate
                jagan mohan rao appala

                Hi,

                 

                If you want to load all files with a single script statement then use wildcard character for file name like below and for changing date format use Date() and Date#() functions.

                 

                TableName:

                LOAD

                    *,

                Date(Date#(DateFieldName, 'DD-MM-YYYY'), 'MMM-DD-YYYY') AS FormattedDate

                FROM [lib://Folder/*.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                OR

                 

                TableName:

                LOAD

                *,

                Date(DateFieldName, 'MMM-DD-YYYY') AS FormattedDate

                FROM [lib://Folder/*.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                Hope this helps you.

                 

                Regards,

                Jagan.