Solved

Run SQL on VB arrays

Posted on 2007-11-15
20
191 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
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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 57
using web browser with BING 40 110
MS SQL Inner Join - Multiple Join Parameters 2 21
SQL Query Syntax Join 4 34
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

920 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

16 Experts available now in Live!

Get 1:1 Help Now