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]

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

 

[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????

 

[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 colum
needs to be averaged for that day - so for example you would averag
1.34,1.33,1.45 and get 1.373, you would than input that value into th
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 decima
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]

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]

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").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("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.

-Keit