Joining on 3 csv files

Tags: , , , ,



I need to retrieve data from 3 different CSV files (Adres.CSV, ID.CSV and Regios.CSV) and combine this in an Excel table.

This SQL statement works fine,

StrSQL = "SELECT ID.[ID], Adres.[Naam], Adres.[Adres], Adres.[Plaats] " _
         & "FROM Adres.CSV Adres INNER JOIN ID.CSV ID ON Adres.[Naam] = ID.[Naam];"

as well as

StrSQL = "SELECT Adres.[Naam], Adres.[Adres], Adres.[Plaats], Regio.[Regio] " _
         & "FROM Adres.CSV Adres INNER JOIN Regios.CSV Regio ON Adres.[Plaats] = Regio.[Plaats];"  

but when I combine them to

StrSQL = "SELECT ID.[ID], Adres.[Naam], Adres.[Adres], Adres.[Plaats], Regio.[Regio] " _
         & "FROM Adres.CSV Adres " _
         & "INNER JOIN Regios.CSV Regio ON Adres.[Plaats] = Regio.[Plaats]" _
         & "INNER JOIN ID.CSV ID ON Adres.[Naam] = ID.[Naam];"

I get an error message that an operator is missing.

What am I overlooking?

Answer

There are two issues here:

  1. You are missing a space between the 3rd and 4th lines.
  2. JET/ACE SQL requires you to use parentheses when doing multiple joins.

This should fix the sql error you are seeing:

StrSQL = "SELECT ID.[ID], Adres.[Naam], Adres.[Adres], Adres.[Plaats], Regio.[Regio] " _
         & "FROM (Adres.CSV Adres " _
         & "INNER JOIN Regios.CSV Regio ON Adres.[Plaats] = Regio.[Plaats]) " _
         & "INNER JOIN ID.CSV ID ON Adres.[Naam] = ID.[Naam];"

Your string ends up evaluating with ...Regio.[Plaats]INNER JOIN ID.CSV.... This lack of white space causes the sql parser to miss your second INNER JOIN .



Source: stackoverflow