Importing value labels from excel to spss

[Filip Van Droogenbroeck]

Dear SPSS users,

I have an excel file which is structured like this:
Variable1 Label1 Value1
Variable1 Label2 Value2
Variable1 Label3 Value3
Variable2 Label1 Value1
Variable3 Label1 Value1
Variable3 Label2 Value2

Is there a way to import this into SPSS?
Is there a way to get it into this:

VALUE LABELS
Variable1 {value1} "Label1"
{value2} "Label2"
{value3} "Label3" /
Variable2 {value1} "Label1" /
Variable3 {value1} "Label1"
{value2} "Label2".

?

Kind regards,
Filip Van Droogenbroeck

[Ruben Geert van den Berg]

Sure. There's two main options.

1) Have Python read the .xls file and send ADD VALUE LABELS commands to SPSS by spss.Submit using the xls cell contents

2) Read the xls file into SPSS => declare a long empty string variable => CONCAT lines of ADD VALUE LABELS commands into this string variable => save this variable as text with ".sps" as file extension instead of ".txt" => use INSERT FILE for running the syntax.

You might want to look into

http://www.spss-tutorials.com/xlrd-python/
http://www.spss-tutorials.com/concatenate/
http://www.spss-tutorials.com/insert/

HTH,

Ruben

P.s. as this question comes by more often, I might write a tutorial with the required Python syntax on it.

[Filip Van Droogenbroeck]

Hi Ruben,

This question has been around a few times:
For example:
https://groups.google.com/forum/#!topic/comp.soft-sys.stat.spss/AQpTj_MOHpI

But nobody responded with a working example; A tutorial would be much appreciated. I think with the methods you suggest the problem remains how to create an automatic loop that knows when to stop concatenating when a new variable appears. Because

Variable1 Label1 Value1
Variable1 Label2 Value2
Variable1 Label3 Value3
Variable2 Label1 Value1
Variable3 Label1 Value1
Variable3 Label2 Value2

Should become
value labels variable1 value1 "label1" value2 "label2" value3 "label3".
value labels variable2 value1 "label1".
value labels variable3 value1 "label1" value2 "label2".

And not
value labels variable1 value1 "label1".
value labels variable1 value2 "label2".
value labels variable1 value3 "label3".
...

The latter wouldn't work.

cheers,
F


Op donderdag 24 juli 2014 18:50:47 UTC+2 schreef Ruben Geert van den Berg:

[David Marso]

" the problem remains how to create an automatic loop that knows when to stop concatenating when a new variable appears"...
NO!
See ADD VALUE LABELS!
--

[Ruben Geert van den Berg]

Yeah, please note the difference between the VALUE LABELS and ADD VALUE LABELS commands.

Also, see http://www.spss-tutorials.com/changing-variable-properties-5-variable-and-value-labels/#vallabs.

But I still recommend the Python approach since I think it's cleaner and shorter. I'll post a working example if I can find the time.

The CONCAT and INSERT approach is a little more "old skool" than I like...

Best,

Ruben

[Filip Van Droogenbroeck]

Aha yes I see. Wish I had seen it earlier
Spent the whole afternoon programming a script in perl which transformed the variables :)

Tnx for the help!
cheers,
F


Op vrijdag 25 juli 2014 16:53:14 UTC+2 schreef Ruben Geert van den Berg:

[Ruben Geert van den Berg]

Hi Filip,

For working examples (including Excel test data) of the two approaches I suggested, see http://www.spss-tutorials.com/apply-dictionary-information-from-excel/.

Both of them may take you (much) less than a whole afternoon ;-)

Hope you find them helpful but suggestions are highly appreciated as well.

Best,

Ruben