Macro with GET DATA /ODBC
Locked
 
|
Hello everyone,
=====================
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
I am having a problem creating a macro that GETS DATA treating an excel spreadsheet as a database (i.e., using SQL). I believe my issue involves concatenating strings especially in terms of how the single apostrophe's are coming through. I want to say the issue I am coming up against is similar to the problem resolved in this thread (https://listserv.uga.edu/cgi-bin/wa?A2=ind0212&L=SPSSX-L&P=R15738) with the heading Subject:Re: Macro with GET DATA /ODBC. I seem to be able to define the macro but when I try to call it I get the error: >SQLExecDirect failed :[Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Dim$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Dim$' is not a local object, check your network connection or contact the server administrator. SET MPRINT YES. *******************************************************. DEFINE !DUMMY(location = !TOKENS(1) / file = !TOKENS(1)) GET DATA /TYPE=ODBC /CONNECT=!QUOTE(!CONCAT("DSN=ExcelFiles;DBQ=",!UNQUOTE(!location),!UNQUOTE(!file),";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")) /SQL='SELECT x, y, z, Score FROM `Dim$`' /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. DATASET NAME Dim WINDOW=FRONT. !ENDDEFINE. *******************************************************. !DUMMY location = '\\network\company\first.last\Desktop' file = '2015q2 client position1 position2 dim.xlsx'. Any help would be greatly appreciated, Derek Mracek |
Administrator
|
Fundamental MACRO 101 lesson A.
Make sure the query works OUTSIDE the macro before putting it inside a macro. You have SET MPRINT ON. That should echo the exact expanded syntax to the output object. Maybe you should scrutinize that and see if it works. You reference an object DIM$. That is what the complaint seems to be. So, aside from the !CONCAT and !UNQUOTE logic, you need to build the query so it works, post that along with what has been expanded from the macro and we can go from there.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
In reply to this post by dmracek23
Before guessing more mysterious problems:
1 - You are missing a back slash between the file location and the xls file name. 2 - Are you 100% sure the `Dim$` table exists in the xls file? (Does the table name even need to be quoted in the SQL query?) If 1 is the problem, the error message may be a red herring. |
|
|
In reply to this post by David Marso
Hello David, Thank you for your quick reply. I apologize. I forgot to mention that the query runs outside of the macro. I am using SPSS 20. GET DATA /TYPE=ODBC /CONNECT='DSN=Excel Files;DBQ=\\network\company\first.last\Desktop\2015q2 client position1 position2 dim.xlsx;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;' /SQL='SELECT x, y, z, Score FROM `Dim$`' /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. DATASET NAME Dim WINDOW=FRONT. On Thu, Aug 6, 2015 at 11:22 AM, David Marso <[hidden email]> wrote: Fundamental MACRO 101 lesson A. |
|
|
In reply to this post by Andy W
Hello Andy, Thank you for your help. I fixed (1; back slash) and in terms of (2) yes that sheet is in the spreadsheet. The query works outside of the macro. When looking at the macro output the apostrophes seem to be appropriate, nevertheless, it is not working. Here is the macro output: 46 0 M> GET DATA /TYPE=ODBC /CONNECT= 'DSN=Excel Files;DBQ=\\network\company\first.last\Desktop\2015q2 client position1 position2 dim.xlsx ;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;' /SQL='SELECT x, y, z, Score FROM `Dim$`' /ASSUMEDSTRWIDTH=255. >Warning. Command name: GET DATA >SQLExecDirect failed :[Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Dim$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Dim$' is not a local object, check your network connection or contact the server administrator. 47 0 M> CACHE. 48 0 M> EXECUTE. On Thu, Aug 6, 2015 at 11:27 AM, Andy W <[hidden email]> wrote: Before guessing more mysterious problems: |
|
Administrator
|
In reply to this post by Andy W
Great catch Andy! So that would require: ... /CONNECT=!CONCAT(!QUOTE(!CONCAT("DSN=ExcelFiles;DBQ=",!UNQUOTE(!location),'/',!UNQUOTE(!file)),.... For starters... Run that and verify the expanded syntax from output against the 'verified' runnable query? Patch as necessary.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
Administrator
|
I didn't see any detectable difference between the macro expansion and the original query.
I don't have ODBC drivers installed on my box and have no good reason to bother to do so. FWIW: Is there any particular reason that you must use ODBC rather than the much simpler GET DATA /TYPE=XLS ? I have wrapped the following in a macro previously and it was trivial. -- GET DATA /TYPE=XLS /FILE='C:\Temp\testODBC.xls' /SHEET=name 'Sheet1' /CELLRANGE=full /READNAMES=on /ASSUMEDSTRWIDTH=32767. EXECUTE. DATASET NAME DataSet1 WINDOW=FRONT.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |