Reformatting text for import 1

[sdfern5]

I have a device which outputs readings of different values to a text file - abc.txt. Each reading is bracketted on a separate line preceded by a descriptive code (in the example below - 0.4 = act power, 0.6 = max power and 0.8 = energy). Each group of measurements has a first line with the time e.g. 2 groups of data:

16:16:00
0.4(0.087)
0.6(0.09)
0.8(000.051)
16:17:00
0.4(0.088)
0.6(0.09)
0.8(000.053)

I would like to to turn this into an Excel spreadsheet with headings like Time¦Act Power¦Energy¦Max Power¦Voltage¦Current

with correspoding values in the rows below. Yes, columns like Voltage must be present even if data is not always present and readings might not always be in the same sequence within groups, but the start of a group can always be identified by the time tag (or presence of ":" in position 3). So as I understand it - I need to have a fixed header, then examine each line's code. If it is a "0.8", put the value in the Enery column etc. but if it is a time tag- go to the next row and import the next group. I'd like to put the code in a macro. How do I do all this in VBA? I'd really be grateful for some example code.

This is my first post.Is this scenario too complex? Should I break it up into separate questions?

Thank you

 

[dyarwood]

What have you tried so far? Can you post your code?

 

[foundryqa]

sdfern5,

I don't think there is any need to split the problem up. One other question though, what are the leading characters for voltage and current?

Fred

 

[foundryqa]

sdfern5,

I had to make a few assumptions on the current and voltage leading digits, but this should get you very close to what you were looking for:

Sub ImportText()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As String
Dim MyType As String
Dim Reading As String
Dim StartingPoint As Integer
Dim StopingPoint As Integer

Application.ScreenUpdating = False

ColNdx = 1
RowNdx = 1
FName = "c:\abc.txt"
Open FName For Input Access Read As #1
'**********************
'Building the column headers in code
'**********************
Cells(1, 1).Value = "Time"
Cells(1, 2).Value = "Act. Power"
Cells(1, 3).Value = "Max Power"
Cells(1, 4).Value = "Energy"
Cells(1, 5).Value = "Voltage"
Cells(1, 6).Value = "Current"

'***********************
'Cycling through the text file
'***********************
While Not EOF(1)
    Line Input #1, WholeLine
    If Mid(WholeLine, 3, 1) = ":" Then
        RowNdx = RowNdx + 1
        ColNdx = 1
        Cells(RowNdx, ColNdx).Value = WholeLine
    Else
        MyType = Mid(WholeLine, 1, 3)
        Debug.Print MyType
        Select Case MyType
        Case "0.4"  'actual power
            ColNdx = 2
        Case "0.6"  'max power
            ColNdx = 3
        Case "0.8"  'energy
            ColNdx = 4
        Case "1.0"  'voltage?
            ColNdx = 5
        Case "1.2"  'current?
            ColNdx = 6
        End Select
        StartingPoint = InStr(3, WholeLine, "(")
        StopingPoint = InStr(4, WholeLine, ")")
        Reading = Mid(WholeLine, StartingPoint + 1, StopingPoint - StartingPoint - 1)
        Cells(RowNdx, ColNdx).Value = Reading
    End If
Wend
Close #1
End Sub

Chip Pearson deserves the credit for the text file code.

If the above code does not make sense, please let me know,

Fred

 

[sdfern5]

Thank you very much foundryqa. I must say how impressed I am with the speed at which you replied. Even more so with the elegant solution. Chip Pearson deserves some gratitude but you deserve the star.

I've tried the code and it works. It does make sense to me although I need to brush up on my code writing. There were a few other tasks I wanted to perform but left them out as I didn't want to confuse the question, like using the first two lines (I left them out in the example) to generate a filename for the resulting xls, and concatenating multiple files before the import. I figured I'd probably find those solutions in previous posts. If I run into problems I'll post again.
Thanks again for your help.