How do I read data from multiple .txt files?

[butterfly2000]

I have a file folder titled "Data" in this folder are 10 .txt files. The data in each file is of the sort:

AAPL 11/08/2005 59.95 60.38 59.10 59.90
AAPL 11/09/2005 60.00 61.21 60.00 60.11
AAPL 11/10/2005 60.64 61.20 59.01 61.18
AAPL 11/11/2005 61.54 62.11 61.34 61.54
AAPL 11/14/2005 61.54 61.98 60.91 61.45
AAPL 11/15/2005 61.60 63.08 61.46 62.28
AAPL 11/16/2005 63.15 65.06 63.09 64.95
AAPL 11/17/2005 65.59 65.88 64.25 64.52

(Stock data... there are 65 lines per file)

I need to:

(a) Plug this data into the worksheet called "datacruncher"
(b) Grab the output in column c, row 3 (which is the result from a bunch of other calculations done on the data) and move this answer, along with the stock ticker, on to a worksheet called "results"
(c) Then I need to repeat this (a, b steps) with the other 9 .txt files). Thus, at the end of running the macro, I will have 10 rows complete on the "results" worksheet, each two columns-- column a with the ticker, column b with the c3 result from the "datacruncher" worksheet.

I have tried to building this macro by using the recorder, because honeslty-- even though I have a rudimentary knowledge of building my own macros using the vba editor... I cannot figure out for the life of me how to get the data in from the txt file and repeat.

Hoping someone can help me out!

Thx

 

[acw]

Hi

1) Where is the text data going? Sheet and cell reference
2) What are the names of the text files?
3) Where in results is the output going?
4) Where is the tickler code going to be on datacruncher?


Tony

 

[babycody]

Copy and paste all the information from the .txt file into a cell that doesn't have anything to the right of it. Go to Data>Text to Columns. Now select delimited and click next. Tick the box next to space and tab since one of these appears to be your delimiter and click next. Now you will choose a destination for the delimited text. Choose the top left cell of the range you wish the information to be in. Finally click finish unless you wish to select a format which you might since you have dates in your second delimited column. Hope this helps.

 

[butterfly2000]

Hi

1) Where is the text data going? Sheet and cell reference
2) What are the names of the text files?
3) Where in results is the output going?
4) Where is the tickler code going to be on datacruncher?


Tony

Hi Tony. Here is the answer to these questions:

(1) The text data is going into cells a3...f68 on "datacruncher"
(2) The name of the txt file is the Ticker name, so the AAPL data is in a file called "APPL" and the MSFT data is in a file titled "MSFT" -- All of these txt files are in a folder on my desktop called "Data"
(3) Once all the data is in "datacruncher" another worksheet actually does the heavy lifting and figures out what I want to know. So, for example (because it is more complicated than this) lets say I am trying to figure out the highest price of the closing prices; I have the equation "=MAX('datacruncher'!f3:f15)" and this equation is cell c3 on the sheet "heavylifting". Here is the kicker: I want this result, and the ticker name (which is repeated on a3...a68, on data cruncher) AND this c3 result from "heavylifting" to be written to A1...B1 on "results" -- Then I want to open the next txt file, plug in the #'s on to "datacruncher" and have those results (the ticker and the C3 result) kick out to A2...B2 on the sheet "results"

Thus, the end result for ten txt files will be a results page that lists 10 tickers on the 1st 10 rows, column A on the worksheet "results" and column B (b1...b10) will have that c3 result, for each individual ticker.

(4) I do not understand this question. Sorry, I am really trying to figure out excel and even have been working through John Walkenback (sp?) excel VBA book, but I am still such a rookie!

Thanks for you pointers!

 

[butterfly2000]

Copy and paste all the information from the .txt file into a cell that doesn't have anything to the right of it. Go to Data>Text to Columns. Now select delimited and click next. Tick the box next to space and tab since one of these appears to be your delimiter and click next. Now you will choose a destination for the delimited text. Choose the top left cell of the range you wish the information to be in. Finally click finish unless you wish to select a format which you might since you have dates in your second delimited column. Hope this helps.

Hi babycody!

Yes, I can do this. My problem is I need it to be automated as ultimately there will be many more than 10 data files and I want to run it everyday. Ideally, I can push one macro botton, and just end up with all the results kicked out for all the data files. Doing it manually like this would be a killer! Thanks for your input though!

 

[acw]

Hi

This may get you going.

Sub bbb()
 ChDrive "C"
 ChDir "C:\temp"
 filess = Dir("*.txt")
 Sheets("datacruncher").Select
 While Not filess = ""
  Sheets("datacruncher").Range("a3:f68").ClearContents
  With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp\" & filess, _
        Destination:=Range("A3"))
        .Name = "fred_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    outrow = Sheets("results").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    Sheets("results").Cells(outrow, "A").Value = WorksheetFunction.Substitute(filess, ".txt", "")
    Sheets("results").Cells(outrow, "B").Value = Sheets("heavylifting").Range("c3").Value
    filess = Dir()
 Wend
End Sub

You will have to set up
1) Your drive
2) your directory
3) the connection in the line
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\temp\" & filess,
4) the options for the querytable depending on how the data is to be parsed.

Probably other things, but all I can see quickly.

HTH

Tony

 

[butterfly2000]

Tony,

Thanks so much! I will play around with this and see if I can get it going! It is extremely kind of you to put so much thought and effort into a response. I am really blown-away

I'll post in this thread if I hit any snags or if I get it working perfectly (fingers crossed!!)

-r