Issues reloading script using a loop

[#1]

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 = ('');
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

[#2]

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

[hic]

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

[#4]

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.)