Make a macro that inport data from a txt file into a excel file.

I have a excel file that I want to create a button on it. When button is clicked then it will pull dsts from a IDSOdump.txt file and put it in the excel file.

Below is an example of the IDSOdump.txt file data.


   DNVRCOIID31 09-03-30 07:26:45

   DNVRCOIID31 09-03-30 07:26:46

   DNVRCOIID31 09-03-30 07:26:51

The only information I want in the spread sheet is every time it say "FROM=DS0INT-0577-1-1-01,TO=DS0INT-0583-1-1-05" I want the following in the spread sheet.
I would like to strip the 0 off of the begin of each number but if it is another number beside 0 then leave it.
Column A      and      Column B
577-1-1-01              583-1-1-05

Who is Participating?
Robberbaron (robr)Connect With a Mentor Commented:
this is short and easy to use.

just dumps output into current sheet, starting at current cell location.

##change to location of the txt file path from my test##

Sub GetText()
    Dim sInputName As String, lfn As Long
    Dim iPart1 As Integer, iPart2 As String, iPart3 As Integer, sPart1 As String, sPart2 As String
    Const sFlag1 = "FROM=DS0INT-"
    Const sFlag2 = ",TO=DS0INT-"
    Const sFlag3 = ":CCSTATE"
    lrow = 0: lCol = 0
    sInputName = "c:\work\ee\IDSOdump.txt"  '<<<<user change required'
    lfn = FreeFile
    Open sInputName For Input As #lfn
    Do While Not EOF(lfn)
        Line Input #lfn, slinedata
        iPart1 = InStr(slinedata, sFlag1)
        If iPart1 > 0 Then
            'found a data line'
            iPart2 = InStr(slinedata, sFlag2)
            iPart3 = InStr(slinedata, sFlag3)
            sPart1 = Mid(slinedata, iPart1 + Len(sFlag1), iPart2 - iPart1 - Len(sFlag1))
            sPart2 = Mid(slinedata, iPart2 + Len(sFlag2), iPart3 - iPart2 - Len(sFlag2))
            If Left(sPart1, 1) = "0" Then
                'strip leading0'
                sPart1 = Mid(sPart1, 2)
            End If
            If Left(sPart2, 1) = "0" Then
                'strip leading0'
                sPart2 = Mid(sPart2, 2)
            End If
            ActiveCell.Offset(lrow, 0) = sPart1
            ActiveCell.Offset(lrow, 1) = sPart2
            lrow = lrow + 1 'move to next row'
        End If
    Close lfn
End Sub

Open in new window

Hi, I have written a code for this part. See code snippet. Create a button on your sheet and assign the macro to it. One thing though: the code is not quite fast...I could take a couple of minutes (+-2 minutes)

hope to see your reply soon.
Sub macro1()
Dim cel As Range
    Flnm = Application.GetOpenFilename(, , "Open File")
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Flnm & "", Destination _
        .Name = "IDSOdump"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.Delete Shift:=xlToLeft
    For Each cel In Columns("A:B").Cells
        If Mid(cel.Value, 5, 4) = "FROM" Or Left(cel.Value, 3) = "TO=" Then
            cel.Replace What:="   ""FROM=DS0INT-", Replacement:="", LookAt:= _
                xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            cel.Replace What:="TO=DS0INT-", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            cel.Replace What:=":CCSTATE=XC", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        End If
End Sub

Open in new window

Troush2009Author Commented:
Great work this is what I was looking for.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.