英文:
Exporting VBA sql Excel query to csv
问题
使用以下代码，我能够对Excel表格执行一些基本的SQL查询。现在，我需要将整个结果集（分配给变量result）导出到CSV文件中。我应该如何做到这一点？
Sub MyMethod()
'--- 声明变量来存储连接、结果和SQL查询
Dim connection As Object, result As Object, sql As String, recordCount As Integer
'--- 连接到当前Excel文件的数据源
Set connection = CreateObject("ADODB.Connection")
With connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'--- 编写SQL查询。在这种情况下，我们将手动选择数据范围
'--- 以打印表格的所有信息
'-- full_name = [first name] + " " + [last name]
sql = "SELECT [id], [first name], [last name], [salary], [tax], [salary] - [tax] as saldo, [first name] & chr(32) & [last name] as fullInfo FROM [miTabla1$A1:E3]"
'--- 运行SQL查询
Set result = connection.Execute(sql)
For i = 0 To result.Fields.Count - 1
Debug.Print result.Fields(i).Name
Next i
'--- 提取信息
Do
' 打印结果的每一列信息
Debug.Print result(0) & ";" & result(1) & ";" & result(2) & ";" & result(3) & ";" & result(4) & ";" & result(5) & ";" & result(6)
result.MoveNext
recordCount = recordCount + 1
Loop Until result.EOF
'--- 打印结果的数量
Debug.Print vbNewLine & recordCount & " results found."
End Sub
请注意，上述代码执行了SQL查询并将结果打印到Debug窗口。如果要将结果导出到CSV文件中，你需要编写代码来创建CSV文件并将结果写入其中。
英文:
Using the next code I am able to do some basic sql queries against an excel sheet. Now, I need to export the whole result set (assigned to the variable result) into a CSV file. How can I do this?
Sub MyMethod()
'--- Declare Variables to store the connection, the result and the SQL query
Dim connection As Object, result As Object, sql As String, recordCount As Integer
'--- Connect to the current datasource of the Excel file
Set connection = CreateObject("ADODB.Connection")
With connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'--- Write the SQL Query. In this case, we are going to select manually the data range
'--- To print the whole information of the table
'-- full_name = [first name] + " " + [last name]
sql = "SELECT [id], [first name], [last name], [salary], [tax], [salary] - [tax] as saldo, [first name] & chr(32) & [last name] as fullInfo FROM [miTabla1$A1:E3]"
'--- Run the SQL query
Set result = connection.Execute(sql)
For i = 0 To result.Fields.Count - 1
Debug.Print result.Fields(i).Name
Next i
'--- Fetch information
Do
' Print the information of every column of the result
Debug.Print result(0); ";" & result(1) & ";" & result(2) & ";" & result(3) & ";" & result(4); ";" & result(5) & ";" & result(6)
result.MoveNext
recordCount = recordCount + 1
Loop Until result.EOF
'--- Print the amount of results
Debug.Print vbNewLine & recordCount & " results found."
End Sub
答案1
得分: 2
将以下内容翻译为中文：
给定结果对象和完整文件路径的字符串作为参数，例如"C:\TESTS\FIRST.CSV"（CSV 是一个文本文件，逗号分隔的值）
Option Explicit
Sub createCSVfile(ByRef result, filePath As String)
Dim FSO As Object, csvFile As Object
Const litSeparator = ";" '或根据您的区域设置使用","
Set FSO = CreateObject("Scripting.FileSystemObject")
Set csvFile = FSO.CreateTextFile(filePath)
Do While Not result.EOF
csvFile.WriteLine result(0) & litSeparator & result(1) & litSeparator & result(2) & litSeparator & _
result(3) & litSeparator & result(4) & litSeparator & result(5) & litSeparator & result(6)
result.MoveNext
Loop
csvFile.Close
Set FSO = Nothing
Set csvFile = Nothing
End Sub
英文:
Give as parameters the result object and a string with the full path to create the csv file eg. "C:\TESTS\FIRST.CSV" (csv is a text file -comma separated values-)
Option Explicit
Sub createCSVfile(ByRef result, filePath As String)
Dim FSO As Object, csvFile As Object
Const litSeparator = ";" 'OR USE "," ACCORDING YOUR REGIONAL SETTINGS
Set FSO = CreateObject("Scripting.FileSystemObject")
Set csvFile = FSO.CreateTextFile(filePath)
Do While Not result.EOF
csvFile.WriteLine result(0) & litSeparator & result(1) & litSeparator & result(2) & litSeparator & _
result(3) & litSeparator & result(4) & litSeparator & result(5) & litSeparator & result(6)
result.MoveNext
Loop
csvFile .Close
Set FSO = Nothing
Set csvFile = Nothing
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论