csv to spss conversion

[Bruce Colton]

I tried to modify some python that Albert-Jan very graciously helped me with time time ago.  The original code which worked very well, aggregated a list of csv files, and then converted the 1 large csv file to spss.  In the present case, I have a need to convert each of the small individual csv files in the list to spss, and add them as I process the list.  I tried running the following w/o success.  

BEGIN PROGRAM.
import csv, glob, os.path, spss
path = "c:/users/bruce/documents/pjm_2010_3"
csvs = glob.glob(os.path.join(path, "*.csv"))
for day, csvx in enumerate(sorted(csvs)):
       infile = open(os.path.join(path, csvx), "rb")
spss.Submit (r"""
            get data /type=txt
            /file= + infile +
            /delimiters=","
           /arrangement=delimited
           /firstcase = 2
           /importcase=all
          /variables= seqnum f3.0 name a20 timepoint a16  rtprice f8.2 rtloss f8.0 rtcongest f8.0 daprice f8.2 daloss f8.0 dacongest f8.0.
   add files
     /file='c:/users/bruce/documents/pjm_2010_3/merged.sav'
     /file=*.
    exe.
    save outfile='c:/users/bruce/documents/pjm_2010_3/merged.sav'.
    """)
infile.close()
END PROGRAM.

 

It crashed on the Get Data.  Invalid subcommand file.  I also tried /file= + csvx + to no avail, as well as in quotes.  Does anyone know how to pull the path/filenames in the list csvx into my get data statement?  At the start, merged.sav is a shell - a file with variables given above, and no cases.  Thanks very much in advance for any assistance. 

 

 

[Jon K Peck]

I see two problems here.  See below.
Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Bruce Colton <[hidden email]>
To:	[hidden email]
Date:	02/02/2010 08:19 PM
Subject:	[SPSSX-L] csv to spss conversion
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

I tried to modify some python that Albert-Jan very graciously helped me with time time ago.  The original code which worked very well, aggregated a list of csv files, and then converted the 1 large csv file to spss.  In the present case, I have a need to convert each of the small individual csv files in the list to spss, and add them as I process the list.  I tried running the following w/o success.  
BEGIN PROGRAM.
import csv, glob, os.path, spss
path = "c:/users/bruce/documents/pjm_2010_3"
csvs = glob.glob(os.path.join(path, "*.csv"))
for day, csvx in enumerate(sorted(csvs)):
      infile = open(os.path.join(path, csvx), "rb")

>>> The get data command is expected a file name, but you are actually opening the file here, so infile is a handle to that open file.
Instead, try just joining the parts of the name:
        infile = os.path.join(path, csvx)


       spss.Submit (r"""
           get data /type=txt
           /file= + infile +
>>>Down here you want infile to be a parameter whose value is substituted each time through the loop, but you are in a triple-quoted string, and + infile + is just text.

Instead, write your command like this.
          spss.Submit(r"""get data /type=txt
           /file = "%s"
          /delimiters=","
          /arrangement=delimited
          /firstcase = 2
          /importcase=all
         /variables= seqnum f3.0 name a20 timepoint a16  rtprice f8.2 rtloss f8.0 rtcongest f8.0 daprice f8.2 daloss f8.0 dacongest f8.0.
  add files
    /file='c:/users/bruce/documents/pjm_2010_3/merged.sav'
    /file=*.
   exe.
   save outfile='c:/users/bruce/documents/pjm_2010_3/merged.sav'.
   """ % infile)
infile.close()
END PROGRAM.

That will substitute the current value of the variable infile where the %s occurs each time it goes through the loop.

HTH,
Jon Peck
 
It crashed on the Get Data.  Invalid subcommand file.  I also tried /file= + csvx + to no avail, as well as in quotes.  Does anyone know how to pull the path/filenames in the list csvx into my get data statement?  At the start, merged.sav is a shell - a file with variables given above, and no cases.  Thanks very much in advance for any assistance.