CODEWITHSUNDEEP
vbaexcelado
luke_t
luke_t

Reputation: 2985

ADO text file query - results split by comma

I am querying a text file using ADO to bring the data into Excel.

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & filePath & ";" & _
        "Extended Properties='text';"

rs.Open "SELECT * FROM " & fileName, cn

wsImport.Range("A1").CopyFromRecordset rs

The issue I'm facing is that the query results are being split, by comma as a delimiter. This means that when I write the data to the worksheet, it's being output across several columns.

I've confirmed that it's being split at the query stage, and not at the writing of the data to the worksheet stage. I have to use rs.Fields(0) and rs.Fields(1) to access some rows of data, when I want all of the data to be accessible within the first field of the created recordset (allowing the data to be written to column A of the worksheet).

Can anyone clarify how I can query the text file, whilst not splitting the data by any delimiter?

I've also tried using the below within the Extended Properties.

Extended Properties='text;HDR=Yes;FMT=Delimited';

and

Extended Properties='text;HDR=Yes;FMT=FixedLength';

Upvotes: 0

Views: 1011

Answers (2)

ASH
ASH

Reputation: 20322

If you turn on the macro recorder and let it record you doing all the steps one time to import a file, you should have your answer.

I just tried it and got this.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\your_path_here\test.txt", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Upvotes: 0

Rory
Rory

Reputation: 34055

Here's a simple example of reading the text file:

Sub foo(filePath As String)
    Dim sDataIn                     As String
    Dim sDataTemp()                 As String
    Dim sDataOut()                  As String
    Dim n                           As Long

    Open filePath For Binary As #1
    sDataIn = Space$(LOF(1))
    Get #1, , sDataIn
    Close #1
    sDataTemp() = Split(sDataIn, vbNewLine)
    ReDim sDataOut(1 To UBound(sDataTemp) + 1, 1 To 1)
    For n = LBound(sDataTemp) To UBound(sDataTemp)
        sDataOut(n + 1, 1) = sDataTemp(n)
    Next n
    ActiveSheet.Range("A1").Resize(UBound(sDataOut), 1) = sDataOut
End Sub

Upvotes: 2

Related Questions