Solved

Run SQL on VB arrays

Posted on 2007-11-15
20
194 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
Independent Software Vendors: 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!

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax Grouping Sum question 7 36
VB script to continue despite error 2 51
T-SQL: Stored Procedure Syntax 3 32
SQL query 7 15
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 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