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.

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

Jacques Geday

This should be for all files in the directory ?? Sorry maybe you mentioned it but faster to ask.
gowflow

Jacques Geday

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

Naresh Patel

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

Jacques Geday

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

Naresh Patel

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

Jacques Geday

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

Naresh Patel

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

Jacques Geday

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

Naresh Patel

ASKER

1 min Sir.

Naresh Patel

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

Naresh Patel

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

Jacques Geday

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

Naresh Patel

ASKER

See attached fo06jan2015(1).xlsx explained how come 26469 rows.

Thanks
fo06JAN2015bhav--1-.xlsx

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

ASKER

Worked Perfect. May I ask Follow Up New Question?