Solved

.txt Formatting 2

Posted on 2014-01-02
12
225 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
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:itjockey
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 29

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Author Comment

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

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:itjockey
ID: 39753820
Row in workbook....
0
 
LVL 8

Author Comment

by:itjockey
ID: 39754454
just asking you are on this question?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39754464
Try this
Source.xlsm
0
 
LVL 29

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:itjockey
ID: 39754618
yes its working perfectly  ...thanks Mr.als315...................
0
 
LVL 8

Author Comment

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



just requesting

thanks
0
 
LVL 8

Author Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

776 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