putting excel column letters into new custom attribute in variable view

[Art Kendall]

When I receive an excel file (e.g., from SharePoint) sometimes there are column headers that do not easily transform into SPSS variable names.

In that instance I UNcheck "read variable names from the first row of data".  I then start the range with A2:

Has anybody created a Python or other command to put a custom attribute in variable view that is the letter(s) that are used in Excel to designate the columns, e.g., A, B, C, ... AA, AB, AC?

Would others find it helpful if whenever an Excel file is read in there would be an option to put the originating Excel column names in a custom attribute?

[Jon K Peck]

Here's a bit of Python code that renames all the variables assuming that you start with A.  Note that certain combinations such as BY are illegal names, so they are doubled.  If the email mangles the indentation, I can send you the code as an attachment.

begin program.
# rename variables into Excel style
import spss, string, textwrap
nvar = spss.GetVariableCount()
names = [spss.GetVariableName(i) for i in range(nvar)]

newnames = []
for i in range(nvar):
    major, minor = divmod(i, 26)
    letter = string.ascii_uppercase[minor]
    if major > 0:
        letter = string.ascii_uppercase[major-1] + letter
    if letter in ["BY", "GE", "GT", "LT", "LE", "EQ", "NE", "OR"]:
        letter = letter + letter
    newnames.append(letter)

cmd = """rename variables (%s=%s)""" % (" ".join(names), " ".join(newnames))
cmd = "\n".join(textwrap.wrap(cmd, width=100))
spss.Submit(cmd)
end program.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        Art Kendall <[hidden email]>
To:        [hidden email]
Date:        08/13/2014 09:30 AM
Subject:        [SPSSX-L] putting excel column letters into new custom attribute in variable view
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

When I receive an excel file (e.g., from SharePoint) sometimes there are
column headers that do not easily transform into SPSS variable names.

In that instance I UNcheck "read variable names from the first row of data".
I then start the range with A2:

Has anybody created a Python or other command to put a custom attribute in
variable view that is the letter(s) that are used in Excel to designate the
columns, e.g., A, B, C, ... AA, AB, AC?

Would others find it helpful if whenever an Excel file is read in there
would be an option to put the originating Excel column names in a custom
attribute?



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/putting-excel-column-letters-into-new-custom-attribute-in-variable-view-tp5726946.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD


===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Art Kendall]

Thank you for getting back to me.

I should have been clearer.
The purpose of having the "FromExcel" variable attribute is to keep track of where the variable comes from to facilitate data cleaning.

I would like to have the originating Excel Column in a custom variable attribute in variable view.
That is I would like to
(1) create a new variable attribute column labelled something like "OriginalColumn" or "FromExcel".
(2) enter as values of that attribute the column designation in Excel

Below is example syntax which does it with separate commands. I have previously just used WordPerfect to create a set of syntax that I use in an INSERT command.

It then has my attempt to modify the Python code.
which result in this message.
>Error # 1.  Command name: ATTRIBUTE
>The first word in the line is not recognized as an SPSS Statistics command.
>Execution of this command stops.
Traceback (most recent call last):
  File "<string>", line 17, in <module> 
  File "C:\Python27\lib\site-packages\spss210\spss\spss.py", line 1525, in Submit
    raise SpssError,error
spss.errMsg.SpssError: [errLevel 3] Serious error.

When the syntax is working there should be 2 new custom attributes "Manual"  and "FromExcel"
that have the same contents.
* pretend this data came from Excel.
data list list /v01 v02 v03 (3f3).
begin data
1 2 3
4 5 6
7 8 9
end data.
VARIABLE ATTRIBUTE variables= v01 ATTRIBUTE= Manual ('A').
VARIABLE ATTRIBUTE variables= v02 ATTRIBUTE= Manual ('B').
VARIABLE ATTRIBUTE variables= v03 ATTRIBUTE= Manual ('C').
DISPLAY DICTIONARY.


begin program.
# Put original Excel Column Letters in Custom Variable Attribute"
import spss, string, textwrap
nvar = spss.GetVariableCount()
names = [spss.GetVariableName(i) for i in range(nvar)]

newnames = []
for i in range(nvar):
    major, minor = divmod(i, 26)
    letter = string.ascii_uppercase[minor]
    if major > 0:
        letter = string.ascii_uppercase[major-1] + letter

cmd = """VARIABLE ATTRIBUTE VARIABLE=  (%s=%s)""" % (" ".join(names), " ".join(newnames))
cmd = """ATTRIBUTE=FromExcel (
cmd = "\n".join(textwrap.wrap(cmd, width=100)))"""
spss.Submit(cmd)
end program.

display dictionary.

[Art Kendall]

The origin of this problem is that SharePoint produces an Excel file that has column headers that can be a few hundred characters long. The headers have the complete question and instructions to the respondent.
Therefore when reading in the Excel file I UNcheck the box for reading variable names and type in a range like A2:BC2000.

