?
Solved

Run SQL on VB arrays

Posted on 2007-11-15
20
Medium Priority
?
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
  • +2
20 Comments
 
LVL 53

Expert Comment

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

Author Comment

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

Expert Comment

by:Dhaest
ID: 20287867
0
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 

Author Comment

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

Expert Comment

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

Author Comment

by:AlHal2
ID: 20288101
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
ID: 20288199
Where do you get an error ? What error do you get ?
0
 

Author Comment

by:AlHal2
ID: 20288243
Could not find Installable ISAM
0
 
LVL 53

Expert Comment

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

On what line do you get the error ?
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 20288359
0
 

Author Comment

by:AlHal2
ID: 20289096
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
ID: 20289650
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
ID: 20290396
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
ID: 20290506
I want to treat each column as a string
0
 

Author Comment

by:AlHal2
ID: 20290710
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
ID: 20296654
Can you post some code so we can try to correct it ...
0
 

Author Comment

by:AlHal2
ID: 20296910
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
ID: 20307844
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
ID: 21000154
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 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