'Syntax error in FROM clause' or 'Syntax error in JOIN operation' when joining two CSV files

Question

I have two CSV files with baseball stats that I'm trying to join as follows,

SELECT 
    Master.playerID, Batting.RBI 
FROM 
    ([Master.csv] INNER JOIN [Batting.csv]) 
ON 
    (Master.playerID=Batting.playerID)

I've written a C# function to connect using Jet. I'm not having any trouble querying a single table (sample query SELECT COUNT(playerId) FROM [Master.csv] works fine), but for some reason it doesn't like the join.

Is it because the Master and Batting tables are unrecognized?

Relevant bits of my C# code attached,

string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    FilePath + ";Extended Properties='text;HDR=Yes;FMT=Delimited;';";
OleDbConnection conn = new OleDbConnection(constr);
OleDbCommand command = new OleDbCommand(SQLStatement, conn);
conn.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(command);
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
command.Dispose();
conn.Dispose();

The exception is thrown at the sda.Fill(dt) line and SQLStatement is simply the query string passed to the function as a string.

*Is it because the Master and Batting tables are unrecognized?*...Yes, you need to identify the source csv with table aliases. See @GordonLinoff's answer below. — Parfait, Nov 06 '16 at 14:09

score 1 · Accepted Answer · answered Nov 06 '16 at 13:50

1

You add:

SELECT m.playerID, b.RBI 
FROM [Master.csv] as m INNER JOIN
     [Batting.csv] as b
     ON m.playerID = b.playerID;

You may need a more advanced syntax to get at the files, but this fixes syntax errors.

answered Nov 06 '16 at 13:50

Gordon Linoff

1,242,037
58
646
786

1

To clarify a bit further here, @GordonLinoff uses table aliases as OP uses aliases, *Master* and *Batting*, not defined in query. – Parfait Nov 06 '16 at 14:10
Parfait! (pun intended). Thanks, Gordon and Parfait - that fixed the problem. – insomniac Nov 06 '16 at 15:48

score 0 · Answer 2 · answered Aug 11 '20 at 13:20

The closing parenthesis on line 4 (the one with the two table names) should not be there. If you actually want to put parens the closing one has to be after the ON clause which in an intrinsic part of the JOIN syntax (not the FROM syntax):

SELECT 
    Master.playerID, Batting.RBI 
FROM 
    ([Master.csv] INNER JOIN [Batting.csv] 
ON 
    (Master.playerID=Batting.playerID))

or, omitting the extra parentheses:

SELECT 
    Master.playerID, Batting.RBI 
FROM 
    [Master.csv] INNER JOIN [Batting.csv] 
ON 
    Master.playerID=Batting.playerID

which I would format as:

SELECT 
    Master.playerID, Batting.RBI 
FROM 
    [Master.csv]
    INNER JOIN [Batting.csv] ON Master.playerID=Batting.playerID

'Syntax error in FROM clause' or 'Syntax error in JOIN operation' when joining two CSV files

2 Answers2