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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

12 Experts available now in Live!

Get 1:1 Help Now