Difficulties with GET DATA to read multisheet EXCEL workbook

[Ruben Geert van den Berg]

Dear all,
 
I've been converting a multisheet EXCEL workbook to a .sav file with GET DATA. However, I'm having a couple of difficulties:
 
1) A date variable in EXCEL shows up as numeric in SPSS and the SPSS values don't look familiar at all. No warning is issued. The EXCEL format was dd-mm-yy. If I want the data to be 'understood' by SPSS, should I use a different format in EXCEL?
 
2) In one sheet, SPSS reads 8 columns of data while there's only 4 columns in the EXCEL sheet. Columns 5-8 look perfectly empty to me. SPSS issues 4 warnings for the empty columns (warning 2101). Other sheets in the same workbook with 4 columns are read properly without warnings. Why does SPSS 'think' it should read 8 columns instead of 4?
 
3) In one instance, I received warning 2109: Encountered a value incompatible with Spss Percent type. (Row 11, Column 8). In EXCEL this cell is empty. (I should take the row and column very literally, right? I mean, if there's fieldnames, then Row 11 is still row 11 (and not 12) in EXCEL, or isn't it?). Other empty cells yield no warnings so why does this one?
 
I'm sorry for posting so many detailed questions to the list but if anyone could shed any light on any of these questions, it would be highly appreciated!
 
Kind regards,
 
Ruben van den Berg
 
P.s. I worked with EXCEL 2003 and SPSS V14 today.


 

---

Express yourself instantly with MSN Messenger! MSN Messenger

[Lemon, John S.]

Ruben

 

I can possibly answer some of your queries:

 

1.       Excel has a number of date formats and I have found that only two of them convert easily into SPSS – they are the top two in the list of date formats and they are marked with an *

2.       Although the cells / columns look empty there may be one cell in column 8 that has something in, even a space, and that is causing the problem and this may also be the cause of your third problem

 

Best Wishes

 

John S. Lemon

DIT ( Directorate of Information Technology ) - Student Liaison Officer

University of Aberdeen

Edward Wright Building: Room G51

 

Tel:  [phone]

Fax: [phone]

 

Diary ( Free / Busy )

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ruben van den Berg
Sent: 03 November 2009 17:13
To: [hidden email]
Subject: Difficulties with GET DATA to read multisheet EXCEL workbook

 

Dear all,
 
I've been converting a multisheet EXCEL workbook to a .sav file with GET DATA. However, I'm having a couple of difficulties:
 
1) A date variable in EXCEL shows up as numeric in SPSS and the SPSS values don't look familiar at all. No warning is issued. The EXCEL format was dd-mm-yy. If I want the data to be 'understood' by SPSS, should I use a different format in EXCEL?
 
2) In one sheet, SPSS reads 8 columns of data while there's only 4 columns in the EXCEL sheet. Columns 5-8 look perfectly empty to me. SPSS issues 4 warnings for the empty columns (warning 2101). Other sheets in the same workbook with 4 columns are read properly without warnings. Why does SPSS 'think' it should read 8 columns instead of 4?
 
3) In one instance, I received warning 2109: Encountered a value incompatible with Spss Percent type. (Row 11, Column 8). In EXCEL this cell is empty. (I should take the row and column very literally, right? I mean, if there's fieldnames, then Row 11 is still row 11 (and not 12) in EXCEL, or isn't it?). Other empty cells yield no warnings so why does this one?
 
I'm sorry for posting so many detailed questions to the list but if anyone could shed any light on any of these questions, it would be highly appreciated!
 
Kind regards,
 
Ruben van den Berg
 
P.s. I worked with EXCEL 2003 and SPSS V14 today.


 

---

Express yourself instantly with MSN Messenger! MSN Messenger

The University of Aberdeen is a charity registered in Scotland, No SC013683.

[Ruben Geert van den Berg]

Dear John,
 
Thanks for your brilliant advice! The date issue has been perfectly solved and warning 2101 as well. I removed the columns that looked empty (and should be empty) with CTRL + -, which solved this problem. Reading EXCEL workbooks into SPSS is now going virtually perfectly, with just one exception:
 
Percentages in EXCEL are sometimes correctly read as percentages and sometimes not. For instance, in sheet 1 I've 0.5 (displayed as 50%). In sheet 2 I've 0.3 (displayed as 30%). If I read both sheets, the percentage variable in sheet 1 get the format PCT11.1. However, the percentage variable in sheet 2 gets the format F11.1. After merging the datasets (ADD FILES, first sheet 1, then sheet 2), these values are 50% and 0.3% (instead of 30%). I guess this is because the dictionary information from the first dataset overrules the later datasets.
 
