Get External Data From Access—Column Empty

[MichaelSchulz]

I created a new Excel 2010 file and used the 'Get External Data' to retrieve data 'From Access' in the form of an Excel table, however when the table is populated, the contents of one column remains empty. The data for all the thirteen other columns populate just fine but for this one column there is no data.

I have an database in Access 2010. There are only two tables. One table is a linked table to another Excel file. The other table is a linked table to another database via an ODBC. (I am certain the problem is not in the ODBC.)

The database contains several queries that extract data from the two linked tables, join and combine the data, until a single output datasheet is the result.

Part of that result are two derived columns.
The values in one derived column are either a text string or is NULL.
The values in the other derived column are either numerical (double datatype) or NULL.

It is the contents of the derived column with numerical values that do not populate in the Excel file.
The derived column that contains text does populate.

I have tried changing the column name.
I have tried moving the portion of the SQL script that derives the column to a subquery.
Neither of these had any effect on the results in Excel.

Does anyone have any suggestions?

 

[Micron]

I don't know what you mean by derived column because the term suggests it is the result of a formula or function in Excel and not actual data, but I can't decide which it is based on your post. If the cell contain formulas or functions that aren't evaluating to a value, the data being imported is probably not of the correct type that the formula needs. Can you run the queries in Excel and check the data being returned? Usually, import issues like this are the result of Access and Excel not treating data as the same type between them.

 

[MichaelSchulz]

A derived column means the column does not exist in the database tables but instead are created as part of a database query as the result of an operation or set of operations. The contents of a derived column can be the result of a mathematical formula (e.g. summing two columns, the product of a column and a static number, etc.), through a logical evaluation of an existing column or columns, or can be simply an assigned value.

In this situation, parts of the database query create and populate columns as part of the result set. These queries run successfully in Access and provide the desired results. So there are no formulas at all in Excel.

It is only when using the 'Get External Data' feature 'From Access' that the anomaly occurs, All columns from the result set are created in Access. The column names are created. All columns are populated with data from the result set with the sole exception of one column.

What is different about this column, I do not know.

As far as datatype, Access is treating it as a Number (double) datatype. I have tried converting it in the Access query to a string datatype and still the column remains empty in Excel.

This one's got me really stumped.

 

[james_lankford]

what if you use the query to create a table in Access and then from Excel try to import the table

do all the columns come in then ?

 

[MichaelSchulz]

what if you use the query to create a table in Access and then from Excel try to import the table

do all the columns come in then ?

That is what I did. I imported to Excel from Access using the 'Get External Data' feature and selecting 'From Access'.

Do you mean a different method and, if so, what is that?

 

[MichaelSchulz]

That is what I did. I imported to Excel from Access using the 'Get External Data' feature and selecting 'From Access'.

Do you mean a different method and, if so, what is that?

My apologies—I misunderstood. I see now what you had meant.

So as part of this troubleshooting, I created a table in Access from the results of the query.

Then I used the same steps in Excel to get the data from the new table.

The contents of the column DID populate.

So what does this mean? Why does it populate in this circumstance and not in the other? And does this indicate what must be done to get the data from the query rather than the table?

 

[xenou]

Try instead of "From Access" using "From Other Sources | Microsoft Query". I have also found the from Access wizard to be a little weird and not working well except with table data. Another alternative would be to script the data retrieval and use ADO.

P.S.
Don't ask me why but I have an idea that if you write a query such as:

SELECT * FROM MyQuery

and then use "From Access" on that guy, it will work.

 

[MichaelSchulz]

Try instead of "From Access" using "From Other Sources | Microsoft Query". I have also found the from Access wizard to be a little weird and not working well except with table data. Another alternative would be to script the data retrieval and use ADO.

P.S.
Don't ask me why but I have an idea that if you write a query such as:

SELECT * FROM MyQuery

and then use "From Access" on that guy, it will work.

The 'select * from' was a good idea but it did not work, either. The results are the same: all the column headers and contents port into Excel just find with that one exception—the header ports but not the contents.

I attempted the 'From Other Sources | Microsoft Query' but cannot locate my database or discover the means for it to become an option.

I have considered a scripting option and may need to take that route but I still wish know what it is about this column of data that is so special that it won't port into Excel.

 

[xenou]

Here's another try using another wizard:
How to Embed a SQL Query in Microsoft Excel: 13 Steps

I don't know if anyone will be able to tell why it doesn't work. Plain truth is it should. So it's rather like a bug more than anything else. You have to just use a lot of trial and error until you find something that works. It may be something in the SQL you wrote that could be changed, or something wrong in Access when it tries to pass back the results, or in the connection drivers, or (least likely) in Excel.

 

[Micron]

I read of this problem in other places, and the solution was to do exactly as done - populate a table first. I believe the reason given what some sort of mis-treatment of data types from one application to the other, even when they're properly defined in their respective apps. I've also seen suggestions that when going the other way, the spreadsheet text be prefaced with the Excel text type character ( ' ) and strip it out at the database end as an update when the issue was text not being handled properly by Access.