时间戳

首页 > 解决方案 > 如何将csv的第二和第三行读取为一行

excel - 如何将csv的第二和第三行读取为一行

问题描述

我的 Excel 读取 CSV 文件以获取网格表的数据。

"header", "header", "header", "header"
"value1", 
"value2", "value3", "value4" 
"value5", "value6", "value7", "value8"
"value9", "value10", "value11", "value12"

我想读取 CSV 的第二行和第三行作为网格表的第一行。其他行一一读取。

我的代码是：

Dim FileName As String, folder As String    
    folder = ThisWorkbook.Path & "\"
    FileName = Dir(ThisWorkbook.Path & "\*.csv")


    With ActiveSheet.QueryTables _
            .Add(Connection:="TEXT;" & folder & FileName, Destination:=ActiveCell)
            .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:=True
        End With

我的方法：我正在尝试用一个新文件修改 csv 文件，该文件将第二行和第三行合并为第二行。

filePath = folder & fileName
 
 Dim fName As String, fso As Object, fsoFile As Object, txt As Variant

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set fsoFile = fso.OpenTextFile(filePath, 1)
    txt = fsoFile.ReadAll
    fsoFile.Close

    txt = Split(txt, vbNewLine)

    txt(2 - 1) = "some text with special characters like & Char(44) & and & Chr(10) & and so on"

    Set fsoFile = fso.OpenTextFile(filePath, 2)
    fsoFile.Write Join(txt, vbNewLine)
    fsoFile.Close

问题是网格表在单元格内将特殊字符显示为 & Char(44) & 和 & Char(10) & ...

标签： excelvbacsv

解决方案

结合第 2 行和第 3 行的三种方法

Sub merge23()

    Dim fso As Object, tsIn, tsOut
    Dim s As String
    Set fso = CreateObject("Scripting.Filesystemobject")
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    Set tsOut = fso.CreateTextFile("C:\temp\test1.csv", 1)
    
    ' method 1
    Do While tsIn.AtEndOfLine <> True
        s = tsIn.readline
        If tsIn.Line <> 3 Then
            s = s & vbCrLf
        End If
        tsOut.write s
    Loop
    tsIn.Close
    tsOut.Close
    
    ' method 2
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    s = tsIn.readall
    tsIn.Close
    s = Replace(s, vbCrLf, "~#~#~", 1, 1) 'mark 1st crlf
    s = Replace(s, vbCrLf, "", 1, 1) ' replace 2nd
    s = Replace(s, "~#~#~", vbCrLf, 1, 1) ' replace 1st crlf
    Set tsOut = fso.CreateTextFile("C:\temp\test2.csv", 1)
    tsOut.writeline s
    
    ' method 3 regex
    Dim regex
    Set regex = CreateObject("vbscript.regexp")
    With regex
       .Global = False
       .MultiLine = True
       .Pattern = "^(.*\r\n.*)\r\n" ' 2nd crlf
    End With
    
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    s = tsIn.readall
    tsIn.Close
    
    Set tsOut = fso.CreateTextFile("C:\temp\test3.csv", 1)
    s = regex.Replace(s, "$1")
    tsOut.writeline s
    tsOut.Close
End Sub

推荐阅读