Anyway, do you (or anybody else) have a clue why one percentage is correctly assigned PCT11.1 and the other (incorrectly) F11.1? I mean, disregarding the number of decimals, there is only one percentage format in EXCEL, right?
 
Kind regards,
 
Ruben van den Berg



 



 

---

Date: Tue, 3 Nov 2009 19:41:56 +0000
From: [hidden email]
Subject: Re: Difficulties with GET DATA to read multisheet EXCEL workbook
To: [hidden email]

Ruben

 

I can possibly answer some of your queries:

 

1.       Excel has a number of date formats and I have found that only two of them convert easily into SPSS – they are the top two in the list of date formats and they are marked with an *

2.       Although the cells / columns look empty there may be one cell in column 8 that has something in, even a space, and that is causing the problem and this may also be the cause of your third problem

 

Best Wishes

 

John S. Lemon

DIT ( Directorate of Information Technology ) - Student Liaison Officer

University of Aberdeen

Edward Wright Building: Room G51

 

Tel:  [phone]

Fax: [phone]

 

Diary ( Free / Busy )

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ruben van den Berg
Sent: 03 November 2009 17:13
To: [hidden email]
Subject: Difficulties with GET DATA to read multisheet EXCEL workbook

 

Dear all,
 
I've been converting a multisheet EXCEL workbook to a .sav file with GET DATA. However, I'm having a couple of difficulties:
 
1) A date variable in EXCEL shows up as numeric in SPSS and the SPSS values don't look familiar at all. No warning is issued. The EXCEL format was dd-mm-yy. If I want the data to be 'understood' by SPSS, should I use a different format in EXCEL?
 
2) In one sheet, SPSS reads 8 columns of data while there's only 4 columns in the EXCEL sheet. Columns 5-8 look perfectly empty to me. SPSS issues 4 warnings for the empty columns (warning 2101). Other sheets in the same workbook with 4 columns are read properly without warnings. Why does SPSS 'think' it should read 8 columns instead of 4?
 
3) In one instance, I received warning 2109: Encountered a value incompatible with Spss Percent type. (Row 11, Column 8). In EXCEL this cell is empty. (I should take the row and column very literally, right? I mean, if there's fieldnames, then Row 11 is still row 11 (and not 12) in EXCEL, or isn't it?). Other empty cells yield no warnings so why does this one?
 
I'm sorry for posting so many detailed questions to the list but if anyone could shed any light on any of these questions, it would be highly appreciated!
 
Kind regards,
 
Ruben van den Berg
 
P.s. I worked with EXCEL 2003 and SPSS V14 today.


 

---

Express yourself instantly with MSN Messenger! MSN Messenger

The University of Aberdeen is a charity registered in Scotland, No SC013683.

---

New Windows 7: Find the right PC for you. Learn more.

[Ruben Geert van den Berg]

Indeed, I selected the entire % columns and respecified the format as percentages (even though all values already looked like normal percentages) and this solved the last little bit of trouble.
 
Thanks a lot!
 
Ruben



 



 

---

To: [hidden email]
Subject: Re: [SPSSX-L] Difficulties with GET DATA to read multisheet EXCEL workbook
From: [hidden email]
Date: Wed, 4 Nov 2009 06:52:14 -0700


My guess is that there is a value somewhere in the aberrant column that doesn't actually have a percentage format.

Regards,

Jon Peck
SPSS, an IBM Company
[hidden email]
312-651-3435

From:	Ruben van den Berg <[hidden email]>
To:	[hidden email]
Date:	11/04/2009 01:49 AM
Subject:	Re: [SPSSX-L] Difficulties with GET DATA to read multisheet EXCEL              workbook
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

Dear John,

Thanks for your brilliant advice! The date issue has been perfectly solved and warning 2101 as well. I removed the columns that looked empty (and should be empty) with CTRL + -, which solved this problem. Reading EXCEL workbooks into SPSS is now going virtually perfectly, with just one exception:

