have unbreakable space in Excel field treated as regular space

[Art Kendall]

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
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

[Jon K Peck]

Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

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




From:        Art Kendall <[hidden email]>
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
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]

Thanks for getting back to me.

I tried

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' ').

and neither worked.


Does anyone on the list know a simple way in excel to change the type, and what type= should be?

On 10/9/2012 10:45 AM, Jon K Peck wrote:

Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

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




From:        Art Kendall [hidden email]
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" [hidden email]

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
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

[Jon K Peck]

rtrim might not work, because you might have regular spaces after the nbsp.
But this worked on my test.
compute nbsp = replace(nbsp, string(160, pib1), "").
alter type nbsp(f8.0).


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




From:        Art Kendall <[hidden email]>
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 09:21 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for getting back to me.

I tried

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' ').

and neither worked.


Does anyone on the list know a simple way in excel to change the type, and what type= should be?

Art Kendall
Social Research Consultants
On 10/9/2012 10:45 AM, Jon K Peck wrote:
Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
peck@...
new phone: [phone]




From:        Art Kendall <Art@...>
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" [hidden email]

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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]

Thanks for your help.

It turns out that the problem was that
compute zip = replace(zip, string(160, pib1), "").
changes the trailing unbreakable space (ASCII 160) to an ordinary space (ASCII 32) not a null character.
alter type still chokes on the trailing space.

The work around now is to create a new variable and not worry about alter type.
numeric zip2(n5).
compute zip2= number(substr(zip,1,5),n5).

On 10/9/2012 11:42 AM, Jon K Peck wrote:

rtrim might not work, because you might have regular spaces after the nbsp.
But this worked on my test.
compute nbsp = replace(nbsp, string(160, pib1), "").
alter type nbsp(f8.0).


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




From:        Art Kendall [hidden email]
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 09:21 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for getting back to me.

I tried

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' ').

and neither worked.


Does anyone on the list know a simple way in excel to change the type, and what type= should be?

Art Kendall
Social Research Consultants
On 10/9/2012 10:45 AM, Jon K Peck wrote:
Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

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




From:        Art Kendall [hidden email]
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" [hidden email]

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
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

[Jon K Peck]

If the replace string is empty, it won't create new spaces, but since string variables are always blank padded, there could be a trailing ordinary blank.  But ALTER TYPE can still convert to numeric.  The syntax below does work.

One other possibility is that there are also other nonprinting characters in the field such as a tab or cr/lf, and that causes the problem.

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




From:        Art Kendall <[hidden email]>
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 11:11 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for your help.

It turns out that the problem was that
compute zip = replace(zip, string(160, pib1), "").
changes the trailing unbreakable space (ASCII 160) to an ordinary space (ASCII 32) not a null character.
alter type still chokes on the trailing space.

The work around now is to create a new variable and not worry about alter type.
numeric zip2(n5).
compute zip2= number(substr(zip,1,5),n5).

Art Kendall
Social Research Consultants
On 10/9/2012 11:42 AM, Jon K Peck wrote:
rtrim might not work, because you might have regular spaces after the nbsp.
But this worked on my test.
compute nbsp = replace(nbsp, string(160, pib1), "").
alter type nbsp(f8.0).


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
peck@...
new phone: [phone]




From:        Art Kendall <Art@...>
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 09:21 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for getting back to me.

I tried

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' ').

and neither worked.


Does anyone on the list know a simple way in excel to change the type, and what type= should be?

Art Kendall
Social Research Consultants
On 10/9/2012 10:45 AM, Jon K Peck wrote:
Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
peck@...
new phone: [phone]




From:        Art Kendall <Art@...>
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" [hidden email]

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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]

email does not seem to preserve the unbreakable space, but the workaround below does work.  The 6th character would be an ascii 160 in the original problem.
the workaround just does an alter type which truncated the string, and then another alter type which creates the number.
data list list / zip (a6).
begin data.
029091
123456
789001
end data.
alter type zip (a5).
alter type zip (n5).
On 10/9/2012 1:24 PM, Jon K Peck wrote:

If the replace string is empty, it won't create new spaces, but since string variables are always blank padded, there could be a trailing ordinary blank.  But ALTER TYPE can still convert to numeric.  The syntax below does work.

One other possibility is that there are also other nonprinting characters in the field such as a tab or cr/lf, and that causes the problem.

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




