Excel - Import External Data – question re. Data Range Properties box

Excel - Import External Data – question re....

Didn't find your answer?

In the Data Range Properties box, when talking about Refresh it says:

If the number of rows in the data range changes upon refresh

1. Insert cells for new data. Delete unused cells
2. Insert entire rows for new data. Clear unused cells
3. Overwrite existing cells with new data. Clear unused cells.

The first of these is the default. But surely, if you are refreshing, shouldn’t you be using the Insert entire rows option, no 2?

I can’t get my brain round the differences between these three options. Can anyone enlighten me as to when each of them is appropriate? Thanks in advance.

David Carter

Replies (14)

Please login or register to join the discussion.

avatar
By Richard Willis
19th Apr 2007 15:54

Default seems to work OK
Hi David

I have written queries to an MS ACcess database with 2500+ records and they have worked fine for several years, even though I only came across this choice recently. The main one selects members of the Suez Veterans' Association for publication on their web site, based on a status field in the D/B, and members are added and voided on a regular basis. Any faults have always been traced back to the data, not in the query.

Like you, without some testing I haven't the foggiest what the diffs. actually imply but the default seems to work OK.

Thanks (0)
avatar
By listerramjet
19th Apr 2007 16:14

its horrible - don't use it!
If I want data from a database (or several databases linked together!) then I have 2 preferred methods. Easiest is to run the query in Access, and copy the data, then paste special, values directly into Excel. use the paste special to stop the formatting.

More complex is to use VBA. i prefer to use the Microsoft DAO object library, although ADO is more up to date. I can use this to run SQL queries built and held in Access, or build SQL queries in VBA and run them, and copy the results directly to a spreadsheet.

Of course using an Access mdb I can link to tables in many different application databases at the same time, and using Access SQL I can link them together.

Whilst the import external data item is directly accessible in Excel, it is most fiddly to use and of limited use, and whilst VBA and DAO requires some patience and learning, it is infinitely more flexible.

Thanks (0)
avatar
By Richard Willis
20th Apr 2007 09:18

Excel best for basic users.
In answer to Alastair's comment; using an Excel query gives basic level users access to information in what may otherwise be a complex database and without giving them any direct access to the d/b. All the user needs to be able to do is to refresh the data.

Also, in my scenario, I can then use the Excel web publishing facility to quickly and easily convert a large amount of data to a web page. I don't know if this facility is available in Access.

I'm sure that there are facilities available to enable basic users to run Alastair's solutions in a secure environment but since most people are familiar with Excel this, as always, seems the easiest and quickest solution.

Thanks (0)
avatar
By adam.hall
20th Apr 2007 11:58

In Defence of Excel!
This important function from database's I use for reporting basis, especailly from sage in relation to a month by month P&L and B/S reports for cleints.

I always use the third option as this clears out all the data on refreshing or opening and then re populates form the database. This ensures the inforamtion being pulled into Excel is never conflicted or merged with existing infomration on the S/sheet.

I agree that information pulled thorugh to Excel is to be used for reportin puroses and gives no actuall link of editing capablilities back to the source data.

If you are linking to a live regularly updated source i.e Sage for KPI's etc and use the automatic update function in your import data proporties. Your KPI's update through the day and with key levels of alrams set up for monitoring responsible individulas can gain greater control over day to day operations.

Thanks (0)
avatar
By listerramjet
23rd Apr 2007 23:49

David, don't want to get too wound up
with cut and paste, but if you assign a range name to the data area and then alter it to encompass all of the new data, and then if your pivot data range is the range name, then refresh will do exactly as you describe for get external data.

As it happens I have an anorak tendancy to do things in a more complex way - but consider this. Lets say you have a text file saved as a csv, ideally with column headers, although this is not necessary. You can link to that file from Access - the screens you go through are similar to the text to columns screens in excel. The end result is a table in Access, although that is simple a representation of the text file. If you produce a new version of the text file - same name, same place, then the Access link will return the new file.

