• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Parsing data from messy text into formatted excel

D

davis saltzgiver

New Member
Hello all,

I am currently in way over my head trying to parse a messy and dynamic text file to bring only specific data into excel so that it is displayed in a manageable way. The data is a bluetooth communication log with a sensor that I want to be able to analyze to detect any problems with the sensors measurement as well as easily display trends - excel will be perfect for this.

The problem is that all of the data is hidden in a mess of bluetooth communication info and the data can look a little different in a few scenarios.

All of the solutions I've found heavily use delimiters or require the data to be structured in order to parse but this isn't possible for me as all of the data is messy already.

I have attached a microsoft word doc (I am using a txt but I wanted to make comments to better explain it to you all, simply save it as txt after viewing the comments) of how the data comes to me, and I have attached an excel sheet of the format that I wish I could import the txt file into. All data that I want to grab is highlighted in the word doc, and then transferred into the excel file how I want it to show up.

Is something like this possible?

I wont be able to thank you enough for any direction or coding expertise this community could give!

Davis
 

Attachments

  • Log Desired Format.xlsx
    69.6 KB · Views: 10
  • VBA Log Example.docx
    125.4 KB · Views: 10
Hi !

« dynamic text file » : explain "dynamic" !? As a text file is …
… just a text file !

No need delimiter but just warming a couple of neurones !
You can read each line and analyse it with general VBA text functions,
at beginner level …

As it depends on the source txt file, so without it …
​
 
You surely aren't the only person in the world using this source of USB Data
Have you looked at forums from the USB Machines suppliers or users sites
Have you spoken to the manufacturers?
 
@Marc L I am using the term dynamic in this case to mean that the data could be coming in different orders or locations in the txt document. The structure of the data I want will always be the same, but there could be many or few lines in between the data I want that I need to sift through - so the measurements I am trying to pull are not necessarily coming in a perfect order.

The word doc I uploaded was simply to aid in highlighting and making comments on which data i'm trying to parse. Ive attached the same document as a txt file to this response. The excel doc is an example of how i'm trying to format it - although I'm having trouble.

@Hui this data was given to us my a mobile dev agency my company no longer works with but this is custom, rather than bring someone up to speed and change the code on the application side to change where/how this info is stored (seems expensive and risky) I thought I would try to use VBA to parse through the data as is and make an excel doc that our staff could understand.

Thanks for your quick responses!
 

Attachments

  • VBA Log Example.txt
    21.1 KB · Views: 2

Is this text file an original one or did you mod it ?

From which OS this file comes ?
​
 
This is the original txt. I am using the latest version of mac OS Sierra Version 10.12.2

Would the format of the txt file differ because of Mac OS?

The txt file will always start with the phone os and phone version, app version, firmware version, and battery level. Under that are lines on lines of data that I need to grab specific things from but havent been able to get what I want in the format I want
 
So start your own code like sample of Line Input # statement
in VBA inner help, reading line by line text file …

Easy for the first 4 lines using Split VBA function to separate data
and Replace VBA function to replace characters.

From line #5 until end of file, use conditional statement like If
and Like operator …
​
 
I saved You 'VBA LOG Example' to txt-format.
... I have also Mac and there are no challenges!

This is one possible way to do ...
'one' is not always the best :)

Press [Get Logs] in Sheet1
... something like this?
... of course different layout than You 'wanted',
because .. You'll see why!
 

Attachments

  • VBA Log Example.txt
    21.2 KB · Views: 2
  • Log Desired Format.xlsb
    27.8 KB · Views: 3
vletm said:
I saved You 'VBA LOG Example' to txt-format.
... I have also Mac and there are no challenges!

This is one possible way to do ...
'one' is not always the best :)

Press [Get Logs] in Sheet1
... something like this?
... of course different layout than You 'wanted',
because .. You'll see why!
Click to expand...

hmmm, the excel workbook simply closes whenever I select "VBA Log Example.txt" is there something I have to load in for the button to work?
 
No issue on my side reading directly via VBA Line Input # statement
post #5 attachment, no need to resave it …

I agree with vletm, better is to use columns headers.

