ODBC Query that returns a file?

[Marks, Jim]

We have a database table that holds  text files as one of the columns.

 

When we query the database table, the file is returned as a column. It is formatted as numeric—hex format.

 

GET DATA

  /TYPE=ODBC

  /CONNECT='valid-connection-string’

  /SQL="SELECT id, SEQUENCENO, POSTTIME, FILE_NAME, FILE_DATE, FILE_SIZE, FILE_TYPE, "+

    "FILE_PATH, FILE_DATA, MACHINENAME, UPDATEDATE, PRODUCT_TYPE  FROM database1.FILE_DATA  WHERE "+

    "(FILE_NAME = 'sample.INI' AND id  = 1000)"

  /ASSUMEDSTRWIDTH=5000.

CACHE.

 

 

We need to be able to read the file into a column as a text format—the hex only translates into the 1^st 255 characters.

 

Any ideas/ suggestions? Or are we limited by the structure of the database?

 

Jim Marks

Director, Market Research

x1616

 

[mpirritano]

Listers,

 

Can I use the following python program which comes with spss to export output to excel from a python program. Just prior to this syntax I have an spss.submit statement that results in an spss graph. Can this graph then cross over to the python program? Does that make sense? The graph is created in the unseen spss process, can I then maninpulate it with python? Like export it to excel?

 

Thanks in advance,

matt

 

 

import SpssClient, sys

               

#ExportToExcel.py

SpssClient.StartClient()

OutputDoc = SpssClient.GetDesignatedOutputDoc()

OutputDoc.ClearSelection()

# Create a new workbook and export all charts to a worksheet

# named Utilization.

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelOperationOptions,

                            "CreateWorkbook")

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelSheetNames,

                            "Utilization")

OutputItems = OutputDoc.GetOutputItems()

for index in range(OutputItems.Size()):

    OutputItem = OutputItems.GetItemAt(index)

    if (OutputItem.GetType() == SpssClient.OutputItemType.HEAD and

        OutputItem.GetDescription() == "Utilization"):

            OutputItem.SetSelected(True)

try:

    OutputDoc.ExportDocument(SpssClient.SpssExportSubset.SpssSelected,

                            "outfile",

                            SpssClient.DocExportFormat.SpssFormatXls)

except:

    print sys.exc_info()[1]

OutputDoc.ClearSelection()

SpssClient.StopClient()

 

 

Matthew Pirritano, Ph.D.

Research Analyst IV

Medical Services Initiative (MSI)

Orange County Health Care Agency

[phone]

[Peck, Jon]

Without reading every line, what you want to do should be fine if you are in the normal internal mode.  From your text, this is within a begin/end program block.  If you have v17, you can call scripting apis from a program.  For v16, you cannnot use the SpssClient methods in a program, but you could run it as a script.

If you are external mode, though, there is no Viewer, and the scripting methods are not available.

But an easier way to do this would be simply to use OMS to capture and export the chart of interest to Excel.  That would be faster, and it would work in both internal and external modes.

Regards,
Jon Peck


-----Original Message-----
From: SPSSX(r) Discussion on behalf of Pirritano, Matthew
Sent: Tue 7/7/2009 6:41 PM
To: [hidden email]
Subject:      [SPSSX-L] using export to excel python program in a python progam              not spss              program block

Listers,



Can I use the following python program which comes with spss to export output to excel from a python program. Just prior to this syntax I have an spss.submit statement that results in an spss graph. Can this graph then cross over to the python program? Does that make sense? The graph is created in the unseen spss process, can I then maninpulate it with python? Like export it to excel?



Thanks in advance,

matt





import SpssClient, sys

               

#ExportToExcel.py

SpssClient.StartClient()

OutputDoc = SpssClient.GetDesignatedOutputDoc()

OutputDoc.ClearSelection()

# Create a new workbook and export all charts to a worksheet

# named Utilization.

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelOperationOptions,

                            "CreateWorkbook")

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelSheetNames,

                            "Utilization")

OutputItems = OutputDoc.GetOutputItems()

for index in range(OutputItems.Size()):

    OutputItem = OutputItems.GetItemAt(index)

    if (OutputItem.GetType() == SpssClient.OutputItemType.HEAD and

        OutputItem.GetDescription() == "Utilization"):

            OutputItem.SetSelected(True)

try:

    OutputDoc.ExportDocument(SpssClient.SpssExportSubset.SpssSelected,

                            "outfile",

                            SpssClient.DocExportFormat.SpssFormatXls)

except:

    print sys.exc_info()[1]

OutputDoc.ClearSelection()

SpssClient.StopClient()





Matthew Pirritano, Ph.D.

Research Analyst IV

Medical Services Initiative (MSI)

Orange County Health Care Agency

[phone]

[Matthew Pirritano]

thanks Jon,

I'll look into OMS. Since I'm doing in external mode I didn't think it made sense. I want it to be completely automated so that someone with no SPSS could get the output by running a simple python program.

matt

 

Matthew Pirritano, Ph.D.
Email: [hidden email]

---

From: "Peck, Jon" <[hidden email]>
To: [hidden email]
Sent: Tuesday, July 7, 2009 6:22:13 PM
Subject: Re: using export to excel python program in a python progam not spss program block

Without reading every line, what you want to do should be fine if you are in the normal internal mode.  From your text, this is within a begin/end program block.  If you have v17, you can call scripting apis from a program.  For v16, you cannnot use the SpssClient methods in a program, but you could run it as a script.

If you are external mode, though, there is no Viewer, and the scripting methods are not available.

But an easier way to do this would be simply to use OMS to capture and export the chart of interest to Excel.  That would be faster, and it would work in both internal and external modes.

Regards,
Jon Peck


-----Original Message-----
From: SPSSX(r) Discussion on behalf of Pirritano, Matthew
Sent: Tue 7/7/2009 6:41 PM
To: [hidden email]
Subject:      [SPSSX-L] using export to excel python program in a python progam              not spss              program block

Listers,



Can I use the following python program which comes with spss to export output to excel from a python program. Just prior to this syntax I have an spss.submit statement that results in an spss graph. Can this graph then cross over to the python program? Does that make sense? The graph is created in the unseen spss process, can I then maninpulate it with python? Like export it to excel?



Thanks in advance,

matt





import SpssClient, sys

               

#ExportToExcel.py

SpssClient.StartClient()

OutputDoc = SpssClient.GetDesignatedOutputDoc()

OutputDoc.ClearSelection()

# Create a new workbook and export all charts to a worksheet

# named Utilization.

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelOperationOptions,

                            "CreateWorkbook")

OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelSheetNames,

                            "Utilization")

OutputItems = OutputDoc.GetOutputItems()

for index in range(OutputItems.Size()):

    OutputItem = OutputItems.GetItemAt(index)

    if (OutputItem.GetType() == SpssClient.OutputItemType.HEAD and

        OutputItem.GetDescription() == "Utilization"):

            OutputItem.SetSelected(True)

try:

    OutputDoc.ExportDocument(SpssClient.SpssExportSubset.SpssSelected,

                            "outfile",

                            SpssClient.DocExportFormat.SpssFormatXls)

except:

    print sys.exc_info()[1]

OutputDoc.ClearSelection()

SpssClient.StopClient()





Matthew Pirritano, Ph.D.

Research Analyst IV

Medical Services Initiative (MSI)

Orange County Health Care Agency

[phone]