No announcement yet.

"file System Object"

  • Filter
  • Time
  • Show
Clear All
new posts

  • "file System Object"

    Hi All

    Can someone, explean how the "File System object" works in Excel?

    I have a various information in one log file, which need to import to excel. So it's i need to declare all fields i need? or how?

    appreciate for your help and explanation.

  • #2
    Re: "file System Object"

    While you can use methods from the File System Object, other methods can also be used to Read a file's data. If you attach a sample file, and explain your goals in more detail, you will get more help.

    I suspect that your Log file is a text file. It should be in a standard delimited format to read specific parts (fields) properly.


    • #3
      Re: "file System Object"

      Hi kenneth

      Thanks for your reply

      My log data, is containing various of information in text file. The problem is, the log file is not in delimited format, or tab format, is not in same order. If i setting one data, I will lose other's data, due to the field is not in same order.

      I'm attached the sample data.
      Attached Files


      • #4
        Re: "file System Object"

        Ok, the next step is to create a spreadsheet using the data from that file for an example. You can do just the first three rows of data if you like.

        It appears that you have two separate sections in the text file. Luckily, it appears that your log data is using column positions for the data.

        Once we know your spreadsheet layout, the file can be parsed properly.


        • #5
          Re: "file System Object"

          hI Kenneth

          thanks again for your reply, here i'm attached the sample layout for 3 field from 2 separate sections....

          Attached Files


          • #6
            Re: "file System Object"

            If no one beats me to it, I will post an example for you later today or tomorrow. Just wanted to let you know that I haven't forgotten.

            It is a fairly simple matter to do what you want. From your example import fields and the txt file, basically, you have two fields to import from the first section of data and then 1 field from the 2nd set. The second set takes a bit more work since it can have blanks lines between data rows. It appears that if the first set has 12 rows of data, the second set has 12 but some blank rows in-between. Since the data appears to use column locations, FileRead should work just fine.

            The best method to approach this may depend on the size of the log file. If it is large, some methods are better than others to optimize speed.


            • #7
              Re: "file System Object"

              I started to code this and found something that you may want to address while we are at it. I copied the 3 columns in the quote below.

              Question 1. Did you want to sort the LINK SLC data to match the LINK column?
              Qestion 2, if 1 is yes. Did you need LINK SLC data as two numbers or would just the last part be best once the match was made? That would seem best to me. You could always add a formula to recreate it as needed.
              LINK LINK SET LINK SLC
              ---- --------- --------
              1 16 MCAL1 41 0
              5 16 MCAL1 42 1
              21 17 MCAL2 43 2
              25 17 MCAL2 44 3
              41 18 GTTK1 71 0
              42 18 GTTK1 72 1
              43 18 GTTK1 73 2
              44 18 GTTK1 74 3
              71 19 GIKT1 1 0
              72 19 GIKT1 5 4
              73 19 GIKT1 21 0
              74 19 GIKT1 25 4


              • #8
                Re: "file System Object"

                Here one method. It still may need tweaking due to what I detailed earlier.
                Type tLogData
                  LINK As String * 4
                  LINKSET As String * 9
                  LINKSLC As String * 8
                End Type
                Sub ImportLogData()
                Dim sLogFile As String
                Dim iLN As Integer, iLNS1 As Integer, iLNS2 As Integer
                Dim sLine As String, aLine As Variant
                Dim LogData As tLogData
                Dim cLink As New Collection, cLinkSet As New Collection, cLinkSLC As New Collection
                Dim var As Variant
                'Initialize integer variables.
                iLN = 0: iLNS1 = 0: iLNS2 = 0:
                'Get log filename
                sLogFile = Application.GetOpenFilename("Log File, *.txt")
                If Dir(sLogFile) = vbNullString Then
                  MsgBox "Log file not found.", vbCritical, "Failure"
                  Exit Sub
                End If
                'Read log file and parse to variables
                Open sLogFile For Input As #1
                Do While Not EOF(1)
                iLN = iLN + 1
                Line Input #1, sLine
                LogData.LINK = Mid$(sLine, 3, 4)
                LogData.LINKSET = Mid$(sLine, 9, 9)
                'start of dataset1
                If LogData.LINK = "----" Then iLNS1 = iLN
                'end of dataset1
                If LogData.LINK = Space(4) And LogData.LINKSET = Space(9) Then iLNS1 = 0
                'start of dataset2
                If LogData.LINK = "-  -" Then
                  iLNS2 = iLN
                  iLNS1 = 0
                End If
                'end of dataset2
                If LogData.LINK = "EXECUTED " Then iLNS2 = iLN
                'Set the collection field values
                Select Case True
                  Case iLN > iLNS1 And iLNS1 <> 0 And iLNS2 = 0
                    cLink.Add LogData.LINK
                    cLinkSet.Add LogData.LINKSET
                  Case iLNS2 > 0 And iLN > iLNS2 And iLNS1 = 0
                    LogData.LINKSLC = Mid$(sLine, 50, 8)
                    If LogData.LINKSLC <> Space(8) Then cLinkSLC.Add LogData.LINKSLC
                  Case Else
                End Select
                Close #1
                Rem Show what is going on
                'Debug.Print "clink.count=" & cLink.Count
                'Debug.Print "clinkset.count=" & cLinkSet.Count
                'For i = 1 To cLink.Count
                '  Debug.Print "clink=" & cLink.Item(i)
                '  Debug.Print "clinkset=" & cLinkSet.Item(i)
                '  Debug.Print "clinkslc=" & cLinkSLC.Item(i)
                'Next i
                'Insert the field values for Sheet1
                For i = 1 To cLink.Count
                  Range("a" & Range("a65536").End(xlUp).Row + 1).Value = cLink.Item(i)
                  Range("b" & Range("b65536").End(xlUp).Row + 1).Value = cLinkSet.Item(i)
                  Range("c" & Range("c65536").End(xlUp).Row + 1).Value = cLinkSLC.Item(i)
                Next i
                End Sub


                • #9

                  Re: &quot;file System Object&quot;

                  WoW!..Thanks so much for taking the time to help me out.

                  Regards of your question, 1 , the anser is Yes. .

                  LINK LINK SET LINK SLC
                  41 18 GTTK1 41 0

                  since it's appeared in diffrent of command group, (ZNSI for LINK and ZNCI for LINK SLC), it have to combine to get SLC to match with link.

                  So Q2, you'r right, it's only need the last part/digits which is SLC field to match with LINK, as below result;

                  LINK LINK SET SLC
                  41 18 GTTK1 0

                  Appreciate for your help,,