使用ms访问将多个文件合并为一个文本

问题描述：

我想将选定的文件合并到一个文本中使用访问。

我曾尝试没有成功这个代码，因为创建一个文本文件，但为空

任何一个可以帮助我在这？

在此先感谢，我的代码如下。

线路中的文本文件：

xN;xDate;xNode;xCO; 
100;2017-09-26 00:00:00;Valley;D6; 
101;2017-09-25 00:00:00;Valley;D3; 
... 
...

代码：

Dim xPath 
Function xExtract() 
    Dim xArray() As Variant 
    Dim I As Integer 
    Dim StrFileName As String 

    xPath = CurrentProject.Path 

PDS: 
xArray = Array("D1", "D2", "D3", "D4", "D5", "D6") 

        new_file = "" & xPath & "\PDS.txt" 

        fn = FreeFile 
        Open new_file For Output As fn 
        Close 
        For I = 0 To UBound(xArray) 

        StrFileName = "\\myserver\inetpub\ftproot\PDS_" & xArray(I) & ".txt" 

         fn = FreeFile 
         Open StrFileName For Input As fn 
         Open new_file For Append As fn + 1 

         Line Input #fn, dato 

         Do While Not EOF(fn) 
         Line Input #fn, dato 
         dati = Split(dato, Chr(9)) 
         For d = 0 To UBound(dati) 
          If d = 0 Then 
           dato = Trim(dati(d)) 
          Else 
           dato = dato & ";" & Trim(dati(d)) 
          End If 
         Next 

         Print #fn + 1, dato 

         Loop 
         Close 
        Next I 

    Application.Quit 
End Function

这是为什么设置两次？ fn = FreeFile –

Access有用于导入和导出分隔文本文件的工具。只需链接到文件，创建一个“联合”查询来统一数据，并将此联合查询导出到分隔文本文件。查看“导入/导出协议”，以分号为分号。让Access为你做好工作...... – marlan

@DougCoats实际上是有效的。 FreeFile返回一个未使用的文件指针。如果你想打开另一个文件，你应该得到一个新的未使用的指针。 –

答

当你的代码工作与Windows EOL格式（CR（回车）+ LF（换行））的文件，我猜你的文件是UNIX EOL格式（只是LF，没有CR），请用像例如一样的文本编辑器检查Notepad ++（查看 - >显示符号 - >显示行尾）。这会导致Line Input在CR中断时读取整行文件。然后你跳过第一行，没有插入任何内容，因为所有文本都在这一行。

您可以使用FileSystemObject来避免这种情况，因为它在LF上断裂。

Function xExtract() 

Const ForReading = 1, ForWriting = 2, ForAppending = 8 'iomode constants 
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 'format constants 
Dim xArray As Variant, dati As Variant 
Dim i As Long, d As Long 
Dim xPath As String, new_file As String, dato As String, StrFileName As String 
Dim FSO As Object, TextStreamIn As Object, TextStreamOut As Object 


xPath = CurrentProject.Path 
new_file = xPath & "\PDS.txt" 

xArray = Array("D1", "D2", "D3", "D4", "D5", "D6") 


Set FSO = CreateObject("Scripting.FileSystemObject") 

Set TextStreamOut = FSO.OpenTextFile(new_file, ForWriting, True, TristateUseDefault) 'open textstream to write 

For i = 0 To UBound(xArray) 'loop through files 
    StrFileName = "\\myserver\inetpub\ftproot\PDS_" & xArray(i) & ".txt" 

    Set TextStreamIn = FSO.OpenTextFile(StrFileName, ForReading) ' open textstream to read 

    TextStreamIn.SkipLine 'skip first line with headers 

    Do Until TextStreamIn.AtEndOfStream 'loop through lines 
     dati = Split(TextStreamIn.Readline, Chr(9)) 
     For d = 0 To UBound(dati) 
      If d = 0 Then 
       dato = Trim(dati(d)) 
      Else 
       dato = dato & ";" & Trim(dati(d)) 
      End If 
     Next 
     TextStreamOut.WriteLine dato 'write line to file 
    Loop 
    TextStreamIn.Close 'close textstream 
Next i 'next file 

TextStreamOut.Close 
Set TextStreamOut = Nothing 
Set TextStreamIn = Nothing 
Set FSO = Nothing 

Application.Quit 
End Function

如果你想留在Open file可以拆分对LF（Split(dato,vbLf）的第一个（也是唯一一个）线和忽略的第一个元素，但你必须检查该文件是UNIX EOL格式，FSO涵盖。

使用ms访问将多个文件合并为一个文本

相关推荐