Convert a DDF File to a Excel File.

We noticed that someone recently ran a search on our blog to try and find out how to convert a DDF to excel. This is a simple thing to do and to be honest there will be lots of examples in the DDL. Just install the DDL and then check the following directory out,

“C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Scripts\Data Management”.

If you don’t have the DDL installed then read on.

First off we need a DMS file. So open up professional and create an empty on. ( To be honest you can do this in notepad also if you done have professional , you just have to type more ). Next in your dms press the following key combination to fire up your macros.

[ctrl]+[M] [I] [TAB]

and you should end up with ,

InputDatasource(Input, "")
    ConnectionString = ""
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

Do it again but this time change the I to an O, make sure you have placed your cursor after the first bit of code.

[ctrl]+[M] [O] [TAB]

you will then end up with ,

InputDatasource(Input, "")
    ConnectionString = ""
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = ""
    MetaDataOutputName = "???.mdd"
End OutputDatasource

Next place your cursor in between the “” of the connection string in the InputDatasource and use the connection string manager  , found under tools in professional,  to connect to your DDF file. Do the same for the OutputDatasource and enter the location of the excel file that you want to create. Your connection string screen will need to look like this one.

Excel Settings

Next delete the MetaDataOutputName = “???.mdd” row from the output source and you should basically end up with a script that looks like this.

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location=Museum.ddf;Initial Catalog=C:Program FilesIBMSPSSDataCollection6DDLDataData Collection FileMuseum.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrCsvDsc;Location=c:tempmuseum.csv"
End OutputDatasource

When you look at the csv in excel you will see something like this,

And there you have it , a DMS script that takes your DDF and puts it into excel.

2 thoughts on “Convert a DDF File to a Excel File.”

  1. When choosing Excel Files as output data source, if we have open-ended answer longer then 255 characters, the verbatim will be truncated. Is there any way we can avoid long verbatim being truncated in Excel? please advise. thank you!

    • Hi Wendy. I think this is down to the version of excel you are using. Later version i believe 2007 on-wards do not strip the characters. What version are you using and i will try and test for you ?

Leave a Comment

%d bloggers like this: