Solved

.txt Formatting 2

Posted on 2014-01-02
12
206 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:itjockey
  • 7
  • 4
12 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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:itjockey
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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:itjockey
Comment Utility
Row in workbook....
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Author Comment

by:itjockey
Comment Utility
just asking you are on this question?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
Try this
Source.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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:itjockey
Comment Utility
yes its working perfectly  ...thanks Mr.als315...................
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
will any one look my open question..pls.....Math2



just requesting

thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
awesome working fantastic ....thank you so much ...I had converted 4000 odd .txt file & uploaded to charting software..........thanks  :) :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now