Query .csv File

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
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

Rich (BB code):
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

Rich (BB code):
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
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top