Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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
I just saw something else which might cause a problem:
let a = Interval(month(EndDate)-month(StartDate),'m');
The Interval function takes a timestamp as parameter and you have months. That you format as minutes.
HIC
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
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.