At the moment that sounds just like your Excel import external data, and in Excel you can do just that from the Access table, or indeed you can point yout pivot directly at the Access table (which means you don't have the row limitation that Excel has!).

This starts to become useful if you are prepared to invest some time in learning how to construct queries in Access, and particularly if you have several tables and you know how to join them. and it also becomes useful if you have data from several sources that join together.

External data is often raw, but you can use Access Queries to transform the raw data into useful information. Queries don't just display data - you can use them to summarise and update new tables. in fact you can build a sequence of queries that you run each month to do the transformations.

Apologies, I'm starting to ramble. My point is simply that the basic Office tools are very powerful tools for data manipulation and presentation - and you can access a lot of this stuff if you have an enquiring mind and google!

Thanks (0)
avatar
By David Carter
21st Apr 2007 10:23

Import external data is earth-shatteringly important (I think?)
I've taken a sudden interest in Import Data because when playing around with Excel 2007 I discovered that you can import Text files into Excel with this option, not just ODBC-enabled. Apparently it has been possible to import Text files since Excel 2000.

Suppose that you are an accountant who creates reports in Excel for management each month. If you do it Alastair's way (copy and paste special into Excel), you have to build the report from scratch each month.

But if you use Import Data, when month 2 comes round you just tell the managers to go to the month 1 report and hit the Refresh button. The month 2 report is produced automatically.

So every accountant who exports data out of their accounts package and into Excel, i.e. 90% of them, would save themselves an enormous amount of time if they used this Data -Import External Data option.

Am I right here? Is this an earth-shatteringly important discovery? (admittedly 7 years late, but better late than never).

Thanks (0)
avatar
By listerramjet
21st Apr 2007 23:54

hi David
Interested by your follow up comment. Your refresh data is presumably going to put data into a worksheet? My copy and paste is less elegant, and I would agree my VBA is more compicated to get the same result. On the other hand it is not difficult when you know how, and my data analysis using SQL queries is going to take me places your import external data will never get to!

As your refresh data is presumably only going to put some numbers into a worksheet - similar end result to my copy and paste - so why don't I get the same report building options as you?

You can connect to text files in a similar fashion using VBA - I would guess that in fact the exernal data option is actually using VBA, and probably OBDC, behind the scenes.

Worth bearing in mind that Access is more than just a database application - it provides JET which is a dms, and you can use an Access mdb file as such without going anywhere near Access as an application. OK it has lots of limitations, and MySQL is a much better bet if you want scalability (although JET can cope with quite a lot for a desktop database), but for a little learning effort you can do some powerful stuff with it, and the nice thing about Excel as a development environment is that almost everyone has it! Put that another way - you can deploy your Excel model with the Access mdb for users that don't have Access.

The stuff microsoft are doing now sounds grand (the way they sell it) but does not do much more than you could do in Office 2000, perhaps apart from making your wallet much lighter.

Thanks (0)
avatar
By RichardSchollar
23rd Apr 2007 09:00

MSQuery
Like Adam, when using MSQuery, I always choose the third option. In the past, when using the default, I've had Excel insert cells and move the existing data a few columns to the right! That is truly a pain, but completely avoided by the third option.

The option to import data using the Microsoft text driver has certainly been around for a good few years - it's a convenient way to import data.

MSQuery does have one or two limitations (like not being able to query on multiple databases at the same time - at least I think this is the case) but in other ways the SQL you can write is superior to Access as it can have greater functionality (dependent on the specific driver employed). It is entirely inependent of Access too.

Richard

Thanks (0)
avatar
By listerramjet
23rd Apr 2007 11:00

a query about query
OK, I only have office 2000, and OK I am not a fan, but taking a look at what MS Query offers me it looks very restrictive. Essentially I can create simple queries using the query builder. and I can connect to data sources and run these queries. The most complicated thing I can do is to filter. I can't see how I might join tables, although the help (somewhat cryptically) tells me that I can change outer and inner joins, without telling me how.

I have to say that I am underwhelmed!

Thanks (0)
avatar
By RichardSchollar
23rd Apr 2007 14:54

MSQuery
Alastair

For more complex queries involving table joins you really need to be writing the SQL directly into the SQL dialog box (the button for which resides below the Format menu in the 2003 MSQuery interface). This then does presume some proficiency with SQL itself (and you won't be able to view the query graphically).

Thanks (0)
avatar
By David Carter
23rd Apr 2007 17:09

Why Get External Data is better than cut and paste
Alastair, you are right that both methods get data into the worksheet. But there's a big difference in what you can do when you want to add new records.

With Cut and Paste or File - Open you usually have to re-import and build the report all over again. With Get External Data you just have to hit the Refresh button and new records are updated to your report automatically. To illustrate the point I've just posted a pivot tip on using Get External Data to pull in a text file and refresh..

When I used the term earth-shattering, I was talking about using Get External Data with text files. Most packages out there don't have live linking, ODBC,, SQL etc . But every one of them can output data as text. The potential with these older packages is enormous, I think (and of course you don't need to use Query either!).

Thanks (0)
avatar
By David Carter
25th Apr 2007 13:36

Get External Data allows you to automate report production
Hi Alastair, I'm not trying to teach you to suck eggs. It's just that for people like me who don't know SQL, VBA or Access, the Get External Data option is a big leap forward.

As part of my job I help organisations improve their reporting. I can do some snazzy things with pivot tables and initially they are very enthusiastic. But when they realise that they are going to have to pay me to me to come in and rebuild them each month, their enthusiasm cools a bit.

But with Get External Data, I can now automate these reports. To run them in future months, the user simply has to press the Refresh button.

Thanks (0)
avatar
By User deleted
27th Apr 2007 17:18

data-range-properties
I came across a similar discussion on another blog.

http://www.dicks-blog.com/archives/2004/04/26/data-range-properties-options/

Hope this helps

Thanks (0)
avatar
By David Carter
28th Apr 2007 08:45

It does help
It really does help. Thanks Spiney

Thanks (0)