Query .csv File

[Hashiru]

Hi All,

I am trying to query csv files to extract some fields and filter the records and return the resulting recordset to a new csv file.

I have adopted two sets of codes by finds it difficult to accomplish the above

Sub GetMyCSVData()
Dim xlcon As ADODB.Connection
Dim xlrs As ADODB.Recordset
Dim strSQL As String
Set xlcon = New ADODB.Connection
Set xlrs = New ADODB.Recordset
 
Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer
 
currentDataFilePath = "C:\Users\hashi\Desktop\CSVFolderPivotTable\"
currentDataFileName = "MSQueryCurrent-7492298.csv"
strSQL = "SELECT `MSQueryCurrent-7492298`.FAIN, `MSQueryCurrent-7492298`.Fund, `MSQueryCurrent-7492298`.Scope, `MSQueryCurrent-7492298`.ALI, `MSQueryCurrent-7492298`.Project, `MSQueryCurrent-7492298`.Activity, `MSQueryCurrent-7492298`.ResourceID, `MSQueryCurrent-7492298`.Accounting, `MSQueryCurrent-7492298`.SystemSource, `MSQueryCurrent-7492298`.Voucher, `MSQueryCurrent-7492298`.VendorName, `MSQueryCurrent-7492298`.Invoice, `MSQueryCurrent-7492298`.`Invoice Date`, `MSQueryCurrent-7492298`.`PO Contract`, `MSQueryCurrent-7492298`.`PO#`, `MSQueryCurrent-7492298`.`ACT Amount`, `MSQueryCurrent-7492298`.RMBAmount, `MSQueryCurrent-7492298`.UTLAmount, `MSQueryCurrent-7492298`.Type, `MSQueryCurrent-7492298`.Package" & _
"FROM `MSQueryCurrent-7492298.csv` `MSQueryCurrent-7492298`" & _
"WHERE (`MSQueryCurrent-7492298`.SystemSource='BAP') OR (`MSQueryCurrent-7492298`.FAIN Like '%DC-*%') OR (`MSQueryCurrent-7492298`.SystemSource='BGL') OR (`MSQueryCurrent-7492298`.FAIN Like '%DC-*%') OR (`MSQueryCurrent-7492298`.SystemSource='BTL') OR (`MSQueryCurrent-7492298`.FAIN Like '%DC-*%')" & _
"ORDER BY `MSQueryCurrent-7492298`.Accounting" & ";"
 
xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
 
xlcon.Open
 
xlrs.Open strSQL
xlrs.MoveFirst
nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset xlrs
 
xlrs.Close
xlcon.Close
 
Set xlrs = Nothing
Set xlcon = Nothing
End Sub

Instead of copying the recordset to Excel Spreadsheet I only have limited choices as the resulting set will and potentially will be more than Excel row limit (more than a million.

I will like the result saved in a new csv file like this

Sub CSVFile()
Dim strPath As String
Dim Conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim StrData As String
Dim StrHeader As String
Dim strSQL As String
Dim Fld As Variant

strPath = "C:\Users\hashi\Desktop\File\ZalexCorp Restaurant Equipment and Supply.accdb"
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath & ";"
Conn.CursorLocation = adUseClient
strSQL = "Select Customer_Name, Address, State, City FROM Dim_Customers"
Set rst = Conn.Execute(CommandText:=strSQL, Options:=adCmdText)
'save the recordset as a tab-delimited file
StrData = rst.GetString(StringFormat:=adClipString, ColumnDelimeter:=",", RowDelimeter:=vbCr, nullexpr:=vbNullString)
For Each Fld In rst.Fields
    StrHeader = StrHeader + Fld.Name & vbTab
Next
Open "C:\Users\hashi\Desktop\File\MyFile.csv" For Output As #1 
Print #1 , StrHeader
Print #1 , StrData
Close #1 
rst.Close
Conn.Close
Set rst = Nothing
Set Conn = Nothing
End Sub

How do I combined the above sets of code to achieve the objects thus set.

Thanks

 

[jimrward]

You need to define the layout of the csv file in a file called schema.ini as the system doesn’t know the format unlike a query from sql or access
There are a few examples on the forum here or try a google search, I am away from my home pc so cannot provide any code examples until later in the day

This will allow you to select the few fields you require and then write the recordset to a new file

 

[Hashiru]

Thanks Jimrward, I will greatly appreciate such examples

 

[jimrward]

Here is a link to a post I replied to 4 years ago, use the code in the last section the first posting is flawed


https://www.mrexcel.com/forum/excel-questions/749070-retrieving-data-via-ado.html?highlight=Jimrward