导出查询输出到一个文本文件 [英] exporting a query output to a text file

问题描述

我有我试图用下面的code导出到文本文件的访问查询：

I have an access query which I am trying to export to a text file using the following code:

DoCmd.TransferText acExportFixed, "Export Specification", _
    "Test Query", "C:\Users\Documents\TestOutput.txt", True

我遇到的问题是：输出文件TestOutput.txt与固定宽度显示的数据，但该列标题逗号分隔。我想列标题是固定的宽度了。

The issue I am having is: The output file "TestOutput.txt" has the data displayed with fixed width but the column headers are comma delimited. I want the column headers to be fixed width too.

你会列标题不会显示相同数据的其他人呢？

What would column headers not be displayed same as the rest of the data?

推荐答案

AFAICT，这是TransferText中不可避免的功能。它似乎缺乏任何一种内置智能地说：好吧，我们出口的acExportFixed，所以让我们来看看在导出规范和输出使用这些相同宽度的列标题定义的列宽。相反，它只是给了列名作为一个逗号分隔的列表。

AFAICT, that is an unavoidable "feature" of TransferText. It seems to lack any kind of built-in intelligence to say "OK, we're exporting as acExportFixed, so let's examine the column widths defined in Export Specification and output the column headers using those same widths". Instead it just gives the column names as a comma-separated list.

与其他一切访问，当它的默认行为不满意，您可以编写VBA code做你的方式。

As with everything else in Access, when its default behaviors are unsatisfactory, you can write VBA code to do it your way.

Const VB_FORREADING = 1
Const VB_FORWRITING = 2
Const cstrFile As String = "C:\Users\Documents\TestOutput.txt"
Const cstrHeaderRow As String = "col1    col2    etc..."
Dim oFSO As Object
Dim oFile As Object
Dim strContents As String

' do TransferText without the field names '
' (HasFieldNames default = False) '
DoCmd.TransferText acExportFixed, "Export Specification", _
    "Test Query", cstrFile 

Set oFSO = CreateObject("Scripting.FileSystemObject")
' read file content into strContents string variable '
Set oFile = oFSO.OpenTextFile(cstrFile, VB_FORREADING)
strContents = oFile.ReadAll
oFile.Close
' re-write file using cstrHeaderRow plus strContents '
Set oFile = oFSO.OpenTextFile(cstrFile, VB_FORWRITING)
oFile.write cstrHeaderRow & vbCrLf & strContents
oFile.Close

Set oFile = Nothing
Set oFSO = Nothing

这篇关于导出查询输出到一个文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！