Reading Multiple Excel Worksheets

[Sam Ballard]

Dear Listserve,

I'm trying to read in three Excel worksheets from the same file all at
once. I keep getting an error and I can't seem to find the problem. I've
tried the wizard too, but I can't get that to work either. The warning
states there is an illegal use of a '.' or '!'. Does anyone know what's
wrong with this syntax?

GET DATA
/TYPE=ODBC
 /CONNECT=
'DSN=Excel Files;DBQ=G:\Shared\SS\From RPNet\2008 Projects\SDUSD Data
Project\Data\fuzzymatchSDCCD.xls;' +
 'DriverId=1046; MaxBufferSize=2048;PageTimeout=5;'
 /SQL =
' SELECT fuzzymatchSDCCD$.  [SSN],  FUZZ'
' FROM  [fuzzymatchSDCCD$], [fuzzymatchSDCCD2$], [fuzzymatchSDCCD3$]'
'  WHERE  [fuzzymatchSDCCD2$]. [SSN] = [fuzzymatchSDCCD$]. [SSN] '
'  AND [fuzzymatchSDCCD3$]. [SSN] = [fuzzymatchSDCCD$]. [SSN] ' .





>Warning.  Command name: GET DATA
>SQLExecDirect failed :[Microsoft][ODBC Excel Driver] Invalid use
of '.', '!', or '()'. in query expression 'fuzzymatchSDCCD$.  [SSN'.


Thanks,
Sam Ballard
SDCCD
[hidden email]

=====================
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

[Sam Ballard]

Dear Listserve,

After reading up more on the problem let me clarify my original post. I
want to read three worksheets from a workbook from an Excel file. The
three worksheets contain two variables, which are the same for all three
sheets (there are too many cases to put all the data on one worksheet). So
I really want to add cases (each sheet has 60k cases, so the final file
would have 180k cases). Is there a way to do this efficiently? Or do I
need to read in one sheet a time and then merge them together?

Thanks,
Sam

=====================
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

[Carsten Pauck]

Hello Sam,

there is probably sth wrong with the sql statement. As far as I can
see the "where condition" does not make sence.

My suggestion is:

1.
Add a column ("LINE") with a line number in each excel-sheet. So
sheet1 in Excel looks like:
LINE FALL
1       1
2       2
3       3
...

And sheet2 looks like:
LINE FALL
1       60001
2       60002
3       60003
...

FALL is a variable with data to be read into spss.

2. Now you can read this excel file with three sheets into spss using this
syntax:

GET DATA /TYPE=ODBC /CONNECT=
 'DSN=Excel Files;DBQ=C:\Dokumente und'+
 ' Einstellungen\FSC\Desktop\Mappe1.xls;DriverId=790;MaxBufferSize='+
 '2048;PageTimeout=5;'
 /SQL = 'SELECT  T0.Line,  T0.Fall,   T1.Fall,  T2.Fall FROM  `sheet1$` '
 'T0, `sheet2$` T1, `sheet3$` T2 WHERE T1.Line = T0.Line  and T2.Line
= T1.Line   '
 /ASSUMEDSTRWIDTH=255
cache.

This syntax creates an spss dataset. In the first column you find the
line number of the
excel sheet. In the second column ("Fall") you find the data of sheet1
and in the
third column (fall_a) of sheet2 ...

3. Now you can restructure the dataset have one column with all the data:

VARSTOCASES /ID = id
 /MAKE trans1 FROM Fall Fall_A Fall_B
 /INDEX = Index1(trans1)
 /KEEP =  Line
 /NULL = KEEP.

4. It would be better to store data with more than 65k cases in a database like
ms access.

Carsten.

2008/5/8, Sam Ballard <[hidden email]>:

> Dear Listserve,
>
>  I'm trying to read in three Excel worksheets from the same file all at
>  once. I keep getting an error and I can't seem to find the problem. I've
>  tried the wizard too, but I can't get that to work either. The warning
>  states there is an illegal use of a '.' or '!'. Does anyone know what's
>  wrong with this syntax?
>
>  GET DATA
>  /TYPE=ODBC
>   /CONNECT=
>  'DSN=Excel Files;DBQ=G:\Shared\SS\From RPNet\2008 Projects\SDUSD Data
>  Project\Data\fuzzymatchSDCCD.xls;' +
>   'DriverId=1046; MaxBufferSize=2048;PageTimeout=5;'
>   /SQL =
>  ' SELECT fuzzymatchSDCCD$.  [SSN],  FUZZ'
>  ' FROM  [fuzzymatchSDCCD$], [fuzzymatchSDCCD2$], [fuzzymatchSDCCD3$]'
>  '  WHERE  [fuzzymatchSDCCD2$]. [SSN] = [fuzzymatchSDCCD$]. [SSN] '
>  '  AND [fuzzymatchSDCCD3$]. [SSN] = [fuzzymatchSDCCD$]. [SSN] ' .
>
>
>
>
>
>  >Warning.  Command name: GET DATA
>  >SQLExecDirect failed :[Microsoft][ODBC Excel Driver] Invalid use
>  of '.', '!', or '()'. in query expression 'fuzzymatchSDCCD$.  [SSN'.
>
>
>  Thanks,
>  Sam Ballard
>  SDCCD
>  [hidden email]
>
>  =====================
>  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
>

=====================
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