How to connect to a RECORDSET

[JKwan]

I ran a SQL command on a csv file into a recordset. Now, how do I run a SQL onto that newly created recordset? I am more interested on the "how to" setup the connection to that recordset.

[Kenneth Hobs]

Not sure what you are asking. Obviously, this site shows the various connection strings. http://www.connectionstrings.com/

Maybe this will help.

Sub pull_data()
Dim s1 As Worksheet, ML_Dir As String
ML_Dir = ThisWorkbook.Path
'Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, True, "Data Used"
Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, False, "F1"
End Sub


'Add Tools > References... > Microsoft ActiveX Data Objects 2.8 Library
Sub Open_Sort_CSV(CSV_Dir, CSV_name, Data_Sheet, Optional Header As Boolean = True, _
Optional SortField As String = "", Optional SortASC As Boolean = True)


Dim connectionString As String
'Late binding:
'Dim objConnection As Object, objRecordset As Object
' Early Binding:
Dim objConnection As Connection, objrecordset As Recordset
Dim A As Integer
Dim Location As Range, Rw As Long, col As Integer, c As Integer, MyField As Variant


'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = 1 '&H1

'set connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""text;HDR=" & Header & ";FMT=Delimited;IMEX=3"""
objConnection.Open connectionString

'get data from csv
Select Case True
Case SortField = ""
objrecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Case SortField <> "" And SortASC
objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` ASC", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Case SortField <> "" And SortASC = False
objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` DESC", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
End Select

'Loop across the fields
If Header Then
With objrecordset
For A = 0 To .Fields.Count - 1
' Add field names to data sheet
If Right(.Fields(A).Name, 7) <> ".NoName" Then _
ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).Offset(0, A).Value = .Fields(A).Name
Next A
End With
'copy data into worksheet under headers
ThisWorkbook.Worksheets(Data_Sheet).Cells(2, 1).CopyFromRecordset objrecordset
'Write RecordSet to results area
Set Location = ThisWorkbook.Worksheets(Data_Sheet).Range("A2")
Rw = Location.Row
col = Location.Column
c = col
With objrecordset
Do Until .EOF
For Each MyField In .Fields
Cells(Rw, c) = MyField
c = c + 1
Next MyField
.MoveNext
Rw = Rw + 1
c = col
Loop
End With



Else
'copy data into worksheet
ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).CopyFromRecordset objrecordset
End If


'end connection and recordset
Set objConnection = Nothing
Set objrecordset = Nothing

End Sub










'Pulls Data from CSV to Data sheet
Sub Open_Sort_CSV_o(CSV_Dir, CSV_name, Data_Sheet, Optional Header As String = "No")


Dim connectionString As String, objConnection As Object, objrecordset As Object
Dim A As Integer
Dim Location As Range, Rw As Long, col As Integer, c As Integer, MyField As Variant


