Solved

.txt Formatting 2

Posted on 2014-01-02
12
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 30

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 30

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 30

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 500 total points
ID: 39754464
Try this
Source.xlsm
0
 
LVL 30

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 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