Parsing data for operation in Excel

[artz]

Hi,

I am using a circuit simulation program called LTSPICE. It has an option to export chart data to a text file. Unfortunately, the format is not directly compatible with Excel. A snippet of the file output is shown below:

Freq. V(out)
1.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002°)
1.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002°)
1.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002°)
1.07151930523761e-002 (1.25267781027906e-001dB,1.69515961436982e+002°)
1.09647819614318e-002 (5.25212965403845e-001dB,1.69269617699112e+002°)
1.12201845430196e-002 (9.25151509702781e-001dB,1.69017384779520e+002°)
1.14815362149688e-002 (1.32508270979400e+000dB,1.68759114931987e+002°)
1.17489755493953e-002 (1.72500579066355e+000dB,1.68494656242269e+002°)
1.20226443461741e-002 (2.12491989944593e+000dB,1.68223852482718e+002°)
1.23026877081238e-002 (2.52482409777929e+000dB,1.67946542960527e+002°)
1.25892541179417e-002 (2.92471735341947e+000dB,1.67662562359300e+002°)
1.28824955169313e-002 (3.32459853104108e+000dB,1.67371740573617e+002°)
1.31825673855641e-002 (3.72446638214826e+000dB,1.67073902536292e+002°)
1.34896288259165e-002 (4.12431953400941e+000dB,1.66768868037971e+002°)

I would like to have 3 columns of data: Freq., Amplitude, and Phase. The Amplitude needs the dB removed and the Phase needs the ° removed. The parentheses also need to be removed. Also, having the data delimited by commas would be best. It would also be nice to have the data limited to 3 decimal points. The files are of variable length in rows.

Could anyone in the Forum help with a VBA macro that could do this processing?

Any help would be greatly appreciated.

Thanks,

Art

 

[jtakw]

I'm not sure I understand your comment above.

In my Post # 6, your Sample data from OP in Column A, my formula in B1 copied down and across to Column D, produces the 3 separate Freq., Amplitude, and Phase values you want limited to 3 decimal places...

 

[artz]

Hi jtakw,

Thanks! Your cell formulas totally work. Why I want VBA is that I can re-purpose the code for other calculations. While your cell formula does exactly what I want, I don't have even a vague clue a to what you are doing in that calculation. I could not use that again for a different application. Make sense?

If you can repackage the this cell formula into VBA, that would be totally awesome.

Thanks so much,

Art

 

[artz]

Hi All,

Many thanks to all who responded. I was able to work with the posts supplied to analyze the data from LTSPICE in Excel.

Thanks again to all.

-Art

 

[Fazza]

heavily based on the macro recorder. cheers

maybe put in your personal macro file

Sub Macro1()


    Dim lLastRow As Long
    Dim vFileSaveAsName As Variant
        
    'if it looks like the expected header
    If Left$(Range("A1").Value2, 4) = "Freq" Then
        
        Rows(1).Delete
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
        Range("A1:A" & lLastRow).CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
            TrailingMinusNumbers:=True
            
        Range("B1:B" & lLastRow).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
            Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            
        Cells.Replace What:="db", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Cells.Replace What:="°)", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Cells.NumberFormat = "0.000"
        ActiveWorkbook.PrecisionAsDisplayed = True
        ActiveWorkbook.PrecisionAsDisplayed = False
        
        'headers
        Rows(1).Insert
        Range("A1:C1").Value2 = Array("Frequency", "Amplitude", "Phase")
        
        vFileSaveAsName = Application.GetSaveAsFilename()
        If vFileSaveAsName <> False Then ActiveWorkbook.SaveAs vFileSaveAsName
    End If
    
End Sub

 

[artz]

Hi Fazza,

Thanks for posting your VBA solution. I tried it today on a dataset when I had some free time at work. It crashed at the point in the code where dB to "" was made. I received a runtime error 1004. Oh, I am running XL 2016 on Win10, 64 bit.

If you can fix this it would be great.

Thanks for your help.

-Art

 

