Extract only text data from .dat file. Find specific strings of text

[SamatarM]

Sub ReadEntireFileAndPlaceOnWorksheet()
  Dim X As Long, FileNum As Long, TotalFile As String, FileName As String, Result As Variant, Lines() As String, rng As Range, i As Long, used As Range, MyFolder As String
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    MyFolder = .SelectedItems(1)
End With
FileName = Dir(MyFolder & "\*.*")
Do While FileName <> ""
  FileName = Dir()
  FileNum = FreeFile
  Open FileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbNewLine)
  ReDim Result(1 To UBound(Lines) + 1, 1 To 1)
    For X = 1 To UBound(Result)
    Result(X, 1) = "'" & Lines(X - 1)
  Next
  Set used = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Columns
  Set rng = used.Offset(0, 1)
  rng.Resize(UBound(Result)) = Result
  Loop
  
End Sub

I am trying to find some data in a .dat (binary file). The data should look like this:

MiHo14.dat
MDF     3.00    TGT 15.0
Time: 06:40:29 PM
Recording Duration: 00:05:02
Database: DB
Experiment: Min Air take
Workspace: MINAIR
Devices: ETKC:1,ETKC:2
Program Description: 0delivupd2
Module_delivupd2
WP: _AWD_5
RP: _AWD
§@
Minimum intake - + revs - Downward gear ​

The code I have currently extracts all data from .dat file and places in Excel file looks like this:

MiHo14.dat
MDF     3.00    TGT 15.0
Time: 06:40:29 PM
Recording Duration: 00:05:02
Database: DB
Experiment: Min Air take
Workspace: MINAIR
Devices: ETKC:1,ETKC:2
Program Description: 0delivupd2
Module_delivupd2
WP: _AWD_5
RP: _AWD
§@
Minimum intake - + revs - Downward gear 
Bã|ŽA…@@,s~?
B{À¿…@@@Ý‚Iá 
Á<
"@²n¢”N@ÇÿÈÿj
Ð=“SØ•N@ÇÿÈÿj	
à¨. —N@ÇÿÈÿj
 8²œg˜N@ÇÿÈÿj
0NI,¯™N@ÈÿÈÿj
Ðä$öšN@ÈÿÈÿj
@Q›=œN@ÈÿÈÿj
Пe…N@ÇÿÈÿj
 GàÍžN@ÇÿÈÿj"
etc....​

This means I have a lot of post processing to do and its difficult to judge where the good text will end as the final line does not have a header..for e.g. all text is preceded by header like Time:, RP:, so its easy to post process..I need to know how to extract only the useful text and ignore all the symbols and gibberish?

 

[oldbrewer]

have you tried opening the file with windows where you can try word, excel etc

who sent you the file, ask them what software was used to create it

 

[SamatarM]

Hi thanks for the response. I should explain a bit more. The file is generated in INCA I don't know if you are familiar with it. I need to get some information out of a large number of these files. This information is now being successfully extracted with my code above. However I'm getting a little more than I want and the sheet is now messy filled with all those symbols you can see. I just want to extract the text only. You can see all the text has a header like time: ,rp: , wp: all except the last user comment that can basically be anything. You have any idea how to sort this. Even a solution just to delete all cells that don't have the information in just to clear my sheet?

 

[oldbrewer]

say cell A1 is "apple$%^&*" where the last 5 characters are gibberish

len(A1) is 10 so a little macro

for j= 1 to 10
if mid(cells(1,1),j,1)="$" then mid(cells(1,1),j,1)="":go to 100

repeat this line for all mad characters
100 next j

 

[SamatarM]

Hi thanks, that would work well if I know what character will be in the cell. I cant think of a way to clear entire cell contents if there is say any one of "'½l
½dC@" characters in the cell for e.g. I tried this :

vArr = Array("½", "ã", "ç")
Set var10 = Commentpaste.Find(vArr, LookIn:=xlValues, LookAt:=xlPart)
If Not var10 Is Nothing Then
    var10.ClearContents
End If​

however doesn't work, going back to the actual extracting of the data easiest way would be to only extract data that I need i.e. useful text. I say easy I mean hardest I have absolutely no Idea how I would specify the text I need, any clues on either of those questions? Thanks for the response!

 

[oldbrewer]

if you examine 10 cells and put every gibberish character into my code you will sort 99%

are the characters all GERMAN ACCENTED LETTERS ?