Complex batch sequence

[Mark Vande Kamp-2]

I am working on a large database/analysis where we are using SQL, a binary utility program, and SPSS.

A common sequence is to:

1)  use SQL to pull a two sets of data in tab-delimited text format
2)  use our utility to match and merge the datasets (the utility does this MUCH faster than it could be programmed in SQL)
3) import the merged text file into SPSS

We want to be able to set this sequence up as a batch process.

I know that SPSS can incorporate SQL commands, but the need to run the utility on a text file seems to rule that approach out.

A Python script seems like the way to go. Unfortunately, I'm clueless about Python. I guess I could go about learning Python from the ground up, but I generally find it much faster to work from relevant examples.

So, I'm looking for examples of Python scripts that control a batch process by submitting appropriate commands to a sequence of different applications. From my research so far, it looks like I want my specific script to:

1) submit a pre-specified and formatted 'sqlcmd' command line that executes a stored query (XX.sql file) and saves the output as a text file,
2) wait until SQL is finished writing to the specified text file
3) submit the binary (XX.exe) command with pre-specified parameters
4) wait until the binary has finished writing to the specified text file
5) starts SPSS and executes a syntax file that reads the processed text file

Does that all make sense? Any help with examples or other tips?

Thanks,

Mark

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

[Jon K Peck]

See below.
Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Mark Vande Kamp <[hidden email]>
To:	[hidden email]
Date:	08/23/2010 03:34 PM
Subject:	[SPSSX-L] Complex batch sequence
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

I am working on a large database/analysis where we are using SQL, a binary utility program, and SPSS.

A common sequence is to:

1)  use SQL to pull a two sets of data in tab-delimited text format
2)  use our utility to match and merge the datasets (the utility does this MUCH faster than it could be programmed in SQL)
3) import the merged text file into SPSS

We want to be able to set this sequence up as a batch process.

I know that SPSS can incorporate SQL commands, but the need to run the utility on a text file seems to rule that approach out.

A Python script seems like the way to go. Unfortunately, I'm clueless about Python. I guess I could go about learning Python from the ground up, but I generally find it much faster to work from relevant examples.

So, I'm looking for examples of Python scripts that control a batch process by submitting appropriate commands to a sequence of different applications. From my research so far, it looks like I want my specific script to:

1) submit a pre-specified and formatted 'sqlcmd' command line that executes a stored query (XX.sql file) and saves the output as a text file,
2) wait until SQL is finished writing to the specified text file
3) submit the binary (XX.exe) command with pre-specified parameters
4) wait until the binary has finished writing to the specified text file
5) starts SPSS and executes a syntax file that reads the processed text file

Does that all make sense? Any help with examples or other tips?
>>>
Whether you need Python or not really depends on how much you want to vary and generalize the job.  If the job always has these steps and just needs some parameters passed, you could use a simple bat file that runs each of these steps in sequence pass parameters on the command line or via files.

If you want the SPSS job to be smart and figure out what to do based on the metadata of the particular datasets you are using, then Python might come into play, but there are extension commands such as SPSSINC SELECT VARIABLES and SPSSINC PROCESS FILES that can help with this without requiring you to learn Python programmability.  I can go into more detail if you want to go that route.

If you don't like bat files, you could run the sequence of steps using the HOST command in SPSS Statistics.

HTH,
Jon Peck



Thanks,

Mark

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