Tag Archives: sql

Using SQL queries to read Excel files in macros

I’m doing my Masters in Geographic Information Systems at U of T and found the need to do this in my research.

You can treat Excel sheets as tables and perform SELECTs and JOINS like you would against a regular database. The language is Visual Basic (VBA to be exact). You can put the following code inside a Macro Sub. NAICS_CODE (Row 1) is a heading of one of the columns in the Results worksheet.

I did need to add the Microsoft ActiveX Data Objects 6.0 Library by going to Tools -> References in the VBA Project (the program that opens up when you edit a macro).

    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Dim rs As New ADODB.Recordset

    With cn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=C:\Users\zarar\Documents\researchdata.xls;" & _
            "Extended Properties=Excel 8.0;"
        .Open
    End With
    
    'Store all naicsCodes in an array
    Dim naicsCodes() As String
    'Get all distinct NAICS codes
    Set rs = cn.Execute("SELECT DISTINCT NAICS_CODE FROM [Results$]")
    Dim i
    i = 0
    Do Until rs.EOF
        ReDim Preserve naicsCodes(i + 1)
        naicsCodes(i) = rs.Fields("NAICS_CODE")
        i = i + 1
        rs.MoveNext
    Loop
    rs.Close
    cn.Close
Advertisement

Performing a LEFT OUTER JOIN using JPQL and JPA

Haven’t made a tutorial-type post in a while so here’s an installment of documenting something that should be already documented. The problem at hand is a very common one: how do you do a LEFT OUTER JOIN on two tables (entities) when you’re using the Java Persistence API.

Let’s go with the sports theme and model basketball shoe sponsorship. An NBA player may or may not have a shoe deal, for example Lebron James has a shoe deal with Nike while Juan Dixon doesn’t. We have two tables and entities to work with: Player and ShoeCompany. Now the task at hand is to retrieve all information about all players. Given that many of them don’t have shoe deals we want to be careful of not ignoring them in our query, that’s where the OUTER JOIN kicks in. In other words, retrieve the set of entities where matching values in the join condition may be absent. Here’s what the entities and tables look like:

@Entity
@Table( name="nba_player" )
public class Player {

    @Id @Column
    private Long id;

    @Column
    private String name;

    @OneToOne
    @JoinColumn( name="shoecompany_id" )
    private ShoeCompany shoeCompany;

    // getters and setters
}
@Entity
@Table( name="shoe_company" )
public class ShoeCompany {

    @Id @Column
    private Long id;

    @Column
    private String name;

    // getters and setters
}

Here’s what the tables look like:

nba_player(id, name, shoecompany_id)
shoe_company(id, name)

Doing this query without an OUTER JOIN is also possible, it’s quite simple actually, we just add an additional WHERE clause where we check for the foreign key being NULL.

SELECT *
FROM nba_player p, shoe_company s
WHERE p.shoecompany_id = s.id or p.shoecompany_id IS NULL

The same SQL query using an OUTER JOIN would look like:

SELECT *
FROM nba_player p LEFT OUTER JOIN  shoe_company s
                                       ON (p.shoecompany_id = s.id)

To generate the OUTER JOIN query using JPQL, you’ll have to write this:

SELECT p FROM Player p LEFT JOIN p.shoeCompany sc

That’s it, now you’re free to use and abuse LEFT OUTER JOINs all you want. I’m using TopLink Essentials as the JPA implementation but that shouldn’t come into play. Grab the techy part of the blog’s feed.