• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Run SQL on VB arrays

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
AlHal2
Asked:
AlHal2
  • 9
  • 6
  • 2
  • +2
1 Solution
 
DhaestCommented:
I don't think that's possible to perform an sql-query on a array.
0
 
AlHal2Author Commented:
What about using the source spreadsheet as a database and putting it into a recordset?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AlHal2Author Commented:
That's do with an xls file.  What about a csv or txt file?
0
 
DhaestCommented:
You can open a csv-file or an xls-file with the example
0
 
AlHal2Author Commented:
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
 
DhaestCommented:
Where do you get an error ? What error do you get ?
0
 
AlHal2Author Commented:
Could not find Installable ISAM
0
 
DhaestCommented:
Are you sure that the path to your csv-file is correct ?

On what line do you get the error ?
0
 
AlHal2Author Commented:
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
 
EDDYKTCommented:
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
 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
I want to treat each column as a string
0
 
AlHal2Author Commented:
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
 
DhaestCommented:
Can you post some code so we can try to correct it ...
0
 
AlHal2Author Commented:
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
 
3_SCommented:
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
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now