From:        Art Kendall [hidden email]
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 11:11 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for your help.

It turns out that the problem was that
compute zip = replace(zip, string(160, pib1), "").
changes the trailing unbreakable space (ASCII 160) to an ordinary space (ASCII 32) not a null character.
alter type still chokes on the trailing space.

The work around now is to create a new variable and not worry about alter type.
numeric zip2(n5).
compute zip2= number(substr(zip,1,5),n5).

Art Kendall
Social Research Consultants
On 10/9/2012 11:42 AM, Jon K Peck wrote:
rtrim might not work, because you might have regular spaces after the nbsp.
But this worked on my test.
compute nbsp = replace(nbsp, string(160, pib1), "").
alter type nbsp(f8.0).


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




From:        Art Kendall [hidden email]
To:        Jon K Peck/Chicago/IBM@IBMUS
Cc:        [hidden email]
Date:        10/09/2012 09:21 AM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as regular space

---

Thanks for getting back to me.

I tried

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' ').

and neither worked.


Does anyone on the list know a simple way in excel to change the type, and what type= should be?

Art Kendall
Social Research Consultants
On 10/9/2012 10:45 AM, Jon K Peck wrote:
Excel considers a cell containing a NBSP character to have type=2, i.e., text, so SPSS does, too.  You could replace those with "" before using ALTER TYPE or fix the spreadsheet before importing, but SPSS has to go by the Excel type code.

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




