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

Posted on 2009-03-30
Last Modified: 2013-11-25
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

Question by:Troush2009
LVL 17

Expert Comment

ID: 24027495
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

LVL 32

Accepted Solution

Robberbaron (robr) earned 500 total points
ID: 24037837
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


Author Closing Comment

ID: 31564439
Great work this is what I was looking for.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy value from a certain cell 5 25
MS Excel IF AND OR statement 3 31
Pivot help - Display only Is Not Null 7 17
How do I crate a Pivot table in Excel 2 11
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now