numeric variables read in as String from Excel

[Paul Mcgeoghan]

Hi,

I have an Excel 2003 file which has appears to be numeric data and even
formatting the columns as numeric doesn't solve it. 1st row has column
headings, 2nd row has numeric data.

However, SPSS 1602 reads in all variables as string.

Other than manually going into variable view and changing all the variables
to numeric, is there any other option using syntax possibly?

Automatic Recode reorders and changes all the numeric values so that isn't
an option.

Thanks,
Paul

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

[Hector Maletta]

The adequate command is AUTORECODE stringvarlist (CONVERT) INTO numvarlist.
Hector

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Paul McGeoghan
Sent: 30 March 2009 08:42
To: [hidden email]
Subject: numeric variables read in as String from Excel

Hi,

I have an Excel 2003 file which has appears to be numeric data and even
formatting the columns as numeric doesn't solve it. 1st row has column
headings, 2nd row has numeric data.

However, SPSS 1602 reads in all variables as string.

Other than manually going into variable view and changing all the variables
to numeric, is there any other option using syntax possibly?

Automatic Recode reorders and changes all the numeric values so that isn't
an option.

Thanks,
Paul

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

[thara vardhan-2]

Hi Paul

Please check your data columns in the Excel file. There may be some unknown values which are actually coded as 'unknown'. Also if the cell reference in Excel is custom or something else other than a numeric than this problem will occur.

I faced a similar situation sometime back with my date variables. Once I removed the 'unknown' and made it a system missing in Excel, SPSS read all the excel files as numeric rather than a string variable.

Hope this helps.

regards
Thara Vardhan
Senior Statistician
Performance Improvement & Planning
NSW Police
Tel: [phone]

Paul McGeoghan <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>

30/03/2009 22:41

Please respond to
Paul McGeoghan <[hidden email]>

To	[hidden email]
cc
Subject	numeric variables read in as String from Excel

Hi,

I have an Excel 2003 file which has appears to be numeric data and even
formatting the columns as numeric doesn't solve it. 1st row has column
headings, 2nd row has numeric data.

However, SPSS 1602 reads in all variables as string.

Other than manually going into variable view and changing all the variables
to numeric, is there any other option using syntax possibly?

Automatic Recode reorders and changes all the numeric values so that isn't
an option.

Thanks,
Paul

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

All mail is subject to content scanning for possible violation of NSW Police Force
Electronic Messaging Policy. All NSW Police Force employees are required to
familiarise themselves with the content of the policy, found under Policies on the
NSW Police Force Intranet.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

The information contained in this email is intended for the named recipient(s) 
only. It may contain private, confidential, copyright or legally privileged 
information.  If you are not the intended recipient or you have received this 
email by mistake, please reply to the author and delete this email immediately. 
You must not copy, print, forward or distribute this email, nor place reliance 
on its contents. This email and any attachment have been virus scanned. However, 
you are requested to conduct a virus scan as well.  No liability is accepted 
for any loss or damage resulting from a computer virus, or resulting from a delay
or defect in transmission of this email or any attached file. This email does not 
constitute a representation by the NSW Police Force unless the author is legally 
entitled to do so.

[Paul Mcgeoghan]

Thanks for replies.

I got around the problem via inserting a dummy row into the 2nd row and
then entering numeric values in the relevant cells.
Then used File Open Database New Query and choose Excel Files.

(File Open Data Files of Type Excel still changed them all into string).

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