From:        Art Kendall [hidden email]
To:        [hidden email]
Date:        10/09/2012 08:33 AM
Subject:        [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" [hidden email]

---

I downloaded an excel file from the address check page at

http://www.melissadata.com/lookups/batchaddresscheck.asp

fields like zip have a trailing unbreakable space (ASCII 160).
If I read in the excel file in those fields come through as string
variables.
alter type zip (n5).
results in system missing data.
A work around is to
compute zip= substr(zip1,5)

However, is there a way to have the unbreakable space automatically
ignored or changed when reading in an excel file

--
Art Kendall
Social Research Consultants

=====================
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

[Bruce Weaver]

Hi Art.  In an earlier post in this thread, you listed syntax that WAS replacing ASCII 160 with a space.  I.e., you had:

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' '). /* <-- NOTE the ' ' .

Are you sure you weren't running this version of the REPLACE?

HTH.
Bruce

Art Kendall wrote

Thanks for your help.
       
        It turns out that the problem was that
        compute zip = replace(zip, string(160, pib1), "").
        changes the trailing unbreakable space (ASCII 160) to an
        ordinary space (ASCII 32) not a null character.
        alter type still chokes on the trailing space.
       
        The work around now is to create a new variable and not worry
        about alter type.
        numeric zip2(n5).
        compute zip2= number(substr(zip,1,5),n5).
       
     
      Art Kendall
Social Research Consultants
      On 10/9/2012 11:42 AM, Jon K Peck wrote:
   
    rtrim might not work,
        because you might
        have regular spaces after the nbsp.
     
      But this worked on my test.
     
      compute nbsp = replace(nbsp,
        string(160,
        pib1), "").
     
      alter type nbsp(f8.0).
     
     
     
        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM
        [hidden email] 
        new phone: [phone]
     
     
     
     
     
      From:      
         Art Kendall
        <[hidden email]>
     
      To:      
         Jon K
        Peck/Chicago/IBM@IBMUS
     
      Cc:      
          [hidden email] 
     
      Date:      
         10/09/2012 09:21 AM
     
      Subject:    
           Re: [SPSSX-L]
        have unbreakable space in Excel field treated as regular space
     
     
     
     
     
      Thanks for getting back to me.
       
        I tried
       
        compute zip =rtrim(zip,string(160, pib1)).
        compute zip = replace(zip, string(160, pib1),' ').
       
        and neither worked.
       
       
        Does anyone on the list know a simple way in excel to change the
        type,
        and what type= should be?
     
     
      Art Kendall
          Social Research Consultants
     
      On 10/9/2012 10:45 AM, Jon K Peck wrote:
     
      Excel considers a cell containing
        a
        NBSP character to have type=2, i.e., text, so SPSS does, too.
         You
        could replace those with "" before using ALTER TYPE or fix the
        spreadsheet before importing, but SPSS has to go by the Excel
        type code.
     
       
        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM
        [hidden email] 
        new phone: [phone]
       
       
       
     
        From:        Art
        Kendall <[hidden email]>
     
        To:         [hidden email] 
     
        Date:        10/09/2012
        08:33 AM
        Subject:        [SPSSX-L]
have
        unbreakable space in Excel field treated as        
             regular space
        Sent by:        "SPSSX(r)
Discussion"
      <[hidden email]>
       
     
     
       
     
          I downloaded an excel file from the address check page at
       
        http://www.melissadata.com/lookups/batchaddresscheck.asp 
         
          fields like zip have a trailing unbreakable space (ASCII 160).
          If I read in the excel file in those fields come through as
          string
          variables.
          alter type zip (n5).
          results in system missing data.
          A work around is to
          compute zip= substr(zip1,5)
         
          However, is there a way to have the unbreakable space
          automatically
          ignored or changed when reading in an excel file
         
          --
          Art Kendall
          Social Research Consultants
         
          =====================
          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]

good catch.  However, when I tried it with double quote there was no space between the quotes, but it turns out that there was an extra regular space.

When I cut_and_paste to syntax from excel there is one extra character an unbreakable space.
When I read in the spread sheet it makes the variable A7 with a unbreakable space and a regular space at the end!

However, the work around that does work without creating an extra variable is to alter type to the correct length and then alter type to n5.
alter type zip (5).
alter type zip (n5).

Whew all this time over an oddball character from excel!
On 10/9/2012 2:30 PM, Bruce Weaver wrote:

Hi Art.  In an earlier post in this thread, you listed syntax that WAS
replacing ASCII 160 with a space.  I.e., you had:

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' '). /* <-- NOTE the ' ' .

Are you sure you weren't running this version of the REPLACE?

HTH.
Bruce



Art Kendall wrote

Thanks for your help.

        It turns out that the problem was that
        compute zip = replace(zip, string(160, pib1), "").
        changes the trailing unbreakable space (ASCII 160) to an
        ordinary space (ASCII 32) not a null character.
        alter type still chokes on the trailing space.

        The work around now is to create a new variable and not worry
        about alter type.
        numeric zip2(n5).
        compute zip2= number(substr(zip,1,5),n5).


      Art Kendall
Social Research Consultants
      On 10/9/2012 11:42 AM, Jon K Peck wrote:

    rtrim might not work,
        because you might
        have regular spaces after the nbsp.

      But this worked on my test.

      compute nbsp = replace(nbsp,
        string(160,
        pib1), "").

      alter type nbsp(f8.0).



        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM

[hidden email]

        new phone: [phone]





      From:      
         Art Kendall
        <

Art@

>

      To:      
         Jon K
        Peck/Chicago/IBM@IBMUS

      Cc:      
         

[hidden email]

      Date:      
         10/09/2012 09:21 AM

      Subject:    
           Re: [SPSSX-L]
        have unbreakable space in Excel field treated as regular space





      Thanks for getting back to me.

        I tried

        compute zip =rtrim(zip,string(160, pib1)).
        compute zip = replace(zip, string(160, pib1),' ').

        and neither worked.


        Does anyone on the list know a simple way in excel to change the
        type,
        and what type= should be?


      Art Kendall
          Social Research Consultants

      On 10/9/2012 10:45 AM, Jon K Peck wrote:

      Excel considers a cell containing
        a
        NBSP character to have type=2, i.e., text, so SPSS does, too.
         You
        could replace those with "" before using ALTER TYPE or fix the
        spreadsheet before importing, but SPSS has to go by the Excel
        type code.


        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM

[hidden email]

        new phone: [phone]




        From:        Art
        Kendall <

Art@

>

        To:        

[hidden email]

        Date:        10/09/2012
        08:33 AM
        Subject:        [SPSSX-L]
have
        unbreakable space in Excel field treated as      
 
             regular space
        Sent by:        "SPSSX(r)
Discussion"
      <

[hidden email]

>





          I downloaded an excel file from the address check page at

        http://www.melissadata.com/lookups/batchaddresscheck.asp

          fields like zip have a trailing unbreakable space (ASCII 160).
          If I read in the excel file in those fields come through as
          string
          variables.
          alter type zip (n5).
          results in system missing data.
          A work around is to
          compute zip= substr(zip1,5)

          However, is there a way to have the unbreakable space
          automatically
          ignored or changed when reading in an excel file

          --
          Art Kendall
          Social Research Consultants

          =====================
          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

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715552.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

[Bruce Weaver]

Art, I think that that first ALTER TYPE was meant to have "(A5)" rather than just "(5)", right?  The FM indicates that you need an output format in the brackets, and 5 is not an output format.  If I try "ALTER TYPE stringvar (5)" on a string variable of length 8, I get this warning:

Text: (End of Command) Command: alter type
The format specified is not recognized.
Execution of this command stops.

But if I try "ALTER TYPE stringvar (A5)", there is no warning, and everything beyond 5 characters (from the left) gets chopped off.

Bruce

Art Kendall wrote

good catch.  However,
        when I tried it with double quote there was no space between the
        quotes, but it turns out that there was an extra regular space.
       
        When I cut_and_paste to syntax from excel there is one extra
        character an unbreakable space.
        When I read in the spread sheet it makes the variable A7 with a
        unbreakable space and a regular space at the end!
       
        However, the work around that does work without creating an
        extra variable is to alter type to the correct length and then
        alter type to n5.
        alter type zip (5).
        alter type zip (n5).
       
        Whew all this time over an oddball character from excel!
     
      Art Kendall
Social Research Consultants
      On 10/9/2012 2:30 PM, Bruce Weaver wrote:
   
   
      Hi Art.  In an earlier post in this thread, you listed syntax that WAS
replacing ASCII 160 with a space.  I.e., you had:

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' '). /* <-- NOTE the ' ' .

Are you sure you weren't running this version of the REPLACE?

HTH.
Bruce



Art Kendall wrote

     
        Thanks for your help.

        It turns out that the problem was that
        compute zip = replace(zip, string(160, pib1), "").
        changes the trailing unbreakable space (ASCII 160) to an
        ordinary space (ASCII 32) not a null character.
        alter type still chokes on the trailing space.

        The work around now is to create a new variable and not worry
        about alter type.
        numeric zip2(n5).
        compute zip2= number(substr(zip,1,5),n5).


      Art Kendall
Social Research Consultants
      On 10/9/2012 11:42 AM, Jon K Peck wrote:

    rtrim might not work,
        because you might
        have regular spaces after the nbsp.

      But this worked on my test.

      compute nbsp = replace(nbsp,
        string(160,
        pib1), "").

      alter type nbsp(f8.0).



        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM


     
     

     
        peck@.ibm

     
     

     
       
        new phone: [phone]





      From: &nbsp; &nbsp; &nbsp;
        &nbsp;Art Kendall
        &lt;

     
     

     
        Art@

     
     

     
        &gt;

      To: &nbsp; &nbsp; &nbsp;
        &nbsp;Jon K
        Peck/Chicago/IBM@IBMUS

      Cc: &nbsp; &nbsp; &nbsp;
        &nbsp;

     
     

     
        SPSSX-L@.uga

     
     

     
       

      Date: &nbsp; &nbsp; &nbsp;
        &nbsp;10/09/2012 09:21 AM

      Subject: &nbsp; &nbsp;
        &nbsp; &nbsp;Re: [SPSSX-L]
        have unbreakable space in Excel field treated as regular space





      Thanks for getting back to me.

        I tried

        compute zip =rtrim(zip,string(160, pib1)).
        compute zip = replace(zip, string(160, pib1),' ').

        and neither worked.


        Does anyone on the list know a simple way in excel to change the
        type,
        and what type= should be?


      Art Kendall
          Social Research Consultants

      On 10/9/2012 10:45 AM, Jon K Peck wrote:

      Excel considers a cell containing
        a
        NBSP character to have type=2, i.e., text, so SPSS does, too.
        &nbsp;You
        could replace those with "" before using ALTER TYPE or fix the
        spreadsheet before importing, but SPSS has to go by the Excel
        type code.


        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM


     
     

     
        peck@.ibm

     
     

     
       
        new phone: [phone]




        From: &nbsp; &nbsp; &nbsp; &nbsp;Art
        Kendall &lt;

     
     

     
        Art@

     
     

     
        &gt;

        To: &nbsp; &nbsp; &nbsp; &nbsp;

     
     

     
        SPSSX-L@.uga

     
     

     
       

        Date: &nbsp; &nbsp; &nbsp; &nbsp;10/09/2012
        08:33 AM
        Subject: &nbsp; &nbsp; &nbsp; &nbsp;[SPSSX-L]
have
        unbreakable space in Excel field treated as &nbsp; &nbsp; &nbsp;
&nbsp;
        &nbsp; &nbsp; &nbsp;regular space
        Sent by: &nbsp; &nbsp; &nbsp; &nbsp;"SPSSX(r)
Discussion"
      &lt;

     
     

     
        SPSSX-L@.uga

     
     

     
        &gt;





          I downloaded an excel file from the address check page at

        http://www.melissadata.com/lookups/batchaddresscheck.asp 

          fields like zip have a trailing unbreakable space (ASCII 160).
          If I read in the excel file in those fields come through as
          string
          variables.
          alter type zip (n5).
          results in system missing data.
          A work around is to
          compute zip= substr(zip1,5)

          However, is there a way to have the unbreakable space
          automatically
          ignored or changed when reading in an excel file

          --
          Art Kendall
          Social Research Consultants

          =====================
          To manage your subscription to SPSSX-L, send a message to


     
     

     
        LISTSERV@.UGA

     
     

     
       
          (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

     
     

     
        LISTSERV@.UGA

     
     

     
         (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

     
     




-----
--
Bruce Weaver
[hidden email] 
http://sites.google.com/a/lakeheadu.ca/bweaver/ 

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715552.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]

yes that is A5.  Chopping off is exactly what I wanted though.
The original problem was that when I brought a ZIP (5-digit US postal code) from excel it became a string with 7 characters.  Rtrim did not work because one of the character was an unbreakable space (ascii 160).
When a string variable has trailing spaces of either kind Alter type cannot convert the variable directly to a number.

The site I used has online mailing address checking. It returns corrected mailing address as an excel file. However, all fields have an unbreakable space at the right side.  When brought into SPSS an ordinary is also appended to each string.

It seems to be   a widespread problem for excel users whenever a spreadsheet is produced by an online application.


On 10/9/2012 6:07 PM, Bruce Weaver wrote:

Art, I think that that first ALTER TYPE was meant to have "(A5)" rather than
just "(5)", right?  The FM indicates that you need an output format in the
brackets, and 5 is not an output format.  If I try "ALTER TYPE stringvar
(5)" on a string variable of length 8, I get this warning:

Text: (End of Command) Command: alter type
The format specified is not recognized.
Execution of this command stops.

But if I try "ALTER TYPE stringvar (A5)", there is no warning, and
everything beyond 5 characters (from the left) gets chopped off.

Bruce



Art Kendall wrote

good catch.  However,
        when I tried it with double quote there was no space between the
        quotes, but it turns out that there was an extra regular space.

        When I cut_and_paste to syntax from excel there is one extra
        character an unbreakable space.
        When I read in the spread sheet it makes the variable A7 with a
        unbreakable space and a regular space at the end!

        However, the work around that does work without creating an
        extra variable is to alter type to the correct length and then
        alter type to n5.
        alter type zip (5).
        alter type zip (n5).

        Whew all this time over an oddball character from excel!

      Art Kendall
Social Research Consultants
      On 10/9/2012 2:30 PM, Bruce Weaver wrote:


      Hi Art.  In an earlier post in this thread, you listed syntax that
WAS
replacing ASCII 160 with a space.  I.e., you had:

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' '). /* <-- NOTE the ' '
.

Are you sure you weren't running this version of the REPLACE?

HTH.
Bruce



Art Kendall wrote


        Thanks for your help.

        It turns out that the problem was that
        compute zip = replace(zip, string(160, pib1), "").
        changes the trailing unbreakable space (ASCII 160) to an
        ordinary space (ASCII 32) not a null character.
        alter type still chokes on the trailing space.

        The work around now is to create a new variable and not worry
        about alter type.
        numeric zip2(n5).
        compute zip2= number(substr(zip,1,5),n5).


      Art Kendall
Social Research Consultants
      On 10/9/2012 11:42 AM, Jon K Peck wrote:

    rtrim might not work,
        because you might
        have regular spaces after the nbsp.

      But this worked on my test.

      compute nbsp = replace(nbsp,
        string(160,
        pib1), "").

      alter type nbsp(f8.0).



        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM






        [hidden email]






        new phone: [phone]





      From:      
         Art Kendall
        <





        Art@





        >

      To:      
         Jon K
        Peck/Chicago/IBM@IBMUS

      Cc:      
         





        [hidden email]







      Date:      
         10/09/2012 09:21 AM

      Subject:    
           Re: [SPSSX-L]
        have unbreakable space in Excel field treated as regular space





      Thanks for getting back to me.

        I tried

        compute zip =rtrim(zip,string(160, pib1)).
        compute zip = replace(zip, string(160, pib1),' ').

        and neither worked.


        Does anyone on the list know a simple way in excel to change the
        type,
        and what type= should be?


      Art Kendall
          Social Research Consultants

      On 10/9/2012 10:45 AM, Jon K Peck wrote:

      Excel considers a cell containing
        a
        NBSP character to have type=2, i.e., text, so SPSS does, too.
         You
        could replace those with "" before using ALTER TYPE or fix the
        spreadsheet before importing, but SPSS has to go by the Excel
        type code.


        Jon Peck (no "h") aka Kim
        Senior Software Engineer, IBM






        [hidden email]






        new phone: [phone]




        From:        Art
        Kendall <





        Art@





        >

        To:        





        [hidden email]







        Date:        10/09/2012
        08:33 AM
        Subject:        [SPSSX-L]
have
        unbreakable space in Excel field treated as    
 
 
             regular space
        Sent by:        "SPSSX(r)
Discussion"
      <





        [hidden email]





        >





          I downloaded an excel file from the address check page at

        http://www.melissadata.com/lookups/batchaddresscheck.asp

          fields like zip have a trailing unbreakable space (ASCII 160).
          If I read in the excel file in those fields come through as
          string
          variables.
          alter type zip (n5).
          results in system missing data.
          A work around is to
          compute zip= substr(zip1,5)

          However, is there a way to have the unbreakable space
          automatically
          ignored or changed when reading in an excel file

          --
          Art Kendall
          Social Research Consultants

          =====================
          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







-----
--
Bruce Weaver

bweaver@

http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715552.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

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715557.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

[Jon K Peck]

ALTER TYPE does convert strings with trailing spaces to numbers fine.  The syntax I posted did exactly that.  NBSP is another matter, since it is a nonnumeric character.

If you want to send me a sample problematic spreadsheet, I'll figure out exactly what is in those strings.

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




From:        Art Kendall <[hidden email]>
To:        [hidden email]
Date:        10/09/2012 05:12 PM
Subject:        Re: [SPSSX-L] have unbreakable space in Excel field treated as              regular space
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

yes that is A5.  Chopping off is exactly what I wanted though.
The original problem was that when I brought a ZIP (5-digit US postal code) from excel it became a string with 7 characters.  Rtrim did not work because one of the character was an unbreakable space (ascii 160).
When a string variable has trailing spaces of either kind Alter type cannot convert the variable directly to a number.

The site I used has online mailing address checking. It returns corrected mailing address as an excel file. However, all fields have an unbreakable space at the right side.  When brought into SPSS an ordinary is also appended to each string.

It seems to be   a widespread problem for excel users whenever a spreadsheet is produced by an online application.


Art Kendall
Social Research Consultants
On 10/9/2012 6:07 PM, Bruce Weaver wrote:
Art, I think that that first ALTER TYPE was meant to have "(A5)" rather than
just "(5)", right?  The FM indicates that you need an output format in the
brackets, and 5 is not an output format.  If I try "ALTER TYPE stringvar
(5)" on a string variable of length 8, I get this warning:

Text: (End of Command) Command: alter type
The format specified is not recognized.
Execution of this command stops.

But if I try "ALTER TYPE stringvar (A5)", there is no warning, and
everything beyond 5 characters (from the left) gets chopped off.

Bruce



Art Kendall wrote

good catch.&nbsp; However,
       when I tried it with double quote there was no space between the
       quotes, but it turns out that there was an extra regular space.

       When I cut_and_paste to syntax from excel there is one extra
       character an unbreakable space.
       When I read in the spread sheet it makes the variable A7 with a
       unbreakable space and a regular space at the end!

       However, the work around that does work without creating an
       extra variable is to alter type to the correct length and then
       alter type to n5.
       alter type zip (5).
       alter type zip (n5).

       Whew all this time over an oddball character from excel!

     Art Kendall
Social Research Consultants
     On 10/9/2012 2:30 PM, Bruce Weaver wrote:


     Hi Art.  In an earlier post in this thread, you listed syntax that
WAS
replacing ASCII 160 with a space.  I.e., you had:

compute zip =rtrim(zip,string(160, pib1)).
compute zip = replace(zip, string(160, pib1),' '). /* &lt;-- NOTE the ' '
.

Are you sure you weren't running this version of the REPLACE?

HTH.
Bruce



Art Kendall wrote


       Thanks for your help.

       It turns out that the problem was that
       compute zip = replace(zip, string(160, pib1), "").
       changes the trailing unbreakable space (ASCII 160) to an
       ordinary space (ASCII 32) not a null character.
       alter type still chokes on the trailing space.

       The work around now is to create a new variable and not worry
       about alter type.
       numeric zip2(n5).
       compute zip2= number(substr(zip,1,5),n5).


     Art Kendall
Social Research Consultants
     On 10/9/2012 11:42 AM, Jon K Peck wrote:

   rtrim might not work,
       because you might
       have regular spaces after the nbsp.

     But this worked on my test.

     compute nbsp = replace(nbsp,
       string(160,
       pib1), "").

     alter type nbsp(f8.0).



       Jon Peck (no "h") aka Kim
       Senior Software Engineer, IBM






       peck@.ibm






       new phone: [phone]





     From: &amp;nbsp; &amp;nbsp; &amp;nbsp;
       &amp;nbsp;Art Kendall
       &amp;lt;





       Art@





       &amp;gt;

     To: &amp;nbsp; &amp;nbsp; &amp;nbsp;
       &amp;nbsp;Jon K
       Peck/Chicago/IBM@IBMUS

     Cc: &amp;nbsp; &amp;nbsp; &amp;nbsp;
       &amp;nbsp;





       [hidden email]







     Date: &amp;nbsp; &amp;nbsp; &amp;nbsp;
       &amp;nbsp;10/09/2012 09:21 AM

     Subject: &amp;nbsp; &amp;nbsp;
       &amp;nbsp; &amp;nbsp;Re: [SPSSX-L]
       have unbreakable space in Excel field treated as regular space





     Thanks for getting back to me.

       I tried

       compute zip =rtrim(zip,string(160, pib1)).
       compute zip = replace(zip, string(160, pib1),' ').

       and neither worked.


       Does anyone on the list know a simple way in excel to change the
       type,
       and what type= should be?


     Art Kendall
         Social Research Consultants

     On 10/9/2012 10:45 AM, Jon K Peck wrote:

     Excel considers a cell containing
       a
       NBSP character to have type=2, i.e., text, so SPSS does, too.
       &amp;nbsp;You
       could replace those with "" before using ALTER TYPE or fix the
       spreadsheet before importing, but SPSS has to go by the Excel
       type code.


       Jon Peck (no "h") aka Kim
       Senior Software Engineer, IBM






       peck@.ibm






       new phone: [phone]




       From: &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;Art
       Kendall &amp;lt;





       Art@





       &amp;gt;

       To: &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;





       [hidden email]







       Date: &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;10/09/2012
       08:33 AM
       Subject: &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;[SPSSX-L]
have
       unbreakable space in Excel field treated as &amp;nbsp; &amp;nbsp;
&amp;nbsp;
&amp;nbsp;
       &amp;nbsp; &amp;nbsp; &amp;nbsp;regular space
       Sent by: &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp;"SPSSX(r)
Discussion"
     &amp;lt;





       [hidden email]





       &amp;gt;





         I downloaded an excel file from the address check page at

       http://www.melissadata.com/lookups/batchaddresscheck.asp

         fields like zip have a trailing unbreakable space (ASCII 160).
         If I read in the excel file in those fields come through as
         string
         variables.
         alter type zip (n5).
         results in system missing data.
         A work around is to
         compute zip= substr(zip1,5)

         However, is there a way to have the unbreakable space
         automatically
         ignored or changed when reading in an excel file

         --
         Art Kendall
         Social Research Consultants

         =====================
         To manage your subscription to SPSSX-L, send a message to






       LISTSERV@.UGA






         (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





       LISTSERV@.UGA





        (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







-----
--
Bruce Weaver



bweaver@




http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715552.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to



LISTSERV@.UGA



  (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



LISTSERV@.UGA



 (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






-----
--
Bruce Weaver
bweaver@...
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/have-unbreakable-space-in-Excel-field-treated-as-regular-space-tp5715542p5715557.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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