• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

.txt Formatting 2

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
Naresh Patel
Asked:
Naresh Patel
  • 7
  • 4
1 Solution
 
gowflowCommented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Naresh PatelTraderAuthor Commented:
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
 
gowflowCommented:
ok fine.
So we loop thru rows in the workbook or in the directory ?
Am a bit lost here.
gowflow
0
 
Naresh PatelTraderAuthor Commented:
Row in workbook....
0
 
Naresh PatelTraderAuthor Commented:
just asking you are on this question?
0
 
als315Commented:
Try this
Source.xlsm
0
 
gowflowCommented:
Here you are !!! that's what it called 'You miss it when you take your kids to foot' !!!
Enjoy

@als315 good Job !
gowflow
0
 
Naresh PatelTraderAuthor Commented:
yes its working perfectly  ...thanks Mr.als315...................
0
 
Naresh PatelTraderAuthor Commented:
will any one look my open question..pls.....Math2



just requesting

thanks
0
 
Naresh PatelTraderAuthor Commented:
awesome working fantastic ....thank you so much ...I had converted 4000 odd .txt file & uploaded to charting software..........thanks  :) :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now