Pulling data from .txt file

[bignoggin]

I need some help pulling data from a txt file into an excel file. I have attached a sample of the file generated by the system and an output on how Im hoping to display the data.

 

[eibi]

Welcome to the forums; I hope you find the help you're looking for.

I looked at the attachments you provided, and I would need some clarification before I could offer any suggestions:

• The durations in your sample spreadsheet don't precisely match the durations in the text file. How exactly do you want to calculate the duration?

• The attendee count in your sample spreadsheet doesn't correspond to the attendees that I count in the text file...Please verify that you want to count each line under "Attendee" in the text file as a single attendee for the purpose of the spreadsheet count.

All best.

 

[bignoggin]

sorry I took that from a spreadsheet a while ago.

The duration can match the txt document HH:MM:SS

The attendees should match the amount of channels below the attendee column.
and yes just down to a single number so in the first one would be 8

 

[Nebu]

Hi:

Find the attached. The macro will pull data from text file and scrape it in the scarping sheet and will pull data into the final sheet. I am not sure whether it will take care of all your requirements as the testing is done only based on the one text file you uploaded. Play around with it and let me know with questions if any.

Thanks

 

[bignoggin]

Thank you so much for the help. It work perfectly with one flaw. if the next report is processed and its smaller than the previous one it does not clear the previous data.

I do have one more question would it be possible to be able to import the data with selecting a file rather than from a named .txt file?

 

[Nebu]

Hi:

Find the attached. I have made the changes as per your post. It will ask you to select the relevant text file.

Thanks

 

[bignoggin]

Im unable to open any .txt files form outside the same directory.

 

[Nebu]

Hi:

I am not sure what you meant by cannot open outside the same directory. I have no issues at my end to open files from any other directory.The file browser act like any other widows browser you have to navigate to the correct location to select the file, or else store all your file in the location where the macro file is stored you will be alright.

Thanks

 

[bignoggin]

Then the issue must be on my network. I was worried that would be the case. Thank you so very much for the help with this.

 

[bignoggin]

I do have one last question if the server has not had any logs if spits out a file that causes the macro to error out. I attached an example. would it be possible to add something into the macro so it can still put into a cell "No Conference" if there are no sessions?

 

[Nebu]

Hi:

Use the following code.

Sub GetData()
   
    Dim cn As Object
    Dim rs As Object
    Dim fd As Office.FileDialog
    Dim FilePath As String
    Dim FileName As String
    Dim querystr As String
    Sheet2.Cells.ClearContents
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
    With fd
        .AllowMultiSelect = False
        .Title = "Please Select Last Week File."
        .Filters.Clear
        .Filters.Add "All Files", "*.txt*"
            If .Show = True Then
              FilePath = .SelectedItems(1)
            End If
        End With
        FileName = Split(FilePath, "\")(UBound(Split(FilePath, "\")))
    pth = ThisWorkbook.Path & "\"
    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & pth & ";" & _
            "Extended Properties = ""text; HDR=Yes"""
    cn.Open cnStr
    Application.ScreenUpdating = False
        On Error Resume Next
        querystr = "SELECT *FROM " & "[" & FileName & "]"
        rs.Open querystr, cn, 3, 4
        Do While Not rs.EOF
          ActiveSheet.Range("A1").CopyFromRecordset rs
        Loop
        rs.Close
 
    Set rs = Nothing
    Set cn = Nothing
 
  Dim i As Long
  Dim j As Long
  Dim k As Long
  Dim l As Long
  Dim m As Long
  Dim n As Long
 
  n = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
  Sheet1.Range("A4:C" & n).ClearContents
 
  Sheet1.[C1] = Sheet2.[A1]
  i = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
  k = 4
 
For j = 9 To i
    If Range("B9") = vbNullString Then
        MsgBox "No Data"
        Exit Sub
    End If
    If Range("B" & j) <> vbNullString Then
        Sheet1.Range("A" & k) = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Substitute(Sheet2.Range("A" & j), "---- Session starting", "", 1))
        l = Sheet2.Range("A" & j).CurrentRegion.Rows.Count + j - 1
        Sheet1.Range("B" & k) = Application.WorksheetFunction.CountA(Range("A" & j + 2 & ":A" & l))
        For m = j + 2 To l
            Sheet2.Range("D" & m).FormulaR1C1 = _
                "=IFERROR(TRIM(MID(RC[-3],FIND(""M  0"",RC[-3])+2,10))+0,TRIM(MID(RC[-3],FIND(""M 0"",RC[-3])+2,10))+0)"
        Next
        Sheet2.Range("C" & j) = Application.WorksheetFunction.Max(Range("D" & j + 2 & ":D" & l))
        Sheet1.Range("C" & k) = Sheet2.Range("C" & j)
        k = k + 1
  End If
Next
  Application.ScreenUpdating = True
End Sub

Thanks