If this function were made an option when reading from an Excel file it would be useful if
a range like C2:BQ2000 produced a custom attribute that ran from C to BQ
a range like E5:AX150   produced a custom attribute that ran from E to AX
etc.

[Jon K Peck]

This should do it.

begin program.
# Add custom Excel source attribute
import spss, string, textwrap
nvar = spss.GetVariableCount()
names = [spss.GetVariableName(i) for i in range(nvar)]

attr = []
for i in range(nvar):
    major, minor = divmod(i, 26)
    letter = string.ascii_uppercase[minor]
    if major > 0:
        letter = string.ascii_uppercase[major-1] + letter
    if letter in ["BY", "GE", "GT", "LT", "LE", "EQ", "NE", "OR"]:
        letter = letter + letter
    attr.append(letter)

for i in range(nvar):
    spss.Submit("""variable attribute variables=%s attribute=Excel("%s").""" % (names[i], attr[i]))
end program.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        Art Kendall <[hidden email]>
To:        [hidden email]
Date:        08/13/2014 12:36 PM
Subject:        Re: [SPSSX-L] putting excel column letters into new custom attribute in variable view
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thank you for getting back to me.

I should have been clearer.
The purpose of having the "FromExcel" variable attribute is to keep track of
where the variable comes from to facilitate data cleaning.

I would like to have the originating Excel Column in a custom variable
attribute in variable view.
That is I would like to
(1) create a new variable attribute column labelled something like
"OriginalColumn" or "FromExcel".
(2) enter as values of that attribute the column designation in Excel

Below is example syntax which does it with separate commands. I have
previously just used WordPerfect to create a set of syntax that I use in an
INSERT command.

It then has my attempt to modify the Python code.
which result in this message.
>Error # 1.  Command name: ATTRIBUTE
>The first word in the line is not recognized as an SPSS Statistics command.
>Execution of this command stops.
Traceback (most recent call last):
 File "<string>", line 17, in <module>
 File "C:\Python27\lib\site-packages\spss210\spss\spss.py", line 1525, in
Submit
   raise SpssError,error
spss.errMsg.SpssError: [errLevel 3] Serious error.

When the syntax is working there should be 2 new custom attributes "Manual"
and "FromExcel"
that have the same contents.
* pretend this data came from Excel.
data list list /v01 v02 v03 (3f3).
begin data
1 2 3
4 5 6
7 8 9
end data.
VARIABLE ATTRIBUTE variables= v01 ATTRIBUTE= Manual ('A').
VARIABLE ATTRIBUTE variables= v02 ATTRIBUTE= Manual ('B').
VARIABLE ATTRIBUTE variables= v03 ATTRIBUTE= Manual ('C').
DISPLAY DICTIONARY.


begin program.
# Put original Excel Column Letters in Custom Variable Attribute"
import spss, string, textwrap
nvar = spss.GetVariableCount()
names = [spss.GetVariableName(i) for i in range(nvar)]

newnames = []
for i in range(nvar):
   major, minor = divmod(i, 26)
   letter = string.ascii_uppercase[minor]
   if major > 0:
       letter = string.ascii_uppercase[major-1] + letter

cmd = """VARIABLE ATTRIBUTE VARIABLE=  (%s=%s)""" % (" ".join(names), "
".join(newnames))
cmd = """ATTRIBUTE=FromExcel (
cmd = "\n".join(textwrap.wrap(cmd, width=100)))"""
spss.Submit(cmd)
end program.

display dictionary.







-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/putting-excel-column-letters-into-new-custom-attribute-in-variable-view-tp5726946p5726950.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD


===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Art Kendall]

Thank you.

For the sake of the archives here is demo syntax.

data list list /v01 v02 v03 (3f3).
begin data
1 2 3
4 5 6
7 8 9
end data.
VARIABLE ATTRIBUTE variables= v01 ATTRIBUTE= Manual ('A').
VARIABLE ATTRIBUTE variables= v02 ATTRIBUTE= Manual ('B').
VARIABLE ATTRIBUTE variables= v03 ATTRIBUTE= Manual ('C').
DISPLAY DICTIONARY.

begin program.
# Add custom Excel source attribute
import spss, string, textwrap
nvar = spss.GetVariableCount()
names = [spss.GetVariableName(i) for i in range(nvar)]

attr = []
for i in range(nvar):
    major, minor = divmod(i, 26)
    letter = string.ascii_uppercase[minor]
    if major > 0:
        letter = string.ascii_uppercase[major-1] + letter
    if letter in ["BY", "GE", "GT", "LT", "LE", "EQ", "NE", "OR"]:
        letter = letter + letter
    attr.append(letter)

for i in range(nvar):
    spss.Submit("""variable attribute variables=%s attribute=Excel("%s").""" % (names[i], attr[i]))
end program.
display dictionary.

[Art Kendall]

Thank you.

This will be very useful in having an audit trail when data is read from Excel even when variable names in the excel are usable in SPSS. At least in my use of SPSS it is very common to rearrange the order of the variables.