As a starter (raw) : (workbook saved in the text file folder)​
Code: 
Sub Demo4Noob()
               TextFile$ = ThisWorkbook.Path & Application.PathSeparator & "VBA Log Example.txt"
        If Dir(TextFile) = "" Then Beep: Exit Sub
        CH = [{": ",", ";"{ ","";"""","";" }",""}]
        ActiveSheet.UsedRange.Clear
        Open TextFile For Input As #1
        Line Input #1, TextLine$
        [B1].Value = TextLine
    For R& = 2 To 4
        Line Input #1, TextLine$
        Cells(R, 1).Resize(, 2).Value = Split(TextLine, CH(1, 1))
    Next
    Do Until EOF(1)
            Line Input #1, TextLine
        If TextLine Like "{ ""rawHeightMillis"": *" Then
            For L& = 1 To UBound(CH)
                TextLine = Replace$(TextLine, CH(L, 1), CH(L, 2))
            Next
                R = R + 1
                S = Split(TextLine, CH(1, 2))
                Cells(R, 1).Resize(, UBound(S) + 1).Value = S
        End If
    Loop
        Close #1
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
:) oops!
Code: 
    With Sheets("Sheet1")
        .Range("A3:F3").ClearContents
        a_max = .Cells(Rows.Count, 1).End(xlUp).Row
        If a_max > 5 Then .Range("A6:I" & a_max).ClearContents
    End With
If a_max > 5 then
is missing from that code
It Clears those 'headers' and ... no work!
Sorry!
Screen Shot 2017-02-01 at 19.56.23.png
 
@davis saltzgiver
hmm ...
Interesting ...
Here are those files again.
I tested - it works here!
... and that txt-file just 'open-read-close'!
It will show the result in 'Sheet1' - that's all!
= it not copy txt-file data to that Excel-file.
 

Attachments

  • Log Desired Format.xlsb
    28.8 KB · Views: 3
  • VBA Log Example.txt
    21.2 KB · Views: 2
@vletm I Just tried it on an older version of excel (I am using 2016) on a coworkers computer and it worked... wonder what the difference is?

I am not familiar with whether they have changed anything in VBA or macros in the last few yrs that would effect this particular code
 
@davis saltzgiver
as I told, it works with my Mac's Excel 2011.
Txt-file has saved with MS-DOS text encoding
(gotta do this step/way, because there is a challenge to read Word-documents).
Of course, I could do version which could show
what is going on then You're running that file in Your normal way
... or You could run it step-by-step too.
 
@vletm no problem, thanks so much for your help! I just thought you might have known what the difference was off-hand but I can definitely go step by step. Thanks again!
 
@vletm So it was closing due to the last line in this chunk:

Code: 
Workbooks.Open (FilePath)
        WB_2 = ActiveWorkbook.Name
       
        With Sheets(1)
            For y = 1 To 4
                datas(y) = .Cells(y, 1)
            Next y
            yy = y - 1
            Do
                yy = yy + 1
            Loop Until Left(.Cells(yy, 1), 35) = "characteristic result { " & Chr(34) & "type" & Chr(34) & ": " & Chr(34) & "0E" Or .Cells(yy, 1) = Empty
            datas(y) = .Cells(yy, 1)
            y = y + 1
           
            Do
                yy = yy + 1
                If Mid(.Cells(yy, 1), 4, 15) = "rawHeightMillis" Then
                    datas(y) = .Cells(yy, 1) & ", " & .Cells(yy + 1, 1) & ", " & .Cells(yy + 2, 1)
                    y = y + 1
                End If
            Loop Until Left(.Cells(yy, 1), 35) = "characteristic result { " & Chr(34) & "type" & Chr(34) & ": " & Chr(34) & "0E" Or .Cells(yy, 1) = Empty
            datas(y) = .Cells(yy, 1)
        End With
    Workbooks(WB_2).Close savechanges:=False

honestly, I'm curious as to why this last line of code didn't close the workbook in excel 2011?

That being said, I removed this line of code and now the workbook stays open but absolutely nothing happens and I don't know why. Its so strange that whatever the reason, it wouldnt work on excel 2016 and it would work on excel 2011
 

Your 2016 is a MAC or Windows version ?

Did you try my sample code under 2016 version ?​
 
@davis saltzgiver
Have You checked what are 'FilePath' and 'WB_2' values?
Those have to be correct!
... and WB_2 have to be that 'data-file's name'!
If 'data-file' opens ... as You wrote, it should be Activeworkbook too.
Did You use that my txt-file too?
As I told ... that part works as fine as possible in my Excel 2011.
Which Excel version do You use?
There can be differences between Mac's Excel versions.
... as well as Windows/Mac ...
that's why there is that 'getting datafile name' if...else...endif
I cannot test those files in other computers nor Excel-versions:(
 
You must log in or register to reply here.
Back
Top