方法一:逐行输出
SUB 将查询结果保存为CSV ()
If CheckBox1.Value = True Then
TEX = "A.EUTRANCELLFDD"
ElseIf CheckBox2.Value = True Then
TEX = "A.EUTRANCELLTDD"
End If
Dim cnn, SQL$, A, B, C, D, E, f, G, H, i, L, M
Set cnn = CreateObject("adodb.connection")
Set RS = CreateObject("adodb.recordset")
cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source =" & ThisWorkbook.Path & "\"
SQL = "SELECT ERBS," & TEX & " AS CELL,(IIF(INSTR(" & TEX & ",'D-')>0,'TDD D',IIF(INSTR(" & TEX & ",'F-')>0,'TDD F',IIF(INSTR(" & TEX & ",'GS-')>0,'FDD900',IIF(INSTR(" & TEX & ",'DC-')>0,'FDD1800',IIF(INSTR(" & TEX & ",'A-')>0,'TDD A','TDD E')))))) AS 频段 FROM [KPI.CSV] A GROUP BY " & TEX & ",ERBS"
Set RS = cnn.Execute(SQL)
ARR = cnn.Execute(SQL).GETROWS
BRR = RS.Fields.Count 'RS.FIELDS.COUNT
Open ThisWorkbook.Path & "\小区列表.csv" For Output As #1
Print #1, RS.Fields(0).Name + Chr(44) + RS.Fields(1).Name + Chr(44) + RS.Fields(2).Name
For J = 0 To UBound(ARR, 2) '列数
'For L = 0 To BRR - 1
Print #1, ARR(0, J) + Chr(44) + ARR(1, J) + Chr(44) + ARR(2, J)
Next
Close #1
RS.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Worksheets("结果").Activate
MsgBox ("OK")
END SUB
方法二:按行(细节决定成败)
Set cnn = CreateObject("ADODB.CONNECTION")
Set RS = CreateObject("ADODB.RECORDSET")
cnn.Mode = adModeReadWrite
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Data Source=" & ThisWorkbook.Path & "\"
SQL = "select REPLACE(IP,'.log','') AS IP,EUtranCell,EUtranFreqRelation,EUtranCellRelation,cellIndividualOffsetEUtran,ishoallowed,qoffsetcelleutran,[loadBalancing(0:NOT_ALLOWED/1:ALLOWED)],[sCellCandidate(1:ALLOWED/2:AUTO)] from [EUtranCellRelation.CSV]"
Set RS = cnn.Execute(SQL) '将SQL语句获得的数据传
Open ThisWorkbook.Path & "\output.CSV" For Output As #1
For M = 0 To RS.Fields.Count - 1
Print #1, RS.Fields(M).Name + Chr(44);
Next
Print #1, '\n'
arr = cnn.Execute(SQL).GetRows
brr = RS.Fields.Count 'RS.FIELDS.COUNT
For j = 0 To UBound(arr, 2) '列数
For L = 0 To brr - 1
Print #1, arr(L, j) + Chr(44); '''';分号表示不换行
Next
Print #1, '\n'
Next
Close #1
cnn.Close
Set rst = Nothing
Set cnn = Nothing
MsgBox "OK"
VBA+ADO输出到CSV(注意细节)
最新推荐文章于 2024-01-21 23:36:23 发布