CODEWITHSUNDEEP
excelvbaprojectdata-analysis
HobbitForHire
HobbitForHire

Reputation: 13

Extracting information from text file in VBA

I'm currently trying to extract data from a text file as part of an analytics challenge in my place of work. The text file is a bunch of data in lines with each heading/entry separated by a comma.

I've looked at several examples of text extraction online but the furthest I've gotten is getting one line in a single cell and then Excel freezing. All others have just frozen Excel after I've put in my conditions.

My current attempts involve the following:

Do Until EOF #1, textLine
Line Input #1, textLine

    Do Until Count = Len(text line) + 1
    Text = Text & Mid(textLine, Count, Count)
    If Right(text, 1) = "," Then
    textImport = Left(text, Count - 1)
    Cells(rowCount, column count) = textImport
    Text = ""
     columnCount = columnCount + 1
    Loop

    rowCount = rowCount + 1

Loop

Can anyone advise where I'm going wrong? I can't share any of the data or the text file due to the nature of the challenge and the data involved.

Upvotes: 0

Views: 1303

Answers (2)

Hansraj
Hansraj

Reputation: 96

Try this if this is not a CSV that can be opened in Excel.

Sub readCSVLikeFile()
    r = 1
    Open "<path of the file> For Input As #1
    While Not EOF(1)
        Line Input #1, txtline
        v = Split(txtline, ",")
        Range(Cells(r, 1), Cells(r, UBound(v) + 1)) = v
        r = r + 1
    Wend
    Close #1

End Sub

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54797

QueryTable Import

You can do this:

Sub QueryImport()

    Const cSheet As Variant = "Sheet1"  ' Worksheet Name/Index
    Const cSource As String = "A1"      ' Source Range

    Dim vntFile As Variant  ' Source Array

    vntFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    If vntFile <> False Then
        With ThisWorkbook.Worksheets(cSheet).QueryTables _
                .Add(Connection:="TEXT;" & vntFile, _
                Destination:=ThisWorkbook.Worksheets(cSheet).Range(cSource))
            .Name = "Pets"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = xlWindows
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End If
End Sub

which will open a dialog where you can pick the file, which will then be imported to Excel, and then you can manipulate it further which is out of scope due to lack of information. Post part of the result in another question to get the desired result.

Upvotes: 2

Related Questions