Left Join 2 csv files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TAS - 28 May 2008 14:49 GMT

I need to join 2 csv files with SQL left join. I keep getting the following
error:

Microsoft JET Database Engine (0x80040E14)
Syntax error in JOIN operation.

Here is my code

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=o:\nobuy\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""

strsql = "SELECT allcust.csv.custnum, allcust.csv.custname,
allcust.csv.slsnum, custbuying.csv.count FROM allcust.csv LEFT JOIN
custbuying.csv ON allcust.csv.custnum=custbuying.csv.custnum ORDER BY
allcust.csv.custnum"

Any help would be greatly appreciated

Reply to this Message

Bob Barrows [MVP] - 28 May 2008 15:01 GMT

> I need to join 2 csv files with SQL left join. I keep getting the
> following error:
[quoted text clipped - 15 lines]
>
> Any help would be greatly appreciated

The syntax would be correct in a Jet database, so it would appear that
outer joins are not supported for text files. This leaves at least two
options:

1. Open two recordsets and loop through them to find the matching
custnums
2. Import the text files into a Jet database (You can create one using
ADOX) and implement the join there.

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Old Pedant - 29 May 2008 19:46 GMT

I would ask first: Can you even do an INNER JOIN between the two? Or even
an implicit join (that is, using a comma between the table names and a WHERE
clause)??

If you can, and if the problem is just the LEFT JOIN, then you *can* produce
the same results as a LEFT JOIN by doing the UNION of a pair of separate
SELECTs.

It goes something like this:
SELECT A.x, A.y, B.z FROM A, B WHERE A.id = B.id
UNION
SELECT x, y, NULL FROM A WHERE A.id NOT IN ( SELECT id FROM B )
ORDER BY 1

Yes, it's a shade ugly, but probably more efficient than having to clone
each table into an Access/Jet DB table and then doing the join.

But if you can't join two CSV's at all, then have fun cloning.

Reply to this Message

Bob Barrows [MVP] - 29 May 2008 20:02 GMT

> But if you can't join two CSV's at all, then have fun cloning.

It's not that bad. ADOX can be used to create a database containing
links to the text files with a few lines of code. I was not talking
about looping through the text files and inserting the data into the
database.
Hopefully the union solution can be used. I have the same doubts that
you do.

Signature

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2008

Left Join 2 csv files