Microsoft Query

  • Thread starter Thread starter anupam
  • Start date Start date
A

anupam

Using Get External Data --> Database --> Excel File

it's not showing the cells having data in %age format. Even afte
changing their format to General its not showing the data.

Also their are 3 coloumns having numeric data, out of which its showin
only 1 coloumn rest 2 coloumns which ar having numeric data is no
getting extracted.

????
 
Hi

For ODBC query from Excel files the workbook is data source. For table is
used named range. It looks like some columns from your source table aren't
included into this named range - check range definition (Insert.Name.Define
from menu).

Another possible cause - those columns aren't included into query. Check SQL
string for this.

And at last (but maybe you have to start with this) - when you define the
named range for use as ODBC query source table, in the top row must exist
headers for every column. I'm not sure, how the query behaves, when the
header is missing. Surely you can't select such column in query builder, but
when you selected all columns (SELECT * FROM ...), then maybe such columns
are simply ignored (a guess only). So check source table's header row - are
all headers present in top row (and check also that there aren't any merged
cells in header).


Arvi Laanemets
 
anupam said:
Using Get External Data --> Database --> Excel File
their are 3 coloumns having numeric data, out of which its showing
only 1 coloumn rest 2 coloumns which ar having numeric data is not
getting extracted.
Click to expand...

Are you sure all rows contain only numeric values or nulls e.g. an
empty string is a string, not a null numeric value. The following
details how Jet determines the data type for an Excel column, you may
find it of some help:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html
it's not showing the cells having data in %age format.
Click to expand...

While the format is used to determine data type, only the raw data is
extracted, not the format. You will need to format the numeric column
as appropriate e.g.

SELECT FORMAT(MyNumCol,"0%") AS MyPercVals FROM [Sheet8$]

Jamie.

--
 
Arvi Laanemets said:
For ODBC query from Excel files the workbook is data source. For table is
used named range.
Click to expand...

An Excel table can be a book level Name that defines a Range (rather
than a formula that returns a Range) e.g.

SELECT MyCol1, MyCol2 FROM [BookLevelName]

or a sheet level Name e.g.

SELECT MyCol1, MyCol2 FROM [Sheet1$SheetLevelName]

or a worksheet e.g.

SELECT MyCol1, MyCol2 FROM [Sheet1$]

or a range address e.g.

SELECT MyCol1, MyCol2 FROM [Sheet1$A1:B999]
in the top row must exist
headers for every column.
Click to expand...

You can omit column headers by specifying HDR=No in the connection
string. Jet will assign the column names as F1, F2, F3 etc. For
example:

SELECT F1, F2
FROM [Excel 8.0;HDR=No;Database=C:\Tempo\db.xls;].[Sheet8$A2:B65536]
;

Jamie.

--
 
Back
Top