How to Output csv,tsv From RecordSet
I tried to output a tsv file from an Access table, but surprisingly there was no easy way.
I will keep it for memoirs.
Contents
Code to Output csv,tsv From RecordSet
The outline of the process is as follows.
①Get header from RecordSet with delimiter
②Fetch data from RecordSet with delimiter
③Output header and data to file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
'==============================================' 'Purpose ：Output Csv or Tsv file to where Access file is located' 'Argument ：Delimiter Delimiter Delimiter: vbTab (tab), (comma)' ' ：Rs Record set to output' 'Return value:' '==============================================' Private Sub ExportRecordSet(Delimiter As String, Rs As DAO.Recordset) Dim strPath As String 'output path' Dim strTblName As String 'table name' Dim Header As String 'table header row' Dim DataRecords As String 'table's data rows' Dim Extension As String 'Extension' Dim RsCol As Long 'for column count' 'Specify output path' strPath = CurrentProject.Path & "\" '"' strTblName = "TABLE" '"' 'Set extension' If Delimiter = "," Then Extension = ".csv" ElseIf Delimiter = vbTab Then Extension = ".tsv" End If '"---------------Process to store header line---------------' 'loop until end the of columns' For RsCol = 0 To Rs.Fields.Count - 1 Header = Header & Rs.Fields(RsCol).Name & Delimiter Next RsCol 'Delete the comma or tab that arrived at the end of the line Header = Left(Header, Len(Header) - 1) '---------------Process to store data row---------------' 'Repeat from top record to EOF' Do Until Rs.EOF 'loop untill end the of columns' For RsCol = 0 To Rs.Fields.Count - 1 DataRecords = DataRecords & Rs.Fields(RsCol).Value & Delimiter Next RsCol 'Delete the comma or tab that arrived at the end of the line' DataRecords = Left(DataRecords, Len(DataRecords) - 1) 'new line' DataRecords = DataRecords & Chr(13) & Chr(10) 'To next record' Rs.MoveNext Loop '---------------Output to file---------------' Open strPath & strTblName & Extension For Output As #1 Print #1, Header & Chr(13) & Chr(10) & DataRecords Close #1 MsgBox "Output is complete." End Sub</span> |
I created an access file.
You can download from here.
Csv, tsv Output Result From RecordSet
Here is the result.
Table Contents
Csv and tsv is from table below.
| supplier_cd | zipcode | tel | supplier_name | fax |
| 301101 | 100-0005 | 000-001 | Cargill | 03-****-**** |
| 301102 | 651-0087 | 000-002 | Nestle | 4-****-**** |
| 301103 | 60601 | 000-003 | Archer Daniels Midland | 5-****-**** |
| 301104 | 153-8578 | 000-004 | Unilever | 6-****-**** |
| 301105 | 150-0002 | 000-005 | Coca-Cola | 7-****-**** |
| 301106 | 15222 | 000-006 | Kraft Heinz | 8-****-**** |
| 301107 | 104-0031 | 000-007 | Suntory | 9-****-**** |
| 301108 | 200000 | 000-008 | Lactalis | 10-****-**** |
Output Result
It was output well like this.
csv
tsv
I will introduce the method to get the data of the table to RecordSet in another page.
https://overseas.loosecarrot.com/2019/03/13/90
