Importing Text Data

[mfx_krypt]

Ok, I have ran into a large problem that I can't figure out. We have a data machine that dumps a file at the end of the day with all the data it has recorded for the day. It is decimal delimited format and I have about 100 of these files with another one added each day. I need to condense all this into one file that you can look at and find information quickly. What I need to do is have each row represent a day, and all the information from that day goes into the respective columns for that day(Row). I originally thought I'd be able to to this through the use of macros but after recording my own macro I have found that there is no way to automate it to execute the same commands to multiple files. I would appreciate any ideas anyone could offer because my current situation with seperate files is very inefficient and harder to search through.

Thanks,
Keith

[Bernie Deitrick]

Keith,

Do the individual files open as single row spreadsheets when opened by Excel?

HTH,
Bernie
MS Excel MVP


"mfx_krypt" <[email protected]> wrote in message
news:[email protected]...
>
> Ok, I have ran into a large problem that I can't figure out. We have a
> data machine that dumps a file at the end of the day with all the data
> it has recorded for the day. It is decimal delimited format and I have
> about 100 of these files with another one added each day. I need to
> condense all this into one file that you can look at and find
> information quickly. What I need to do is have each row represent a
> day, and all the information from that day goes into the respective
> columns for that day(Row). I originally thought I'd be able to to this
> through the use of macros but after recording my own macro I have found
> that there is no way to automate it to execute the same commands to
> multiple files. I would appreciate any ideas anyone could offer because
> my current situation with seperate files is very inefficient and harder
> to search through.
>
> Thanks,
> Keith
>
>
> --
> mfx_krypt
> ------------------------------------------------------------------------
> mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
> View this thread: http://www.excelforum.com/showthread...hreadid=469529
>

[mfx_krypt]

No, that would have made this alot easier to do. Each file consists of about 300 rows, which I need to average before inputting them into the condensed spreadsheet.

[Bernie Deitrick]

Average in what way, and where does the resulting data go? Get specific.

HTH,
Bernie
MS Excel MVP


"mfx_krypt" <[email protected]> wrote in message
news:[email protected]...
>
> No, that would have made this alot easier to do. Each file consists of
> about 300 rows, which I need to average before inputting them into the
> condensed spreadsheet.
>
>
> --
> mfx_krypt
> ------------------------------------------------------------------------
> mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
> View this thread: http://www.excelforum.com/showthread...hreadid=469529
>

[Dave Peterson]

Just to add to Bernie's questions...

What do the input files look like. Decimal delimited sounds kind of vague
(well, to me).

Is it one value per line--just the number?
Is it two values per line--maybe a date and number?

Where are all the files located--in a dedicated folder with nothing else in
them. It might make it easier if that could be done. Then the macro could open
each file it finds in that folder.

How do you tell the date of the data--is it the name of the file????

Bernie Deitrick wrote:
>
> Average in what way, and where does the resulting data go? Get specific.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "mfx_krypt" <[email protected]> wrote in message
> news:[email protected]...
> >
> > No, that would have made this alot easier to do. Each file consists of
> > about 300 rows, which I need to average before inputting them into the
> > condensed spreadsheet.
> >
> >
> > --
> > mfx_krypt
> > ------------------------------------------------------------------------
> > mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
> > View this thread: http://www.excelforum.com/showthread...hreadid=469529
> >

--

Dave Peterson

[mfx_krypt]

Ok, sorry for any confusion. The format of the text file is like this

09/22/05 12:30:21,1.34,3.27,0.345,0.0043,2.34
09/22/05 12:30:26,1.33,3.27,0.341,0.0050,2.39
09/22/05 12:30:31,1.45,3.31,0.401,0.0046,2.29

And it goes on, about 15 values per row and about 300 rows, each column needs to be averaged for that day - so for example you would average 1.34,1.33,1.45 and get 1.373, you would than input that value into the next spreadsheet and average the next column.

Also to answer the other question, file formats are in terms of dates - something like 092205.HV2 (it has hv2 extension but its just a decimal delimited text file). And yes they are in a dedicated folder.

[Bernie Deitrick]

mfx_krypt,

Try the macro below. Change the folder path to reflect the dedicated folder location.

HTH,
Bernie
MS Excel MVP

Sub CombineHV2Files()
Dim myCell As Range
Dim myName As String
Dim i As Integer

With Application.FileSearch
..NewSearch
'Change this to your directory
..LookIn = "C:\Documents and Settings\HV2 Files"
..Filename = "*.HV2"
..SearchSubFolders = False
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True
Set myCell = Range("B65536").End(xlUp)(2)
With myCell
..FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
..AutoFill Destination:=Range(myCell, _
myCell.Offset(-1, 0).End(xlToRight)(2)), _
Type:=xlFillDefault
End With
myName = ActiveSheet.Name
Cells(myCell.Row, 1).Value = Left(myName, 2) & _
"/" & Mid(myName, 3, 2) & "/" & Right(myName, 2)
myCell.EntireRow.Copy
With ThisWorkbook.Worksheets(1).Range("A65536"). _
End(xlUp)(2).EntireRow
..PasteSpecial Paste:=xlPasteValues
End With
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True

Next i
Else
MsgBox "There were no files found."
End If
End With

Cells.NumberFormat = "0.0000"
Range("A:A").NumberFormat = "mm/dd/yy"
Cells.EntireColumn.AutoFit
End Sub



