How can I deal with this text file?

[TheSearcher]

I have a text file that cannot be imported and turned into a table because it has no line feed characters and is therefore well over 255 columns. The fields are delimited by commas, however.
How can I search this file for all instances of "{id" and then grab the next 10 characters to the right and store them somewhere?

Thanks in advance,
TS

 

[jdraw]

Do you have a comma delimited file? CSV

Perhaps you could paste/post a sample of the data.
It would be helpful to readers if you give some description of what the data represents, its use etc.

 

[TheSearcher]

This is an excerpt of the data:
04:00,type:MOTION_MP4,srcType:cam,srcId:911340,deviceAlias:jlkurtzer:EC:1A:59:E6:79:FC,duration:14.469,viewed:false,favorite:false,values:{},media:{thumbnail:{},videoCdn:{},videoCdnInfo:{},download:{},video:{}}},{id:274081357,dttm:2015-03-16T04:04:30-04:00,type:MOTION_MP4,srcType:cam,srcId:911340,deviceAlias:jlkurtzer:EC:1A:59:E6:79:FC,duration:8.453,viewed:false,favorite:false,values

It looks like a log file. I have no idea what the data represents. My job is to find each occurrence of {"id:" and then capture the next 9 digits to the right. In the above example I would need to grab "274081357."

Thanks for your help!

 

[spikepl]

try this

    Dim objStream, strData
    Set objStream = CreateObject("ADODB.Stream")

    objStream.Mode = 12    'adModeShareExclusive
    objStream.Open

    objStream.LoadFromFile (FullFilePathAndExtension)

    strData = objStream.ReadText()
    objStream.Close

    Set objStream = Nothing

then all your data sit in strData

 

[Galaxiom]

VBA required.

Use the TextStreamObject to bring the text in as a string. The Read Method can be used to get a specified number of characters so you don't have to read the whole thing at once if it is huge.

Then a loop using InStr() to find the next occurrence of {"id:"

You will need to include the Start argument of InStr() to find each subsequent instance.

Then Mid() to get the values.

Write the values to a table with an insert query or via a recordset.

 

[MarkK]

Check out the VBA.Split() function, which makes this kind of job easy.

 

[TheSearcher]

Spikepl,

I did what you suggested and inserted strData into a field in the table. This is what the data looks like:
慮汩㨢絻∬楶敤䍯湤㨢絻∬楶敤䍯湤湉潦㨢絻∬潤湷潬摡㨢絻∬楶敤≯笺絽ⱽ≻

What do you think went wrong?

Thanks,
TS

 

[spikepl]

inserted strData into a field in the table

why?

what is the character set of your file? Try opening it in notepad and see what it says when you try to do save as

 

[TheSearcher]

I inserted it into a table so that I can query it and pull the specific data that I need.
I opened it in Notepad and it's just a regular text file.

 

[spikepl]

Try opening it in notepad and see what it says when you try to do save as

look at Encoding field

don't mix tables into this-see the posts by galaxiom and markK

 

[TheSearcher]

My apologies. It's using an ANSI character set.

 

[spikepl]

try this. Insert following line
objStream.Charset = "utf-8"
after
Set objStream = CreateObject("ADODB.Stream")

 

[spikepl]

If that doesn't work try

objStream.Charset = "_autodetect"

If that doesn't work either then I apologize, and will have to dig out some other code

 

[jdraw]

You could adapt this.
It works with multiple instances of your test data sample.
It works where the sample data is in a file named
C:\Users\Mellon\Documents\funnydata.txt

Change the file name as appropriate.
I write the numeric strings to the immediate window, but you could write to a table or output file.

Sub funny()

          Dim i As Integer
          Dim x As String, y() As String
          Dim ofso As FileSystemObject
          Dim tsin As TextStream
          Const ForReading = 1

10        On Error GoTo funny_Error

20        Set ofso = CreateObject("Scripting.FileSystemObject")
30        Set tsin = ofso.OpenTextFile("C:\Users\Mellon\Documents\funnydata.txt", ForReading)
40        x = tsin.ReadAll
50        y = Split(x, "{id:")
60        For i = LBound(y) To UBound(y)
70            If (IsNumeric(Left(y(i), 9))) Then
80                Debug.Print Left(y(i), 9)
90            End If
100       Next i

110       On Error GoTo 0
120       Exit Sub

funny_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure funny of Module ADO_Etc"

End Sub

 

[TheSearcher]

Thanks JDraw. Oddly, when I run this code absolutely nothing happens. I added Windows Scripting Runtime to my references and also tried changing debug.print to a msgbox but nothing happens when I run the code.

 

[TheSearcher]

Hi jdraw,

I did some debugging and it is failing at: y = Split(x, "{id:")
The message I'm getting is: "Error 13 (Type mismatch) in procedure funny of Module ADO_Etc"
This is odd because I changed the name of the procedure to ParseIt_3 and the module is called Module1.
Any ideas?

Thanks for your help.
TS

 

[jdraw]

Did you change the file name to your own?
Did you look in the immediate window?
Look at the error message in the vba, the module name is coded.

 

[TheSearcher]

Yes, I changed the file name to my own.
Yes, I have the immediate window open (that's how I run the procedure).
I set up a message box and the file is being read. I can see the data. But when it gets to y = Split(x, "{id:") it fails with the type mismatch error. The error appears when I try to display the value of y in a message box: MsgBox y

I'm not sure what you mean by "Look at the error message in the vba, the module name is coded."

 

[MarkK]

I would declare the target of the Split() assignment as a Variant, like . . .

dim vArray as Variant
vArray = Split(x, "{id:")

 

[gemma-the-husky]

I tend to use low level routines

 dim fnum as long
 dim fname as string
 dim s as string
 dim l as string
  
 fnum = freefile
 fname = whatever
  
 s=""
 open fname for input as #fnum
 while not eof(fnum)
     line input #fnum, l
     if s <> "" then s = s & vbcrlf
     s = s & l
 wend
 close #fnum

s now contains a string with all the file data

the examples to split the data look correct.

you could instead use a loop to find positions of {id:

sort of this pseudologic

 [B]while find_next_instance_of_substring "{id:" is true[/B]
 [B]    process result[/B]
 [B]wend[/B]