VBA将分隔符文本文件转换为Excel [英] VBA Convert delimiter text file to Excel

问题描述

我有一个代码将文件从.txt（使用|分隔符）转换为xslx，
的代码，但代码对于某些文件（当我打开它在excel中）工作正常，其他错误，当我尝试通过excel功能区（从文本获取外部数据 - >导入）时，文件是正确的。

这是我的代码： / p>

  Sub tgr（）
 
 Const txtFldrPath As String =C：\ ... \ txtFiles
 Const xlsFldrPath As String =C：\excelFiles
 
 Dim CurrentFile As String：CurrentFile = Dir（txtFldrPath&\&* .txt） 
 Dim strLine（）As String 
 Dim LineIndex As Long 
 
 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False 
 While CurrentFile<> vbNullString 
 LineIndex = 0 
关闭＃1 
打开txtFldrPath& \& CurrentFile输入为＃1 
虽然不是EOF（1）
 LineIndex = LineIndex + 1 
 ReDim保留strLine（1到LineIndex）
行输入＃1，strLine（LineIndex） 
 Wend 
关闭＃1 
 
使用ActiveSheet.Range（A1）。调整大小（LineIndex，1）
 .Value = WorksheetFunction.Transpose（strLine）
 .TextToColumns其他：= True，OtherChar：=| 
结束
 
 ActiveSheet.UsedRange.EntireColumn.AutoFit 
 ActiveSheet.Copy 
 ActiveWorkbook.SaveAs xlsFldrPath& \&替换（CurrentFile，.txt，.xlsx），xlOpenXMLWorkbook 
 ActiveWorkbook.Close False 
 ActiveSheet.UsedRange.ClearContents 
 
 CurrentFile = Dir 
 Wend 
 Application.DisplayAlerts = True 
 Application.ScreenUpdating = True 
 End Sub 
  

这张图片显示了excel和原始文件.txt中的结果

解决方案

首先，你有坏的数据。 AI060616.txt中总负债和 30,619,676.00 之间的标签字符。 Excel不喜欢单元格内容的选项卡。实际上，制表符是TextToColumns命令中的默认分隔符，当数组被引入时，它将转换为两列数据。

 打开txtFldrPath& \& CurrentFile输入为＃1 
虽然不是EOF（1）
 LineIndex = LineIndex + 1 
 ReDim保留strLine（1到LineIndex）
行输入＃1，strLine（LineIndex） 
'STRIP TABS OUT并更换空间!!!!! 
 strLine（LineIndex）=替换（strLine（LineIndex），Chr（9），Chr（32））
 Wend 
关闭＃1 
  / pre> 
 
 
接下来， 
 
I have a code that convert files in folder from .txt (with "|" separator) to xslx, 
But the code works fine for some files (when I open it in excel), the others are wrong, when I try to import the wring one manually by excel ribbon(Get external data -- > from text), the files are correct.

This is my code :
Sub tgr()

    Const txtFldrPath As String = "C:\...\txtFiles"     
    Const xlsFldrPath As String = "C:\excelFiles"     

    Dim CurrentFile As String: CurrentFile = Dir(txtFldrPath & "\" & "*.txt")
    Dim strLine() As String
    Dim LineIndex As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    While CurrentFile <> vbNullString
        LineIndex = 0
        Close #1
        Open txtFldrPath & "\" & CurrentFile For Input As #1
        While Not EOF(1)
            LineIndex = LineIndex + 1
            ReDim Preserve strLine(1 To LineIndex)
            Line Input #1, strLine(LineIndex)
        Wend
        Close #1

        With ActiveSheet.Range("A1").Resize(LineIndex, 1)
            .Value = WorksheetFunction.Transpose(strLine)
            .TextToColumns Other:=True, OtherChar:="|"
        End With

        ActiveSheet.UsedRange.EntireColumn.AutoFit
        ActiveSheet.Copy
        ActiveWorkbook.SaveAs xlsFldrPath & "\" & Replace(CurrentFile, ".txt", ".xlsx"), xlOpenXMLWorkbook
        ActiveWorkbook.Close False
        ActiveSheet.UsedRange.ClearContents

        CurrentFile = Dir
    Wend
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

this picture show the result in excel and the original file .txt
解决方案
First off, you have bad data. There is a tab character between Total liabilities and 30,619,676.00 in AI060616.txt. Excel doesn't like tabs in cell content. In fact, the tab character is the default delimiter on a TextToColumns command and this translates into two column of data when the array is brought in.
    Open txtFldrPath & "\" & CurrentFile For Input As #1
    While Not EOF(1)
        LineIndex = LineIndex + 1
        ReDim Preserve strLine(1 To LineIndex)
        Line Input #1, strLine(LineIndex)
        'STRIP TABS OUT AND REPLACE WITH A SPACE!!!!!
        strLine(LineIndex) = Replace(strLine(LineIndex), Chr(9), Chr(32))
    Wend
    Close #1
Next, the Range.TextToColumns method 'remembers' all of the settings that were used the last time it was run through; it doen't matter whether that was by the user on hte worksheet or through VBA. You need many more parameters than you have supplied in order to guarantee that it is going to operate the way you want.
    With ActiveSheet.Range("A1").Resize(LineIndex, 1)
        .Value = WorksheetFunction.Transpose(strLine)
        'DEFINE THE OPERATION FULLY!!!!
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                       Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
                       Other:=True, OtherChar:="|"
    End With


                        
这篇关于VBA将分隔符文本文件转换为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！