?
Solved

.txt Formatting 2

Posted on 2014-01-02
12
Medium Priority
?
243 Views
Last Modified: 2014-01-04
Hi Experts,

Need Code for .txt file Modification. I have say 100 .txt file in one folder want to change something in that.

Process.  On Button Click.

01.

Very first  file of that folder location & find that .txt file name in workbook – Source –Sheet – Data. In column B. If not found then go for next .txt file. If found then need to modify very first line of .txt file from “<TICKER>, <PER>, <DTYYYYMMDD>, <TIME>, <OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>”
to
“<TICKER>, <NAME>, <PER>,<DTYYYYMMDD>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>”

i.e. “<NAME> added & <TIME> removed.

02.

Whole data  in text file is in  this manner “xyz,D,20050422,000000,59,59,47.78,49.05,24266946,0”

Need to add <TICKER> in that & Remove <TIME> from data i.e. “A,xyz,D,20050422,59,59,47.78,49.05,24266946,0
ticker found i.e. “A” from source file sheet data column C which is matching with its name in column B.

03.

Sava this new .txt file with its name as Ticker in same location.
i.e.  now “xyz.txt” to “A.txt” move to next till end.


See attached Files.

Thanks
Source.xlsm
xyz.txt
A.txt
0
Comment
Question by:Naresh Patel
  • 7
  • 4
12 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39753343
ok let me recap my understanding here:

You want us to have a macro in the workbook Source.xlsm that you posted say this macro is activated by a button Called Update. When you activate this button it will:

see data in sheet Data and pick Row2 and locate that file (we need to know in what directory the full path)

if that file contain what you explained above then process ... save on the hard disk with the new name .. and move to the next file that is in Row3 find the file ....
etc.. till end of rows in Sheet Data

Is my understanding correct ? if yes then need you to provide the full path of that folder to hard coded in the macro so it is easier to move on.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39753412
First Happy New Year Sir,

Now Back To Question.

see data in sheet Data and pick Row2 and locate that file (we need to know in what directory the full path)

C:\Users\Administrator\Downloads

if that file contain what you explained above then process ... save on the hard disk with the new name .. and move to the next file that is in Row3 find the file ....
etc.. till end of rows in Sheet Data

Yes as you said -  little change in logic. May be more convenient.
WB - Source - WS - Data - Start From Column B Row 2 (B2)
Find Name in "C:\Users\Administrator\Downloads" if not there then go for next i.e.Cell B3.
if there then process as described. & Save it with new name which is next column same Row . example there is "xyz" in cell B2 - search in "C:\Users\Administrator\Downloads" & it is there then process as described -  save in same location with new name "A" as it is next to xyz i.e. Cell C2.

Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39753422
Yes that is what I understood !!! no change for me it was obvious when you loop thru the workbook then you use the data that is in the workbook.

I had first once I read your post thought I had to loop thru the directory .... but reading the post again and again noticed that you were referring to the workbook then it was obvious that we need to only loop thru the items that are in the workbook and not ALL THE FILES IN THE DIRECTORY as you first mentioned.

You need to be careful at this is in your workbook not all the files are mentioned there then you will miss some in the process.

Maybe now you need to review your logic. My question is: How do you update your workbook ? Manually or automatically ? if manually then you may run into the situation that you have missing info in the workbook that exist on the hard drive and then you have a problem.

If you want to make sure you process ALL THE FILES then the opposite should be done:

Loop thru all the files in the directory
file picked from the directory >> look to see its data in the sheet Data locate the row then check what you require about first line etc....

If the file is not found in the workbook THEN Maybe a line in the workbook should be added at the end telling you file so and so was not updated as not in the workbook

Let me know
gowflow
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 39753467
Sir Gowflow,

This is  only one time Process as I had tried with actual .txt file say "xyz.txt" to upload in charting software but it report me some error so need to change little bit in format. this all files have data till 31 Dec 2013. after doing this  I have different kind of text file which contain all ticker or name in one file with  particular date i.e. I have .txt file viz 01jan20141 & 2jan2014 & now 03jan2013.txt which have all tickers in one file.


Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39753756
ok fine.
So we loop thru rows in the workbook or in the directory ?
Am a bit lost here.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39753820
Row in workbook....
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39754454
just asking you are on this question?
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 39754464
Try this
Source.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39754492
Here you are !!! that's what it called 'You miss it when you take your kids to foot' !!!
Enjoy

@als315 good Job !
gowflow
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39754618
yes its working perfectly  ...thanks Mr.als315...................
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39754683
will any one look my open question..pls.....Math2



just requesting

thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39755845
awesome working fantastic ....thank you so much ...I had converted 4000 odd .txt file & uploaded to charting software..........thanks  :) :)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
I came across an unsolved Outlook issue and here is my solution.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question