将所有值作为文本传递的连接字符串

I'm struggling with importing data into excel sheet from text file with header line

我在用标题行从文本文件中导入数据到excel表格中

Values in the text file are separated with tab, therefore I had to create Schema.ini file, which is saved in the same folder as text file:

文本文件中的值与选项卡分开，因此我必须创建模式。ini文件，保存在与文本文件相同的文件夹中:

[test no1.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
Col1="Column number 1" Float
Col2="Column number 2" Text
Col3="Column number 3" Text

I'm selecting all values from text file into recordset. Then, I'm using this connection string to open target excel:

我从文本文件中选择所有的值到记录集。然后，我使用这个连接字符串来打开target excel:

Public Function getXlsConn() As ADODB.Connection
    Dim rv As New ADODB.Connection
    Dim strConn As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & targetFileName & ";" & _
    "Extended Properties=""Excel 12.0;HDR=YES;IMEX=0"";"

    rv.Open strConn
    Set getXlsConn = rv
End Function

I get target recordset (range in excel sheet), I'm looping through all lines of source recordset (data from text file) and passing them into target recordset. At the end of loop I'm updating target recordset with command UpdateBatch:

我获得目标记录集(excel表中的范围)，我循环遍历源记录集的所有行(来自文本文件的数据)并将它们传递到目标记录集。在循环结束时，我使用命令UpdateBatch更新目标记录集:

Sub CopyToXls(pRecordSet As ADODB.Recordset, pSheetName As String)
    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim i As Long
    Dim size As Integer
    Dim fieldsArray() As Variant
    Dim values() As Variant

    Set con = getXlsConn()

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseServer
    'header starts from 2nd row
    rs.Open "select *  from [" & pSheetName & "$A2:C600000]", con, _
             adOpenDynamic, adLockOptimistic

    'get number of columns and their names
    size = rs.Fields.Count - 1
    ReDim values(size)
    ReDim fieldsArray(size)
    For i = 0 To size
        fieldsArray(i) = rs.Fields(i).Name
    Next i

    'get end of file
    If rs.EOF = False Then
        rs.MoveFirst
    End If

    'copy rows from source recordset (text file) to target recordset (excel sheet)
    Do Until pRecordSet.EOF = True
            For i = 0 To size
                    values(i) = pRecordSet.Fields(i).Value
            Next i
            rs.AddNew fieldsArray, values
        pRecordSet.MoveNext
        rs.MoveNext
    Loop

    rs.UpdateBatch

    rs.Close
    Set rs = Nothing
    Set con = Nothing

End Sub

Unfortunately, all values are passed as text, therefore SUM function (located in A1 cell) for first column is not working.

不幸的是，所有值都作为文本传递，因此第一列的SUM函数(位于A1单元格中)不起作用。

I've tried to change IMEX parameter of connection string - for values 1 and 2 I'm getting error "Cannot update. Database object is read-only".

我尝试改变连接字符串的IMEX参数-对于值1和2，我得到错误"无法更新。数据库对象是只读的。

I would like to pass values exactly as I defined them in Schema.ini file. Is this possible?

我希望传递的值与我在模式中定义的值完全相同。ini文件。这是可能的吗?

1 个解决方案

#1

Here is a general method to connect to a Text document.

这里有一个连接到文本文档的通用方法。

My example text file looks like this:

我的示例文本文件如下所示:

1,a,b
1,a,b
1,a,b
1,a,b

For simplicity I just made the delimiter a comma.

为了简单起见，我将分隔符设置为逗号。

Here's the code I'm using. One special note, if you have a different delimiter you'll need to change the delimiter type. I've noted that section of the code.

这是我使用的代码。特别注意，如果您有不同的分隔符，您将需要更改分隔符类型。我已经注意到了这段代码。

Public Sub OutputToExcel()
    Dim mySheet     As Worksheet: Set mySheet = ThisWorkbook.Sheets("Sheet1")
    Dim FolderPath  As String: FolderPath = "C:\Users\Megatron\Desktop\"
    Dim SQL         As String: SQL = "SELECT CDbl(F1) as Field1, " & _
                                     "Cstr(F2) as Text1, CStr(F3) as Text2 " & _
                                     "FROM MyFile.txt"

    Dim myRs        As ADODB.Recordset: Set myRs = New ADODB.Recordset
    Dim conn        As ADODB.Connection: Set conn = New ADODB.Connection

    'Change the FMT=Delimited to FMT=TabDelimited,
    'or continue using the schema.ini which I prefer
    Dim connstr     As String: connstr = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
                                         "Data Source=" & FolderPath & _
                                         ";Extended Properties='text;HDR=No;FMT=Delimited';"
    'Open a connection
    With conn
        .connectionstring = connstr
        .Open
    End With

    'Read the data
    myRs.Open SQL, conn, adOpenForwardOnly, adLockOptimistic

    'Output the data
    mySheet.Range("A1").CopyFromRecordset myRs

    'Clean Up
    If myRs.State = adStateOpen Then myRs.Close: Set myRs = Nothing
    If conn.State = adStateOpen Then conn.Close: Set conn = Nothing
End Sub

#1