Refreshing data in Excel from a SQL DB

[Paul D]

I have imported data from SQL, and I have added some
columns to the spreadsheet. When I try to re-fresh the
spreadsheet, it does not import any current or new data.

** however when I create a new tab, and import again, the
data is current.

I need to create the same calculations in different tabs
(worksheets)and use filtering by date and by client type,
to produce a usage report.

 

[Gerrit-Jan Linker]

You could consider using SQL*XL. SQL*XL is an addin for Excel that
allows you to use your database directly from Excel. It is fully
functional: it supports the transfer of data from the database to
Excel and vise versa.

Reloading the data in SQL*XL is equivalent to rerunning the query.
There is a special button on SQL*XL to refresh any query on the
worksheet.

Have a look at SQL*XL at www.oraxcel.com

Best regards,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

 

[Jamie Collins]

(e-mail address removed) (Gerrit-Jan Linker) wrote ...

You could consider using SQL*XL. SQL*XL is an addin for Excel that
allows you to use your database directly from Excel. It is fully
functional: it supports the transfer of data from the database to
Excel and vise versa.

I note this tool attempts to solve that age old problem in the Excel
newsgroups of how to refresh external data based on amendments made in
Excel. So, I thought I'd download it and give it a quick test drive. I
downloaded and installed the 'lite' versions of the standard tool and,
because I don't have Oracle to hand, the ADO tool too.

I connected to my external data being an Excel workbook, which seems
most appropriate for an Excel tool.

[General note: I encountered several bugs with this product and I had
to restart my machine a few times as a consequence (on one occasion I
got a message saying the product wouldn't quit, forcing me to kill it
at a lower level!) However, the most frustrating bug is that the tool
has trouble with Names ('named ranges') e.g. where the table is
Sheet2$SheetLevelName and I use the 'Count Rows' functionality I get a
syntax error and where it is '29-12-2003$'MyName I'm told my driver is
unable to retrieve column information! Another frustrating problem is
that once connected my data source workbook cannot be subsequently
opened without quitting and restarting Excel.]

Get the external data with the following query:

SELECT MyKeyColumn, MyDataColumn FROM Blah

The rows are correctly returned as follows:

MyKeyColumn MyDataColumn
----------- ------------
5 2
Null Null
5 2

First, try amending a row. Change the nulls to 99. Update Multiple
Rows (Update Multiple Row is not available) reports changes to two
cells. Success! However, if I sort on the key column it reports four
cells changed, meaning it hasn't kept track of the actual row.

Next, try inserting a row (I'll start again with the unamended data).
Insert a new row at the bottom of the returned range and give it
values of 99 for each column. Update Multiple Rows detects no changes.
Try again, this time inserting the row below the first row and it
merely detects the rows as being amended rather than detecting a new
row. If committed this would result in a loss of data.

Finally, delete row (again using the unamended data). I delete the
last row. Update reports the row has changed, clicking yes to confirm
the change brings up a (provider?) error message 'Key information is
insufficient or incorrect...', press OK and I get the tool's own
message for the same error, OK to that, it moves down a row and I get
the same loop of messages (ask whether to change, the provider error,
the tool's error) which presumably would continue until row 65536 was
reached. So, not only did it fail to detect that a row had been
deleted, it completely lost track of the data range.

Based on this quick test I conclude that challenge of keeping track of
external data while being edited in Excel remains unsolved by this
tool.

Jamie.

--

 

[Gerrit-Jan Linker]

Hello Jamie,

Thanks for your test. A few replies:

SQL*Xl does not recognise a named range as a table name. It will
indeed error on that. What exactly did you execute? Did you just give
it the range where the table name can be found or did you do a:
select * from MyName

I implemented SQL*XL to always expect a proper SQL select statement.
It would be interesting to hear more precisely from you what
flexibility you expected.

When updating rows you cannot change the order or delete rows, that's
right. There are ways to cater for deleted and inserted rows however
sorting would pose a fundamentally difficulty. Even deleting and
inserting is difficult to get right. I have worked on an
implementation but it has never made it into the product. Maybe I
should try it again.

Note that I have sent you a personal message to see where your
problems originate. Until today I was not aware that any such problems
exist with SQL*XL and would like to look into why you have
difficulties.

Again, thanks for posting your test results.

Best regards, Gerrit-Jan Linker
Author of SQL*XL
www.oraxcel.com

 

[Jamie Collins]

(e-mail address removed) wrote ...

Thanks for your test.

Mine was an honest (but brief) review and I glad you saw it as such.

SQL*Xl does not recognise a named range as a table name. It will
indeed error on that.

Actually, it does recognize named ranges!

What exactly did you execute? Did you just give
it the range where the table name can be found or did you do a:
select * from MyName

I was using the tool's own functionality, rather than executing SQL.
Steps to reproduce:

1. Connect to Excel data source.
2. Choose Describe Table/View.
3. Select Sheet2$SheetLevelName.
4. Column information displays correctly.
5. Hit the Count Rows button.
6. See error message, 'Syntax error in From clause...'

When updating rows you cannot change the order or delete rows, that's
right. There are ways to cater for deleted and inserted rows however
sorting would pose a fundamentally difficulty. Even deleting and
inserting is difficult to get right. I have worked on an
implementation but it has never made it into the product. Maybe I
should try it again.

Yes, these are the issues as I see them. I think there is a genuine
demand for a tool that can update an external source by EITHER
tracking user changes (amend/insert/delete rows), preventing them from
doing any illegal operations (e.g. inserting a new column) OR giving
the user a free reign and when they are finished work out which rows
changed (amended/inserted/deleted) and whether any illegal operations
occurred. Of course, I'm going to do it myself one day, unless someone

I have sent you a personal message to see where your
problems originate.

Sure, we should go off-line now. However, the email address you see is
exclusively for spam <g> so I'll email you at your site.

Jamie.

--