12 Replies Latest reply: May 31, 2012 9:27 AM by mattlundgren RSS

    Issues reloading script using a loop

      Hi all,

       

      I am trying to reload a csv using the below but nothing seems to happen and it quickly reverts to the Sheet Properties with no column attributes.

       

      Can one of you kind people please take a look and tell me where I am going wrong?

       

      My file name is called: "201106 TA_OG_Broker_Raw_Data_Report.csv" and it goes to "201204 TA_OG_Broker_Raw_Data_Report.csv"

       

       

      set StartDate = '01/06/2011';
       EndDate = addmonths(today(),-1);
      let a = Interval(month(EndDate)-month(StartDate),'m');
      for i = 0 to a;
      let OmgeoFileName = Date(addmonths(StartDate,i),'YYYY')&Date(addmonths(StartDate,i),'MM')&' TA_OG_Broker_Raw_Data_Report.csv';
      let OmgeoFileFullPath = ('[O:\MI\2012\OMGEO Downloads - NEW\'&OmgeoFileName&']');
      Directory;
      LOAD [Report Period Header], 
        Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],
        Year([Report Period Header]) as Year,
         Month([Report Period Header]) as Month,
           [Current Status], 
           [Buy / Sell], 
           [Security Code], 
           [Number of Shares], 
           [Trading Currency Code], 
           [Number of Allocations], 
           [Settlement Date], 
           [Trade Date and Time], 
           [SDC Indicator], 
           [SDA Indicator], 
           [Counterparty Institution Acronym]
      FROM
      $(OmgeoFileFullPath)
      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
      NEXT 
      
      

       

      Many thanks in advance,

      Matt

        • Issues reloading script using a loop
          Dave Riley

          Hi Matt,

           

          Rather than looping, can you use a wildcard in the filename? Such as ...

           

          Directory;

          LOAD [Report Period Header],

          ...

               [Counterparty Institution Acronym]

          FROM

          [* TA_OG_Broker_Raw_Data_Report.csv]

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          flipside

            • Re: Issues reloading script using a loop

              Hi flipside, thanks for your response.

               

              Do I just need to change this:

               

               

              FROM
              $(OmgeoFileFullPath)
              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
              NEXT 
              
              

               

              to this:

               

               

              FROM
              [* TA_OG_Broker_Raw_Data_Report.csv]
              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
              

              If so, that doesn't seem to be working.

               

              Many thanks,

              Matt

                • Issues reloading script using a loop
                  Henric Cronström

                  You can use wildcards in the file name, but I do not recommend it. It causes problems as soon as you use preceding loads.

                   

                  Instead I think you should fix your loop. Your "Let OmgeoFileFullPath = ..." is incorrect. Use this instead:

                   

                  let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & $(OmgeoFileName) ;

                   

                  Also, change your Load statement from "FROM $(OmgeoFileFullPath) (txt..." to "FROM [$(OmgeoFileFullPath)] (txt..."

                   

                  HIC

                    • Re: Issues reloading script using a loop

                       

                      Thank you MIC but this doesn't work either.  I've copied the full script again in case I am missing anything..

                       

                       

                      set StartDate = '01/06/2011';
                       EndDate = addmonths(today(),-1);
                      let a = Interval(month(EndDate)-month(StartDate),'m');
                      for i = 0 to a;
                      let OmgeoFileName = Date(addmonths(StartDate,i),'YYYY')&Date(addmonths(StartDate,i),'MM')&' TA_OG_Broker_Raw_Data_Report.csv';
                      let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & $(OmgeoFileName);
                      
                      Directory;
                      LOAD [Report Period Header], 
                        Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],
                        Year([Report Period Header]) as Year,
                         Month([Report Period Header]) as Month,
                           [Current Status], 
                           [Buy / Sell], 
                           [Security Code], 
                           [Number of Shares], 
                           [Trading Currency Code], 
                           [Number of Allocations], 
                           [Settlement Date], 
                           [Trade Date and Time], 
                           [SDC Indicator], 
                           [SDA Indicator], 
                           [Counterparty Institution Acronym]
                      FROM
                      [$(OmgeoFileFullPath)] 
                      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                      NEXT
                      
                      Many thanks,
                      Matt
                      
                        • Re: Issues reloading script using a loop
                          Henric Cronström

                          Sorry, I missed a couple of single quotes...

                           

                          One of the following should work:

                          let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & '$(OmgeoFileName)' ;

                          set OmgeoFileFullPath = O:\MI\2012\OMGEO Downloads - NEW\$(OmgeoFileName) ;

                           

                          If not, I suggest you run the script in the debugger and check that you really have the right file names.

                           

                          HIC

                          • Re: Issues reloading script using a loop
                            Dave Riley

                            Matt,

                             

                             

                            Another tip if you don't want to use the debugger, just add a msgbox line to check your variables during the load (but be careful using this in long loops - you might have a lot of key presses!!)

                             

                            eg Add this after your 'Let a = ...' line

                             

                            x = msgbox($(a));

                             

                            I got a value of -2880 in my test!!

                             

                            flipside

                             

                             

                             

                            EDIT: Also remember to remove or comment it out when testing finished.

                              • Re: Issues reloading script using a loop

                                Hi Guys,

                                 

                                Many thanks for both your feedback however I am still getting an issue.  I have run it through the debugger and it looks like it's s file path issue:

                                 

                                 

                                OmgeoFileFullPath <NULL>
                                OmgeoFileName <NULL> 
                                
                                

                                 

                                 

                                However, I know the filepath is exactly right which is why I am even more baffled.

                                 

                                Just to put this into context, I get issues reloading using multiple files (I mean importing each file one by one).  So I just wanted to get one load to prevent this problem.

                                 

                                Do you know of any alternitives (btw I am new to all this and only just learning the QV code)

                                 

                                Many thanks again.

                                Matt

                                  • Re: Issues reloading script using a loop
                                    Henric Cronström

                                    Yup, I see another problem: You StartDate is a string and not a number, and then your month(StartDate) will fail.

                                     

                                    Never mind. Try this instead. It's simpler:

                                     

                                    for each vFileName in FileList('O:\MI\2012\OMGEO Downloads - NEW\*.csv')

                                    LOAD [Report Period Header],

                                      Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],

                                      Year([Report Period Header]) as Year,

                                       Month([Report Period Header]) as Month,

                                                 '$(vFileName)' as FileName,

                                         [Current Status],

                                         [Buy / Sell],

                                         [Security Code],

                                         [Number of Shares],

                                         [Trading Currency Code],

                                         [Number of Allocations],

                                         [Settlement Date],

                                         [Trade Date and Time],

                                         [SDC Indicator],

                                         [SDA Indicator],

                                         [Counterparty Institution Acronym]

                                    FROM [$(vFileName)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                    NEXT

                                     

                                    Then you can extract the date from the field FileName using string functions (subfield, left, right, etc.)

                                     

                                    HIC

                                    • Re: Issues reloading script using a loop

                                      Many thanks Henric for replying with the below message

                                       

                                       

                                      Yup, I see another problem: You StartDate is a string and not a number, and then your month(StartDate) will fail.
                                      
                                      Never mind. Try this instead. It's simpler:
                                      
                                      for each vFileName in FileList('O:\MI\2012\OMGEO Downloads - NEW\*.csv') 
                                      LOAD [Report Period Header], 
                                        Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],
                                        Year([Report Period Header]) as Year,
                                         Month([Report Period Header]) as Month,
                                                   '$(vFileName)' as FileName,
                                           [Current Status], 
                                           [Buy / Sell], 
                                           [Security Code], 
                                           [Number of Shares], 
                                           [Trading Currency Code], 
                                           [Number of Allocations], 
                                           [Settlement Date], 
                                           [Trade Date and Time], 
                                           [SDC Indicator], 
                                           [SDA Indicator], 
                                           [Counterparty Institution Acronym]
                                      FROM [$(vFileName)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                                      NEXT
                                      
                                      Then you can extract the date from the field FileName using string functions (subfield, left, right, etc.)
                                      
                                      
                                      
                                • Issues reloading script using a loop
                                  Dave Riley

                                  Hi Henric,

                                   

                                  Thanks for the info, I wasn't aware of any issues with wildcards in loads - I haven't had any myself. What sort of problems does it cause?

                                   

                                  Matt >  I was also going to add that I don't think the loop logic works, because your start value is Month(01/06/2011) = 6 and your end value will be 4.  Therefore you are trying to loop from 6 to 4.  Also, is Interval the right function, I thought this was just for time intervals (up to 'day' value)?

                                   

                                  If it's just a case of avoiding Preceding Loads, then the wildcard load should work (you don't need the loop) but with a where clause added ...

                                   

                                  Directory;

                                  LOAD [Report Period Header],

                                  ...

                                       [Counterparty Institution Acronym]

                                  FROM

                                  [* TA_OG_Broker_Raw_Data_Report.csv]

                                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                                  where left(FileName(),6)>='$(StartDate)'

                                  and left(FileName(),6)<'$(EndDate)';

                                   

                                  The variable StartDate would need to be generated to equate to 201106 and EndDate to 201205 (CurrentMonth).

                                   

                                  flipside

                                    • Re: Issues reloading script using a loop
                                      Henric Cronström

                                      Wildcards in file names work fine as long as you have a single Load, e.g.

                                      Load ... from [*.csv] ;

                                       

                                      But as soon as you put a preceding load in front of it, then you run into problems:

                                      Load ...;

                                      Load ... from [*.csv] ;

                                       

                                      The second Load is piped into the first, i.e. the first Load becomes a transformation and filter layer. I use this construction a lot, because then I can make a step-wise transformation of data. But I cannot use wild cards in the file name...

                                       

                                      HIC