How do I import data from a PL/SQL package into SPSS?

Question

I have this huge Oracle package that generates a .CSV file after performing the traditional ETL process. Currently, I am using a bunch of staging tables to load data from various sources and transforming the data using the listagg function and eventually dumping it as a clob. I am looking at about 300-400 columns with varying datatypes.

What we are doing?

Export to .CSV file then import to SPSS... It requires a lot of work to import data and setup the formatting in SPSS

What I am looking for?

Save all data as a table in my database...Issue: The data will be changing constantly and the data in the table needs to be session/transaction based. In other words, the old data needs to be truncated every time a new transaction is run.
Generate metadata file for the columns in existing .CSV file...Preferable solution
Generate a .SAV File (using package)...I have explored this option but didn't have any luck

I am seeking for the best way to import data from an Oracle package into SPSS program. If other options are available, I would be happy to explore that.

Can SPSS connect to the Oracle database as a data source? The data could then be put into a global temporary table, so each session would have its own private set; which involves less contention than truncating/inserting a normal table might. What metadata do you want to generate, and in what format? — Alex Poole, Apr 04 '14 at 16:27
I am not sure if we have the ODBC driver setup in SPSS to connect the Oracle Database, but I can find that out. Also, we are looking to generate the metadata for the existing file which should include the variable names, labels and formatting (data type and size). Format wise, I would say if we could get .mdd file or xml format that would work great. Any suggestions on being able to produce .sav file directly using PL/SQL? — FutbolFan, Apr 04 '14 at 19:00
You can write from PL/SQL using UTL_FILE or DBMS_OUTPUT and there are plenty of functions for working with XML e.g. http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb10pls.htm#ADXDB1100 but you might find writing your own file a litle laborious. Connecting to oracle via odbc and either just calling the package or getting it to return a refcursor that you can query might be easier — kayakpim, Apr 11 '14 at 07:33
Thanks Kayakpim! I think I have decided to go ahead and use a python script to convert the .csv to .xml format. And, then use the XMLTOSPSS class library from pmstation.com to convert it to .SAV format. The metadata will be created in the excel file as a dummy row to provide the data length and datatype.Thanks everyone! — FutbolFan, Apr 15 '14 at 16:10
What data import options other than CSV are available in SPSS (I don't know SPSS at all) ? It sounds part of the solution is global temporary table as suggested by @AlexPoole. I don't see an issue to generate metadata file, please be more specific what is the problem there. Instead of CSV you can generate XML easily (as also suggested by others). I'm afraid your question is a bit too vague to be addressed in more details. — user272735, Jan 18 '16 at 06:38

score 0 · Answer 1 · answered Jan 17 '16 at 17:05

I don't know how SPSS works and what does/might it expect as an input data, but in the comments you've mentioned that you're going to convert CSV files to XML in order to use so called "XMLTOSPSS". Basically you can unload data from Oracle directly to XML files using dbms_xmlquery package.

Generating headers for the CSV files based on Oracle tables should also be straightforward:

select
  c.column_name,
from all_tab_columns c
where c.owner=upper('{owner}') and c.table_name=upper('{table}')
order by c.owner, c.table_name, c.column_id;

How do I import data from a PL/SQL package into SPSS?

1 Answers1