[Fazza]

hello,

Is the code good on the sample data? In first post of the thread

thanks

 

[artz]

Hi Fazza,

Yes it was. I figured out what happened I think. After I pulled the .txt file into Excel, it had no spaces in the string. When I pasted the text into this BB, it added a tab between the frequency and the first left parenthese. Below I've corrected the data by removing the tab manually.

"Freq. V(out)"
"1.00000000000000e-002(-1.07460150501695e+000dB,1.70221075386850e+002ｰ)"
"1.02329299228075e-002(-6.74640008303624e-001dB,1.69991552342250e+002ｰ)"
"1.04712854805090e-002(-2.74683403826346e-001dB,1.69756559710614e+002ｰ)"
"1.07151930523761e-002(1.25267781027906e-001dB,1.69515961436982e+002ｰ)"
"1.09647819614318e-002(5.25212965403845e-001dB,1.69269617699112e+002ｰ)"
"1.12201845430196e-002(9.25151509702781e-001dB,1.69017384779520e+002ｰ)"
"1.14815362149688e-002(1.32508270979400e+000dB,1.68759114931987e+002ｰ)"
"1.17489755493953e-002(1.72500579066355e+000dB,1.68494656242269e+002ｰ)"
"1.20226443461741e-002(2.12491989944593e+000dB,1.68223852482718e+002ｰ)"
"1.23026877081238e-002(2.52482409777929e+000dB,1.67946542960527e+002ｰ)"
"1.25892541179417e-002(2.92471735341947e+000dB,1.67662562359300e+002ｰ)"

If you can get the macro to operate on this, that would be great.

Thanks,

Art

 

[Fazza]

hi, Art

A slight modification. Again, I used the macro recorder. Basically just added one more text to data - at the beginning.

Works for me on a copy of the data posted just above. If your data is different, please modify to suit.

Season's greetings. Fazza

Sub Macro1()


    Dim lLastRow As Long
    Dim vFileSaveAsName As Variant
        
    'if it looks like the expected header
    If InStr(Range("A1").Value2, "Freq") > 0 Then
        
        Rows(1).Delete
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
        Range("A1:A" & lLastRow).CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        
        Range("A1:A" & lLastRow).CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
            TrailingMinusNumbers:=True
            
        Range("B1:B" & lLastRow).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
            Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            
        Cells.Replace What:="db", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Cells.Replace What:="°)", Replacement:=vbNullString, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Cells.Replace What:=Chr$(63) & ")", Replacement:=vbNullString, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        Cells.NumberFormat = "0.000"
        ActiveWorkbook.PrecisionAsDisplayed = True
        ActiveWorkbook.PrecisionAsDisplayed = False
        
        'headers
        Rows(1).Insert
        Range("A1:C1").Value2 = Array("Frequency", "Amplitude", "Phase")
        
        vFileSaveAsName = Application.GetSaveAsFilename()
        If vFileSaveAsName <> False Then ActiveWorkbook.SaveAs vFileSaveAsName
    End If
    
End Sub

 

[artz]

Hi Fazza,

I tried to install the Mr.ExcelHTMLMaker add-in couldn't get it to work. What I did discover is that the BB HTML editor adds the tab and quotes on each line of the Excel data see below.

"Freq. V(out)"
"1.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002ｰ)"

This should be:

Freq. V(out)
1.00000000000000e-002(-1.07460150501695e+000dB,1.70221075386850e+002ｰ)

This is how Excel shows the data. Can you work around this?

Thanks,

-Art

 

[Fazza]

The codes so far have worked perfectly for me on the posted samples.
It seems something is different between the sample data & your real data.
And there is a problem pasting a good sample of the real data.

Best I'm thinking is to please use the macro recorder. ALT-T-M-R
The generated code will be 99% of what you need.
The approach I used was data-to-text a couple of times - ALT-D-E-A
And then a couple of text replacements - CTRL-H

If the code isn't 100% right, please modify it along the lines of the above posted code.
I'm away now til next year. cheers