Percentages in EXCEL are sometimes correctly read as percentages and sometimes not. For instance, in sheet 1 I've 0.5 (displayed as 50%). In sheet 2 I've 0.3 (displayed as 30%). If I read both sheets, the percentage variable in sheet 1 get the format PCT11.1. However, the percentage variable in sheet 2 gets the format F11.1. After merging the datasets (ADD FILES, first sheet 1, then sheet 2), these values are 50% and 0.3% (instead of 30%). I guess this is because the dictionary information from the first dataset overrules the later datasets.

Anyway, do you (or anybody else) have a clue why one percentage is correctly assigned PCT11.1 and the other (incorrectly) F11.1? I mean, disregarding the number of decimals, there is only one percentage format in EXCEL, right?

Kind regards,

Ruben van den Berg

---

Date: Tue, 3 Nov 2009 19:41:56 +0000
From: [hidden email]
Subject: Re: Difficulties with GET DATA to read multisheet EXCEL workbook
To: [hidden email]

Ruben
 
I can possibly answer some of your queries:
 
1.       Excel has a number of date formats and I have found that only two of them convert easily into SPSS – they are the top two in the list of date formats and they are marked with an *
2.       Although the cells / columns look empty there may be one cell in column 8 that has something in, even a space, and that is causing the problem and this may also be the cause of your third problem
 
Best Wishes
 
John S. Lemon
DIT ( Directorate of Information Technology ) - Student Liaison Officer
University of Aberdeen
Edward Wright Building: Room G51
 
Tel:  [phone]
Fax: [phone]
 
Diary ( Free / Busy )
 
From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Ruben van den Berg
Sent: 03 November 2009 17:13
To: [hidden email]
Subject: Difficulties with GET DATA to read multisheet EXCEL workbook
 
Dear all,

I've been converting a multisheet EXCEL workbook to a .sav file with GET DATA. However, I'm having a couple of difficulties:

1) A date variable in EXCEL shows up as numeric in SPSS and the SPSS values don't look familiar at all. No warning is issued. The EXCEL format was dd-mm-yy. If I want the data to be 'understood' by SPSS, should I use a different format in EXCEL?

2) In one sheet, SPSS reads 8 columns of data while there's only 4 columns in the EXCEL sheet. Columns 5-8 look perfectly empty to me. SPSS issues 4 warnings for the empty columns (warning 2101). Other sheets in the same workbook with 4 columns are read properly without warnings. Why does SPSS 'think' it should read 8 columns instead of 4?

3) In one instance, I received warning 2109: Encountered a value incompatible with Spss Percent type. (Row 11, Column 8). In EXCEL this cell is empty. (I should take the row and column very literally, right? I mean, if there's fieldnames, then Row 11 is still row 11 (and not 12) in EXCEL, or isn't it?). Other empty cells yield no warnings so why does this one?

I'm sorry for posting so many detailed questions to the list but if anyone could shed any light on any of these questions, it would be highly appreciated!

Kind regards,

Ruben van den Berg

P.s. I worked with EXCEL 2003 and SPSS V14 today.

---

Express yourself instantly with MSN Messenger! MSN Messenger

The University of Aberdeen is a charity registered in Scotland, No SC013683.

---

New Windows 7: Find the right PC for you. Learn more.

---

New Windows 7: Find the right PC for you. Learn more.

[Russel Wildeman]

Dear List,

 

I have a large data set that splits the data into three components, namely learner, parents and educators. The linking variable is school ID. This is multilevel data but one of the files lists the School ID slightly differently.

 

In the learner file, School ID would be 1 1 1 1 1 etc. depending on the number of learners whereas in the principal file (because only one principal selected per school), it would read 1 2 3 4 5 etc. I have calculated a simple index from the Principal’s file and want to insert this variable into the larger learner file ensuring that each school’s score on this index variable is matched to its proper School ID. There are about 1000 schools.

 

Any help because I can’t do a straight Merge Files command

 

Many thanks,

Russell

 

[Simon Phillip Freidin]

This looks like a one-to-many match (one principal matches to many learners). Use match files with the principal's file specified as a table:

match files file='learners.sav'/table='principal.sav'/by schoolid.

In the principal file each schoolid can only appear once.

------------

In the learner file, School ID would be 1 1 1 1 1 etc. depending on the
number of learners whereas in the principal file (because only one principal
selected per school), it would read 1 2 3 4 5 etc. I have calculated a
simple index from the Principal's file and want to insert this variable into
the larger learner file ensuring that each school's score on this index
variable is matched to its proper School ID. There are about 1000 schools.

____________________________________

Amazing ... this handheld mobile virtual world