Context: SPSS 14 for Windows; Excel 2000; Windows XP, up to
date.
I've received a three-sheet Excel workbook that I can't read using ODBC.
(I can read it using
GET DATA /TYPE=XLS). I
don't know in what version of Excel it was created, except that it's not
the new Excel format. The database wizard says it has no tables, and a
query that works elsewhere gives an error message I don't
understand.
"A query that works elsewhere": I have made one modified
version of the workbook that I can read using ODBC. Unfortunately, I
don't know what I did, and can't repeat it. The changes that I know of
are (a) the working version is Excel 2000; (b) filtered rows and hidden
columns are un-hidden. But doing those to the original, again, doesn't
result in a workbook I can read.
Here's the error when I use the query from the workbook I can read, to
try to read one that I can't. The Excel file is
not read-only, nor
open:
NEW FILE.
GET DATA /TYPE=ODBC /CONNECT=
'DSN=Excel Files;' +
'DBQ='
+
'C:\Documents and Settings\Richard\My
Documents'
+
'\Professional\MetLife\Live
data\'
+
'Sales_Hierarchy from Cube 03-30-09 CD WINCROSS
-- Excel 2000.xls' +
'DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
/SQL = "SELECT
"
+
"DIST, AGENT_ID, PRODUCER,
BROKER_ID, BROKER, " +
"N_BRKR_ID,
N_BRKR, RSM, RSD,
"
+
"`Sales Head` AS
Sales_Head, "
"N_BRKR_CD,
MAIN_CD, RSM_CD, RSD_CD, SH_CD, "+
"`N_BRKR_CD Syntax` AS
N_BRKR_CD_Syntax, F17, "+
"`MAIN_CD Syntax` AS MAIN_CD_Syntax, `RSM_CD Syntax` AS
RSM_CD_Syntax, `RSD_C
D Syntax` AS"+
" RSD_CD_Syntax, `SH_CD Syntax` AS SH_CD_Syntax,
"
"N_BROKER_LABEL, MAIN_LABEL, RSM_LABEL,
RSD_LABEL, SH_LABEL FROM "+
" `'N_BRKR Unique$'`"
/ASSUMEDSTRWIDTH=255
>Warning. Command name: GET DATA
>SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] Cannot
update. Database or object is read-only.
CACHE.
DATASET NAME Excel_2000 WINDOW=FRONT.
"The more obscure the observed behavior, the simpler is
the underlying problem." Right?
-With many thanks,
Richard
=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
Locked