'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
'connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""text;HDR=" & Header & ";FMT=Delimited;IMEX=1"""
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""text;HDR=" & Header & ";FMT=Delimited(,);"""
objConnection.Open connectionString

'get data from csv
objrecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

'Loop across the fields
If Header = "Yes" Then
With objrecordset
For A = 0 To .Fields.Count - 1

' Add field names to data sheet
ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).Offset(0, A).Value = .Fields(A).Name
Next A
End With

'this errors for no good reason so stop any errors
'On Error Resume Next

'copy data into worksheet under headers
ThisWorkbook.Worksheets(Data_Sheet).Cells(2, 1).CopyFromRecordset objrecordset
'Write RecordSet to results area



Else
'copy data into worksheet
ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).CopyFromRecordset objrecordset
End If


'end connection and recordset
Set objConnection = Nothing
Set objrecordset = Nothing

End Sub

[JKwan]

So with your code

'get data from csv
Select Case True
Case SortField = ""
objrecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Case SortField <> "" And SortASC
objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` ASC", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Case SortField <> "" And SortASC = False
objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` DESC", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
End Select


the variable objrecordset contains your data. You can copy that recordset onto your Excel set and what not. Now, instead of copying that recordset, I would like to run SQL on that recordset (I hope I am explaining it correctly). For example, I would like to aggregate that recordset, so in order to do that, how do I "connect" to that recordset? The reason I am doing that is my data is kinda screwed up, so I need to do some pre-processing

My "date" is of varying length (and it is a bastardized date) and I need to aggregate to a day of data. In order for me to aggregate it, I ran a SQL of extracting a workable date


left([readingdate], instr([readingdate], " "))

This gave me a recordset, now, I would like to run another SQL to aggregate that recordset.


my data looks like this (I am just illustrating the varying length of the date)
Wellhead Tubing Pres KPa g,1,1/1/2016 16:39:47.000,
Wellhead Tubing Pres KPa g,2,1/1/2016 13:23:58.908,-21.317
Wellhead Tubing Pres KPa g,3,1/1/2016 13:38:09.633,22.000
Wellhead Tubing Pres KPa g,4,1/1/2016 13:38:19.566,-21.317
.
.
.
Wellhead Tubing Pres KPa g,1,1/10/2016 16:39:47.000,
Wellhead Tubing Pres KPa g,2,1/10/2016 13:23:58.908,-21.317
Wellhead Tubing Pres KPa g,3,1/10/2016 13:38:09.633,22.000
Wellhead Tubing Pres KPa g,4,1/10/2016 13:38:19.566,-21.317
.
.
.
Wellhead Tubing Pres KPa g,1,10/10/2016 16:39:47.000,
Wellhead Tubing Pres KPa g,2,10/10/2016 13:23:58.908,-21.317
Wellhead Tubing Pres KPa g,3,10/10/2016 13:38:09.633,22.000
Wellhead Tubing Pres KPa g,4,10/10/2016 13:38:19.566,-21.317

[Kenneth Hobs]

If you have a short example csv file and a manually marked up file of what you want, I can help better. Attach example file(s) if you like.

If you are processing the data by sql, you will have to use sql syntax. While there is a a Left() method in sql, there may not be an Instr().

It is possible to add a 2nd recordset object and run the 2nd sql on it. Try something simple first.

You can do multiple sql's in one call as well. http://www.4guysfromrolla.com/webtech/083101-1.shtml

I would do simple sql's first though.

[JKwan]

Kenneth, thank you for your interest with the thread. I attached an example data file. It is very short. The sample file contains what I illustrated above with different length of "date". It contains a day of readings which I need to aggregate on equipment (I only have one) and date from that I need average pressure. So you know and hope you are not going to spend too much time on it. I already have it running however, I would like to enhance the speed of it. I currently, load the file into Excel, isolate the date, put into a new column, then run a SQL command on it with aggregate totaling. It works but kinda slow. This is why I am trying another way to see if I can speed it up. The actual data file range from 500,000 to a million records, depending on the date range that I am exporting out of SCADA.

Once again, thank you for your interest.

[snb]

Successful on your file:


Sub M_snb()
With CreateObject("scripting.filesystemobject")
.createtextfile("G:\OF\nwe.csv").write Join(Filter(Split(.opentextfile("G:\Downloads\sampledata.csv").readall, vbCrLf), ",10/10/2016 "), vbCrLf)
End With

Workbooks.Open "G:\OF\nwe.csv"
End Sub

[Aflatoon]

You cannot run SQL on a recordset.

[JKwan]

@Aflatoon : I was thinking along that the line that I am not able to run SQL with a recordset

@snb : I see that your example extracts one day of reading, my thinking is that it does not help. What will help is that if I can some how remove the "time" portion from the date, that would be perfect. My current working solution is that I split the date and time into a new columns, then I aggregate on a single day with average of the pressure reading for that day. With your example, I don't think I can aggregate at all, am I correct?

[JKwan]

@snb: Something is odd, I am able to extract data from the sample file that I created. However, if I run it on my actual data file, I have nothing. I don't know if it is the actual data file from a UNIX system and contains some invisible characters or not. The sample file was created under Windows?? Even loading the actual data file is funny, it is already a csv file but I have to parse it in Excel otherwise, it loads the entire file into a single column.

[Aflatoon]

You should be able to use a simple query:

Sub GetCSVData() Dim cn As Object
Dim rs As Object
Dim sQuery As String
Dim lngFieldCount As Long
Dim sPath As String
Dim sTable As String


Const adOpenStatic As Long = 3
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1


sPath = "C:\Testing"


Set cn = CreateObject("ADODB.Connection")


With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & sPath & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
.Open
End With
sQuery = "SELECT [Equipment], LEFT([ReadingDate], InStr(1, [ReadingDate], ' ') - 1) As 'Reading Date', SUM([Pressure])/COUNT([Pressure]) As 'Average pressure' FROM [SampleData.csv] GROUP BY [Equipment], LEFT([ReadingDate], InStr(1, [ReadingDate], ' ') - 1);"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
Cells(1, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

[snb]

We can make a separate column of the time:


Sub M_snb()
With CreateObject("scripting.filesystemobject")
.createtextfile("G:\OF\nwe.csv").write Replace(.opentextfile("G:\Downloads\sampledata.csv").readall, "/2016 ", "/2016,")
End With

Workbooks.Open "G:\OF\nwe.csv", local:=True
End Sub

[JKwan]

@Aflatoon: Well, I be darned. That is exactly what I wanted to do! I failed miserably! Don't clap yet! It worked great with the sample file! HOWEVER, when I applied the code to the real data set. It died, with the same error that plagued me - "Data type mismatch in criteria expression"

@snb: I will take a look at your second suggestion later

Thank you all for the great help.

[JKwan]

@Aflatoon: Looking the full set of data, ReadingDate contains BLANKS. How I can get rid of those junkie records?

[JKwan]

In case other people does not know..... Because my data file (CSV) contains blank dates, the Left and InStr does not work. So, I appended a WHERE clause to it (ReadingDate Is Not Null). Well, it bombs left right and center!! Long story short, I used an Excel file (not CSV), the SQL works beautifully!!