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

 

[artz]

Hi Fazza,

I am still missing the disconnect. So if your code can work on data input, this we should be good to go:

Freq.V(out)
1.00000000000000e-002(-1.07460150501695e+000dB,1.70221075386850e+002ｰ)
1.00230523807790e-002(-1.03460514919950e+000dB,1.70198365412089e+002ｰ)
1.00461579027840e-002(-9.94608837938438e-001dB,1.70175601937677e+002ｰ)
1.00693166885180e-002(-9.54612571695972e-001dB,1.70152784832463e+002ｰ)
1.00925288607668e-002(-9.14616350938836e-001dB,1.70129913964946e+002ｰ)
1.01157945425990e-002(-8.74620176138324e-001dB,1.70106989203265e+002ｰ)
1.01391138573668e-002(-8.34624047770293e-001dB,1.70084010415204e+002ｰ)
1.01624869287070e-002(-7.94627966315257e-001dB,1.70060977468187e+002ｰ)
1.01859138805412e-002(-7.54631932258399e-001dB,1.70037890229280e+002ｰ)
1.02093948370768e-002(-7.14635946089642e-001dB,1.70014748565187e+002ｰ)
1.02329299228075e-002(-6.74640008303633e-001dB,1.69991552342250e+002ｰ)
1.02565192625141e-002(-6.34644119399902e-001dB,1.69968301426450e+002ｰ)
1.02801629812647e-002(-5.94648279882769e-001dB,1.69944995683400e+002ｰ)
1.03038612044162e-002(-5.54652490261519e-001dB,1.69921634978351e+002ｰ)
1.03276140576140e-002(-5.14656751050343e-001dB,1.69898219176184e+002ｰ)
1.03514216667934e-002(-4.74661062768453e-001dB,1.69874748141414e+002ｰ)
1.03752841581801e-002(-4.34665425940097e-001dB,1.69851221738186e+002ｰ)
1.03992016582906e-002(-3.94669841094622e-001dB,1.69827639830276e+002ｰ)

I will try this data on my end with your latest code.

Thanks,

-Art

 

[artz]

Hi Fazza,

With this data input, I still receive the runtime error.

-Art

 

[artz]

Hi Fazza,

That last data post was copied from Excel to Notepad and then copied into the Excel BB. Notepad has no HTML capability - maybe that's the key.

-Art

 

[artz]

Hi Fazza,

I hacked at your code and was able to get the it to produce the desired result. It was a simple change. Original code on this line was:

 Range("C1:C" & 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

Simply changed the Tab setting to true and it all worked. Final macro is:

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("C1:C" & lLastRow).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, 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

Many thanks again for the help. Happy Holidays!

-Art