Naresh Patel
asked on
FO Formatting
Hi Experts,
I have on.csv file in which I have to change data programmatically. Please help me out with VBA.
Here are the steps.
". Put text values to this dates "I" "II" "III" in Oldest to newest. So 29-Jan-15 = I , 26-Feb-15 = II & 26-Mar-15 = III. apply this values to respective dates in all column C.
Need to execute this from other WB & above file location is "D:\AmiBroker Data\NSE\FO\". there are numbers of files.
Need to Execute for all files but not now as this is part of process there are follow up questions.
See attached
Thanks
fo06JAN2015bhav.csv
After.csv
I have on.csv file in which I have to change data programmatically. Please help me out with VBA.
Here are the steps.
01.
In attached .csv file Find "NIFTY" in column B with criteria "XX" in column E. there are 3 matches with this criteria.02.
Now we have 3 dates in column C as per above criteria i.e. "29-Jan-15" "26-Feb-15" "26-Mar-15". Put text values to this dates "I" "II" "III" in Oldest to newest. So 29-Jan-15 = I , 26-Feb-15 = II & 26-Mar-15 = III. apply this values to respective dates in all column C.
03.
Delete all rows which doesn't match values "I" "II" "III" in column CNeed to execute this from other WB & above file location is "D:\AmiBroker Data\NSE\FO\". there are numbers of files.
Need to Execute for all files but not now as this is part of process there are follow up questions.
See attached
Thanks
fo06JAN2015bhav.csv
After.csv
An other question. In the example you only had 3 dates and applied I, II, III would it happens so that:
1) You can have several rows of the same date ? like several I or II ?
2) You can have more than 3 dates ? like 5 or 6 dates ? if yes then how do we name them roman ? I, II, III, IV, V etc ... ?
3) Can we name them say A, B C etc ... instead of roman ?
4) You want to delete all rows that does not meet these new naming including the header or keeping the header ?
gowflow
1) You can have several rows of the same date ? like several I or II ?
2) You can have more than 3 dates ? like 5 or 6 dates ? if yes then how do we name them roman ? I, II, III, IV, V etc ... ?
3) Can we name them say A, B C etc ... instead of roman ?
4) You want to delete all rows that does not meet these new naming including the header or keeping the header ?
gowflow
ASKER
yes Sir for all files but still something is still left which is for next follow-up question. So say this is part A next question part B. part A+B formatting for all files in directory.
Thanks
Thanks
pls see my last comment but ignore only point 1) as I just realized that you want to rename all dates like 29-Jan-15 to I and not only this record !
Waiting for answer on remaining points.
gowflow
Waiting for answer on remaining points.
gowflow
ASKER
Last post is in reply of your second last post.
1) Yes Same dates need to assign I or II or III.
2) There will be only 3 dates with match criteria i.e. NIFTY XX.
3) If in future it creates problem then 1 2 3 will be fine.
4) Sorry my mistake - Keep heading.
Thanks
1) Yes Same dates need to assign I or II or III.
2) There will be only 3 dates with match criteria i.e. NIFTY XX.
3) If in future it creates problem then 1 2 3 will be fine.
4) Sorry my mistake - Keep heading.
Thanks
No problem if only 3 different dates then we can use I, II, III but if you have variable dates like 10 or 15 different dates then it is a 'P A I N' to start counting in roman !!! :)
gowflow
gowflow
ASKER
yes but I II III will be in older to newer date i.e. I will be oldest date and III will be newest date.
Thanks
Thanks
Yes sure no problem. Older to newest do you have any problem if the data is sorted by I, II, III ??? Like all I then all II etc...
Last but no least, we override existing file right ? like we save the new changes and replace the existing file ?
gowflow
Last but no least, we override existing file right ? like we save the new changes and replace the existing file ?
gowflow
ASKER
1 min Sir.
ASKER
Ultimately I need to create .txt file after deleting actual file.
in attached look in the column Q there is formula which combine cell values. so above procedure then formula then copy column Q and create .txt file & delete original file. this is my next follow up question. .txt file name as actual file name.
See attached
After.xlsx
Final-File.txt
in attached look in the column Q there is formula which combine cell values. so above procedure then formula then copy column Q and create .txt file & delete original file. this is my next follow up question. .txt file name as actual file name.
See attached
After.xlsx
Final-File.txt
ASKER
Sorry for delay as I created formula and save in .csv format so after saving formula gone and displayed all values in column Q as text. so I had created again and save as .xlsx file.
Thanks
Thanks
Sorry I do not appreciate when you come up with something new just in the middle of a question. Sorry too late as I already developed the macro as per your initial request.
When I came to compare my result based on the file you posted fo06JAN2015bhav.csv which have 86 rows I noticed that your After.csv file have: 26470 rows !!! how can 86 rows become 26470 you must have posted the wrong file !!!
Pls checkout the fo06JAN2015bhav.csv file you posted and make out a corresponding After.csv and posted so I can make sure all is fine and then I will post my solution.
We can look at other request in subsequent questions if you like.
gowflow
When I came to compare my result based on the file you posted fo06JAN2015bhav.csv which have 86 rows I noticed that your After.csv file have: 26470 rows !!! how can 86 rows become 26470 you must have posted the wrong file !!!
Pls checkout the fo06JAN2015bhav.csv file you posted and make out a corresponding After.csv and posted so I can make sure all is fine and then I will post my solution.
We can look at other request in subsequent questions if you like.
gowflow
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked Perfect. May I ask Follow Up New Question?
ASKER
Perfect.
Yes pls go ahead.
gowflow
gowflow
ASKER
Posting link over here in 10 min.
Thanks
Thanks
ASKER
gowflow