VBA读取CSV内容方法
目录1、vba+ado读取当前目录下的CSV数据并保存为csv或本sheet中21.1 vba+ado读csv查询结果存为csv文件(逐行输出)21.2 vba+ado读csv查询结果另存为csv文件(逐个字段输出不换行)21.3 vba+ado读csv查询结果输出到本工作簿中的sheet32、vba+ado读取当前工作数据41、vba+ado读取当前目录下的CSV数据并保存为csv或本sheet中1.1 vba+ado读csv查询结果存为csv文件(逐行输出)SUB 将查询结果保存为CSV_1 ()Dim cnn, SQL$ 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 * FROM KPI.CSV A GROUP BY ERBS"Set RS = cnn.Execute(SQL) ARR = cnn.Execute(SQL).GETROWSBRR = RS.Fields.Count 'RS.FIELDS.COUNTOpen ThisWorkbook.Path & "小区列表.csv" For Output As #1Print #1, RS.Fields(0).Name + Chr(44) + RS.Fields(1).Name + Chr(44) + RS.Fields(2).NameFor J = 0 To UBound(ARR, 2) '列数'For L = 0 To BRR - 1Print #1, ARR(0, J) + Chr(44) + ARR(1, J) + Chr(44) + ARR(2, J)NextClose #1RS.Closecnn.CloseSet rst = NothingSet cnn = NothingWorksheets("结果").ActivateMsgBox ("OK")END SUB1.2 vba+ado读csv查询结果另存为csv文件(逐个字段输出不换行)SUB 将查询结果保存为CSV_2()Set cnn = CreateObject("ADODB.CONNECTION")Set RS = CreateObject("ADODB.RECORDSET")cnn.Mode = adModeReadWritecnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)""Data Source=" & ThisWorkbook.Path & "" SQL = "select * from EUtranCellRelation.CSV"Set RS = cnn.Execute(SQL) '将SQL语句获得的数据传 Open ThisWorkbook.Path & "output.CSV" For Output As #1For M = 0 To RS.Fields.Count - 1 Print #1, RS.Fields(M).Name + Chr(44); Next Print #1, 'n'arr = cnn.Execute(SQL).GetRowsbrr = RS.Fields.Count 'RS.FIELDS.COUNTFor j = 0 To UBound(arr, 2) '列数For L = 0 To brr - 1Print #1, arr(L, j) + Chr(44); ''''分号表示不换行NextPrint #1, 'n'NextClose #1cnn.CloseSet rst = NothingSet cnn = NothingMsgBox "OK"END SUB1.3 vba+ado读csv查询结果输出到本工作簿中的sheetSub ado读CSV存sheet()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 * FROM TEST.CSV Set rs = cnn.Execute(SQL)Sheets("结果").Cells.ClearContentsSheets("结果").Range("a2").CopyFromRecordset rs For i = 0 To rs.Fields.Count - 1 Sheets("结果").Cells(1, i + 1) = rs.Fields(i).Name NextWorksheets("结果").ActivateMsgBox ("OK") END SUB2、vba+ado读取当前工作数据SUB ado读工作簿中sheet数据()Dim cnn, SQL$ Set cnn = CreateObject("adodb.connection") Set rs = CreateObject("adodb.recordset") cnn.Open "provider=microsoft.ace.oledb.12.0;Extended Properties =excel 12.0;data source=" & ThisWorkbook.FullName a = “SELECT * FROM 结果$ " Set rs = cnn.Execute(SQL) Sheets("修改前后输出").Cells.ClearContents Sheets("修改前后输出").Range("a2").CopyFromRecordset rs For i = 0 To rs.Fields.Count - 1 Sheets("修改前后输出").Cells(1, i + 1) = rs.Fields(i).Name Nextrs.Closecnn.CloseSet rst = NothingSet cnn = NothingWorksheets("修改前后输出").ActivateMsgBox ("OK")END SUB