"mfx_krypt" <[email protected]> wrote in message
news:[email protected]...
>
> Ok, sorry for any confusion. The format of the text file is like this
>
> 09/22/05 12:30:21,1.34,3.27,0.345,0.0043,2.34
> 09/22/05 12:30:26,1.33,3.27,0.341,0.0050,2.39
> 09/22/05 12:30:31,1.45,3.31,0.401,0.0046,2.29
>
> And it goes on, about 15 values per row and about 300 rows, each column
> needs to be averaged for that day - so for example you would average
> 1.34,1.33,1.45 and get 1.373, you would than input that value into the
> next spreadsheet and average the next column.
>
> Also to answer the other question, file formats are in terms of dates -
> something like 092205.HV2 (it has hv2 extension but its just a decimal
> delimited text file). And yes they are in a dedicated folder.
>
>
> --
> mfx_krypt
> ------------------------------------------------------------------------
> mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
> View this thread: http://www.excelforum.com/showthread...hreadid=469529
>

[mfx_krypt]

Thank you very much for this macro, I just have two questions about it. First it is showing dates in the first column as 05/09/AS for all cells, it isn't showing the day of the month. It probably just isnt reading the format the date is in correctly. The files names look like this: 050920AS.HV2, 050921AS.HV2. And in column A it is stored like this: 9/20/2005 23:56, ect. I tired lookin through the code but couldnt figure out what I needed to change to get the date to show correctly.

Also, is it possible to specify which columns it averages and imports into the worksheet, I would like to leave out a few columns but if this would complicate things too much than I'll just hide the ones I don't need. Thanks for any info and thanks again for the macro.

Keith

[Bernie Deitrick]

Keith,

You had said that the files were named "something like 092205.HV2" which was mmddyy.HV2

When Excel opens that file, the tab name becomes 092205. Since the file is actually named "
050920AS.HV2", which is yymmddAS.HV2, then the tab name becomes 050920AS.

So, you need to change the lines:

myName = ActiveSheet.Name
Cells(myCell.Row, 1).Value = Left(myName, 2) & _
"/" & Mid(myName, 3, 2) & "/" & Right(myName, 2)

to

myName = Left(ActiveSheet.Name,6)
Cells(myCell.Row, 1).Value = Mid(myName, 3, 2) & _
"/" & Right(myName, 2) & "/" & Left(myName, 2)

You may need to rearrange some of those depending on your date system.

As for leaving out columns, the best thing would be to delete the unwanted columns at the end, with
a command like:

Range("B1,D1,F1").EntireColumn.Delete

which will delete columns B, D, and F (this is just an example....).

HTH,
Bernie
MS Excel MVP


"mfx_krypt" <[email protected]> wrote in message
news:[email protected]...
>
> Thank you very much for this macro, I just have two questions about it.
> First it is showing dates in the first column as 05/09/AS for all
> cells, it isn't showing the day of the month. It probably just isnt
> reading the format the date is in correctly. The files names look like
> this: 050920AS.HV2, 050921AS.HV2. And in column A it is stored like
> this: 9/20/2005 23:56, ect. I tired lookin through the code but couldnt
> figure out what I needed to change to get the date to show correctly.
>
> Also, is it possible to specify which columns it averages and imports
> into the worksheet, I would like to leave out a few columns but if this
> would complicate things too much than I'll just hide the ones I don't
> need. Thanks for any info and thanks again for the macro.
>
> Keith
>
>
> --
> mfx_krypt
> ------------------------------------------------------------------------
> mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
> View this thread: http://www.excelforum.com/showthread...hreadid=469529
>

[mfx_krypt]

Ok, thanks for the advice. I really appreciate your help this is going to work perfectly for my situation.

-Keith

[mfx_krypt]

Sorry last question, I am trying to add some code to this so that with each file it opens it also opens a file with a .CV1 extension, averages the values and adds them onto the row of values stored in myCell.

I tried doing it but I can't figure it out quite yet...this vba is getting addicting though.

Sub Load_HV2()
Dim myCell As Range
Dim myName As String
Dim i As Integer

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Test"
.Filename = "*.HV2"
.SearchSubFolders = False
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True
Range("B1, G1, H1, K1, L1, M1, N1, P1, S1, T1, U1, V1, W1, X1, Y1, Z1, AA1, AB1, AC1, AD1, AE1, AF1, AG1").EntireColumn.Delete
Columns("D:D").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Set myCell = Range("B65536").End(xlUp)(2)
With myCell
.FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
.AutoFill Destination:=Range(myCell, _
myCell.Offset(-1, 0).End(xlToRight)(2)), _
Type:=xlFillDefault
End With
myName = Left(ActiveSheet.Name, 6)
Cells(myCell.Row, 1).Value = Mid(myName, 3, 2) & _
"/" & Right(myName, 2) & "/" & Left(myName, 2)
'NEW
Workbooks.OpenText Filename:=myName & "AS.CV1", _
DataType:=xlDelimited, Comma:=True
With myCell
.FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
.AutoFill Destination:=Range(myCell, _
myCell.Offset(-1, 0).End(xlToRight)(2)), _
Type:=xlFillDefault
End With
'NEW
myCell.EntireRow.Copy
With ThisWorkbook.Worksheets(1).Range("B65536"). _
End(xlUp)(2).EntireRow
.PasteSpecial Paste:=xlPasteValues
End With
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True

Next i
Else
MsgBox "There were no files found."
End If
End With

Cells.NumberFormat = "0.0000"
Range("A:A").NumberFormat = "dd/mm/yy"
Cells.EntireColumn.AutoFit
End Sub

[mfx_krypt]

Ah, Nevermind. I have just figured it out.

-Keith