vba 将 Access 查询结果导出到 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13309994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export Access query results to csv
提问by cheshirepine
I have an access database which manipulates data from a Magento e-commerce store, reformats the data and (hopefully!) spits out a CSV file which can then be imported into ebay Turbolister for mass upload to eBay.
我有一个访问数据库，它处理来自 Magento 电子商务商店的数据，重新格式化数据并（希望如此！）吐出一个 CSV 文件，然后可以将其导入 ebay Turbolister 以批量上传到 eBay。
I have got as far as creating a query which correctly lays out the data into the format required by Turbolister.
我已经创建了一个查询，该查询将数据正确地排列为 Turbolister 所需的格式。
My issues are various (including some which appear to be related to Access' handling of large field contents), however the crux of my problem is that I am struggling to get working a simple script which exports the query results as a properly formatted CSV (including doubling up on double quotes where required inside a text value i.e. if the value itself contains quotes which need to be retained).
我的问题多种多样（包括一些似乎与 Access 处理大字段内容有关的问题），但是我问题的症结在于我正在努力使用一个简单的脚本来将查询结果导出为格式正确的 CSV（包括在文本值内需要的地方加倍双引号，即如果值本身包含需要保留的引号）。
The DoCmd.TransferText solution throws an error related to field size ('the field is too small to accept the amount of data you attempted to add') so thats no good.
DoCmd.TransferText 解决方案会引发与字段大小相关的错误（“该字段太小，无法接受您尝试添加的数据量”），因此这不好。
Has anyone got a good working CSV export routine in VBA that they can suggest?
有没有人在 VBA 中有一个很好的工作 CSV 导出例程，他们可以建议？
Cheers
干杯
采纳答案by Matt Donnan
This is an old function I sometimes used to use, it allows you to specify the delimeter, it also checks the data it's outputting and if it can't be evaluated to either a date or a numeric etc, then it uses double quotes:
这是我有时使用的一个旧函数，它允许您指定分隔符，它还检查它输出的数据，如果它不能被评估为日期或数字等，那么它使用双引号：
Public Function ExportTextDelimited(strQueryName As String, strDelimiter As String)
Dim rs As Recordset
Dim strHead As String
Dim strData As String
Dim inti As Integer
Dim intFile As Integer
Dim fso As New FileSystemObject
On Error GoTo Handle_Err
fso.CreateTextFile ("C:\Untitled.csv")
Set rs = Currentdb.OpenRecordset(strQueryName)
rs.MoveFirst
intFile = FreeFile
strHead = ""
'Add the Headers
For inti = 0 To rs.Fields.Count - 1
If strHead = "" Then
strHead = rs.Fields(inti).Name
Else
strHead = strHead & strDelimiter & rs.Fields(inti).Name
End If
Next
Open "C:\Untitled.csv" For Output As #intFile
Print #intFile, strHead
strHead = ""
'Add the Data
While Not rs.EOF
For inti = 0 To rs.Fields.Count - 1
If strData = "" Then
strData = IIf(IsNumeric(rs.Fields(inti).value), rs.Fields(inti).value, IIf(IsDate(rs.Fields(inti).value), rs.Fields(inti).value, """" & rs.Fields(inti).value & """"))
Else
strData = strData & strDelimiter & IIf(IsNumeric(rs.Fields(inti).value), rs.Fields(inti).value, IIf(IsDate(rs.Fields(inti).value), rs.Fields(inti).value, """" & rs.Fields(inti).value & """"))
End If
Next
Print #intFile, strData
strData = ""
rs.MoveNext
Wend
Close #intFile
rs.Close
Set rs = Nothing
'Open the file for viewing
Application.FollowHyperlink "C:\Untitled.csv"
Exit Function
Handle_Err:
MsgBox Err & " - " & Err.Description
End Function
It may need a couple of tweaks as I've taken out some bits which were only relevant to my particular case but this may be a starting point.
它可能需要进行一些调整，因为我已经删除了一些仅与我的特定情况相关的部分，但这可能是一个起点。
