4 Replies Latest reply: Oct 24, 2013 10:14 AM by Clever Anjos RSS

    Loading from Excel files

    Jon Einebærolm

      Dear all,

       

      Each month I'm loading from a new excel file. I'm using the following dirty "formula"

      Load
      *
      FROM
      [..\Data\status-net*]

       

      load
      club as Club,
          
      dep,
           'actual'
      as Scenerio,
         
      Left(Measure_Date, Index(Measure_Date,' ', SubStringCount(Measure_Date, ' ')-1)-1) as Measure,
          
      Date(Date#(Left(TextBetween(Measure_Date,' ', ' ', (SubStringCount(Measure_Date,' ')-1)),3) & Right(Measure_Date, 4),'MMMYYYY'),'DD.MM.YYYY') as Datee,
          
      Value,
           'primaryClubVisits'
      as Source,
          
      filename() as FilenameFakta

         Resident Temp;
        
      DROP Table Temp;

      The Excel file contains values from this year and the last year. The heading in the Excel file are the same except that the heading changing from "year" for each year and monthly for each month. I'm using the headings to make a date, as you can see from the script.  That's why I am using a temp file to load everything (using the load *). Otherwise I will not be able to load the changing headings.

       

      The problem starts when I will receive files in 2014 when the headings contains 2013 and 2014. Meaning that I will have duplicated values for 2013 when we are in 2014. And duplicated values when we are 2015 due to the 2014 values.

       

      Any suggestion?

       

      Thanks

       

       

        • Re: Loading from Excel files
          Clever Anjos

          Maybe you should rewrite your expressions using "SubField"

          Left(Measure_Date, Index(Measure_Date,' ', SubStringCount(Measure_Date, ' ')-1)-1) as Measure

          Date(Date#(Left(TextBetween(Measure_Date,' ', ' ', (SubStringCount(Measure_Date,' ')-1)),3) & Right(Measure_Date, 4),'MMMYYYY'),'DD.MM.YYYY') as Datee,

          turns

          subfield(Measure_Date,' ',1) as Measure

          date(date#(Subfield(Measure_Date,' ',2) & Subfield(Measure_Date,' ',3),'MMMYYYY'),'MMM/YYYY') as Datee

          • Re: Loading from Excel files
            Gysbert Wassenaar

            The obvious solution is to make sure the excel files themselves don't include the year and month number in the headers. Clean up the thrash at the source. Probably won't happen. So, assuming the columns are at least always in the same place, try loading with No Labels and skip the first row. Then rename the fields yourself:

             

            Load

            @1 as Dim1,

            @2 as Dim2,

            @3 as Year,
            @4 as Month,
            @5 as Amount

            From *.xls (biff, no labels, table is Sheet1$, filters(

            Remove(Row, Pos(Top, 1))

            ));