Solved

Run SQL on VB arrays

Posted on 2007-11-15
20
190 Views
Last Modified: 2010-04-30
The following is an extract of a file

CODE      BDATE            NAME
682803      26/01/01      CROATIA OSIGURANJE
685750      09/10/98      CROATIA OSIGURANJE PREF.
51215F      24/08/06      HOTEL DUBROVNIK
414415      01/12/06      INA INDUSTRIJA NAFTE
682813      14/06/94      ISTRATURIST UMAG
50939L      06/08/07      MAGMA
32522N      02/04/04      MEDIKA
673272      25/01/95      PLIVA
868693      19/08/94      PODRAVKA PREHRAMBENA INDUSTRIJA
286613      05/01/96      VARTEKS
36180J      31/05/06      VIRO TVORNICA SECERA
281107      23/08/96      ACI OTC
27218X      24/06/03      AD PLASTIK
30464X      02/01/07      ADRIS GRUPA
30462C      31/07/03      ADRIS GRUPA PREF
26777T      06/02/03      AGROLAGUNA
32330V      03/11/05      AGROMEDIMURJE

I use this code to ingest into a vb6 array.

Option Explicit
Dim AryData() As String, DataCt As Integer, MaxDataCt As Integer
Dim AryTitles() As String, TitlesCt As Integer
Private Sub Form_Load()
ReDim AryTitles(1 To 3) As String
DataCt = 1
MaxDataCt = 50
ReDim AryData(1 To 3, 1 To MaxDataCt)
Open "C:\test.csv" For Input As #1
Input #1, AryTitles(1), AryTitles(2), AryTitles(3)
Do Until EOF(1)
    Input #1, AryData(1, DataCt), AryData(2, DataCt), AryData(3, DataCt)
    DataCt = DataCt + 1
    If DataCt = MaxDataCt Then
        MaxDataCt = MaxDataCt + 50
        ReDim Preserve AryData(1 To 3, 1 To MaxDataCt)
    End If
Loop
Close
ReDim Preserve AryData(1 To 3, 1 To DataCt - 1)
End
End Sub

I would like to use some features of SQL on this array e.g to sort it and to group it (much easier in SQL than VB).  Please can you amend the above code, so I can put this into a recordset and run some SQL.  For example give me a count of how many codes are all numeric and how many codes end with a letter.
0
Comment
Question by:AlHal2
  • 9
  • 6
  • 2
  • +2
20 Comments
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
I don't think that's possible to perform an sql-query on a array.
0
 

Author Comment

by:AlHal2
Comment Utility
What about using the source spreadsheet as a database and putting it into a recordset?
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
0
 

Author Comment

by:AlHal2
Comment Utility
That's do with an xls file.  What about a csv or txt file?
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
You can open a csv-file or an xls-file with the example
0
 

Author Comment

by:AlHal2
Comment Utility
11.15.2007 at 10:57AM GMT, ID: 20288097
I tried running this, but it failed.  I think it's best to assume ignore the Excel part when looking at a csv or txt file, but am not sure how to code the connection string.  The worksheet name is test.

Set con = CreateObject("ADODB.Connection")
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\test.csv;" & _
           "Extended Properties=Excel 8.0;IMEX=1;HDR=YES;"
con.Connectionstring = StrCon
con.open
Set rst = con.execute("SELECT * FROM test")
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
Where do you get an error ? What error do you get ?
0
 

Author Comment

by:AlHal2
Comment Utility
Could not find Installable ISAM
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
Are you sure that the path to your csv-file is correct ?

On what line do you get the error ?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:EDDYKT
Comment Utility
0
 

Author Comment

by:AlHal2
Comment Utility
I went there and got this code.  On the cn.Open I'm getting the message could not find installable ISAM
Option Explicit

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim PathtoTextFile As String
Dim PathtoMDB As String
Dim myarray() As Variant
Private Sub Form_Load()
  PathtoTextFile = "C:\test.csv"
  PathtoMDB = "C:\PathtoMDB\"
  'cmdOpen.Caption = "Open textfile and display field value"
  cmdInsert.Caption = "Insert textfile values into MDB"
End Sub
Private Sub CmdOpen_Click()
  '============================method 1=================================
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & PathtoTextFile & ";" & _
          "Extended Properties=""csv;HDR=YES;FMT=Delimited"""
  rs.Open "select * from test.csv", cn, adOpenStatic, adLockReadOnly, adCmdText
  MsgBox rs(0)
  rs.Close
  cn.Close
End Sub
0
 
LVL 26

Expert Comment

by:EDDYKT
Comment Utility
PathtoTextFile = "C:\"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & PathtoTextFile & ";" & _
       "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

rs.Open "select * from test.csv", cn, adOpenStatic, adLockReadOnly, adCmdText
0
 

Author Comment

by:AlHal2
Comment Utility
Looking better.
The first column is a mixture of numbers and text.  Since the array is defined as a variant the codes ending with a number are coming out as null.  Could it be to do with the connection string?

What do you suggest?
0
 

Author Comment

by:AlHal2
Comment Utility
I want to treat each column as a string
0
 

Author Comment

by:AlHal2
Comment Utility
Also if I try to filter the results eg where the length of the name is 10 characters I get the message
selected collating sequence not supported by the operating system.

If I retry I get the message No value given for one or more required parameters.  I've increased the points.
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
Can you post some code so we can try to correct it ...
0
 

Author Comment

by:AlHal2
Comment Utility
When I open test2.csv I see that column A is null where the last character of column A in test.csv is a letter.  The contents of test.csv are above.

Dim cn As ADODB.Connection
Dim rs As Recordset
Dim PathtoTextFile As String
Dim PathtoMDB As String
Dim myarray() As Variant
Dim strsql As String
Dim counter As Integer
Private Sub Form_Load()
  PathtoTextFile = "C:\"
  PathtoMDB = "C:\PathtoMDB\"
Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & PathtoTextFile & ";" & _
       "Extended Properties=""text;"""
Set rs = New ADODB.Recordset
strsql = "select * from test.csv" ' where len(name)=10"
With rs
    .ActiveConnection = cn
    .ActiveConnection.CommandTimeout = 120
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Source = strsql
    .Open

End With
       
myarray = rs.GetRows
rs.Close
cn.Close
Open "C:\test2.csv" For Output As #1
For counter = LBound(myarray, 2) To UBound(myarray, 2)
    Write #1, myarray(0, counter), myarray(1, counter), myarray(2, counter)
Next
End
End Sub
0
 
LVL 10

Expert Comment

by:3_S
Comment Utility
you can build an recordset out of you array

set rs = new adodb.recordset
rs.cursorlocation = adUseClient
rs.fields.append "CODE" ,adVarChar,6
rs.fields.append "DATEFIELD",adVarChar,8
rs.fields.append "NAMEFIELD",adVarChar,50
rs.Open
'To add the array to the recordset
'start loop using counter
rs.AddNew
rs.Fields("CODE")=AryDate(0,counter)
rs.fields("DATEFIELD")=AryDate(1,counter)
rs.fields("NAMEFIELD")=AryDate(2,counter)
rs.Update
end loop

'now you can sort
rs.movefirst
rs.sort = "NAMEFIELD ASC"
'you can now go through you recordset and they will be sorted on the NAMEFIELD

In the same way you can use filter
I don't know how you can perform a group by in this way
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now