Excel as database

@Rohit Gaurav​ You've probably realised you cannot join two workbooks together, although you can join sheets together in the same workbook.

My approach here to make it set based, rather than row by row, would be to add the mapping data to your target sheet and then running and update query where you join the sheets together

Something like:

You can use the ODBC drivers to do a standard select on your map sheet

SELECT A.[Code],A.[Value] FROM [Sheet1$$] AS A;

Then after exporting that as a datatable and using excel package to add to your target workbook, you can connect to the target and run query similar to this

UPDATE [Sheet1$$] AS A

INNER JOIN [Map$$] AS B

ON A.[Code] = B.[Code]

SET A.[Value] = B.[Value];

You can then remove the mapping sheet if needed.

A bit verbose, but if you have lots of rows to map etc. it should perform quite well compared to lots of loops

@Paul Hawkins​ - Thank you for this, it works perfectly fine!!

Hi @Paul Hawkins​ , for constructing those queries which documentation do you use? These queries vary from db to db, so for building these which db documentation do you look at? Thanks!

@Andoni Aguirre Aranguren​ It is quite difficult to find good documentation on the syntax used when querying excel with SQL. I've picked few bits up over the years when trying to write more complex queries.

By and large the basics are the same as other SQL DB engines, but there are differences in some of the functions and is also much more limited in what you can do. It does has a lot of similarities with MS Access queries though.

For example, functions like CASE in SQL SERVER, the equivalent is IIF etc.

You can do a lot though, like in the case above you could use DROP TABLE to delete the sheet out of the workbook.

Hi @Paul Hawkins​ ,

Thanks for answering. You are 100% right, I struggle a lot to find comprehensive documentation for using these queries in this scenario. I do the queries first access db (I create tables there and import excel data with the excel files involved) and from there I google a lot I guess...

Regarding to the last point you mention (DROP TABLE), as far as I know that won't delete the sheet itself from the workbook (it'll only delete the content from the sheet, leaving the sheet blank)

@Andoni Aguirre Aranguren​  You're absolutely right! It's been a while since I last tried so just did a test to check and the sheet remained with data deleted. Must say since the v11 days I've been using some custom libraries for these sorts of actions instead.

Talking of documentation, found a reference here:

https://learn.microsoft.com/en-us/sql/odbc/microsoft/drop-table-statement-limitations?view=sql-server-ver16

@Paul Hawkins​ That documentation is definitely a great asset (I was not aware of it!).

In regards to what it says:

"When the Microsoft Excel 5.0, 7.0, or 97 driver is used, the DROP TABLE statement clears the worksheet but does not delete the worksheet name. Because the worksheet name still exists in the workbook, another worksheet cannot be created with the same name."

Fyi, as far as I'm aware, the driver version that I'm using is 12.0 and the same issue happens (worksheet is not deleted)

Thanks for the info, cheers!

@Andoni Aguirre Aranguren​ Yes, on first reading I thought too maybe it only applied to .xls extensions, so tried with higher on a xlsx and saw same behaviour.