excel (xls) open database problem
Locked
excel (xls) open database problem
 
|
Can someone give me some insight into this problem. I am attempting to read in an excel (.xls) file and after having browsed and highlighted and opened the file so that the name appears in the ODBC driver login box and then clicked OK,
I get a box (labeled “ODBC”) saying SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] External table is not in the expected format. This problem file is one of a set of files that all have the same structure and those other files open ok.
Thanks, Gene Maguin |
Re: excel (xls) open database problem
|
|
First, why are you trying to use ODBC to
read an Excel file? Why not use File>Open>Data and select Excel from
the list of file types? (Or GET DATA /TYPE=XLS in syntax).
Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Maguin, Eugene" <[hidden email]> To: [hidden email] Date: 10/05/2015 02:03 PM Subject: excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> Can someone give me some insight into this problem. I am attempting to read in an excel (.xls) file and after having browsed and highlighted and opened the file so that the name appears in the ODBC driver login box and then clicked OK, I get a box (labeled “ODBC”) saying SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] External table is not in the expected format. This problem file is one of a set of files that all have the same structure and those other files open ok. Thanks, Gene Maguin ===================== 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
|
Re: excel (xls) open database problem
|
|
Rick, thanks for your really quick reply.
I did File>Open>Data and select Excel from the list of file types and selected the file name. The return message was (2052) Error accessing the Excel file. The file may be open by another application. If so, close it and retry. * File: "U:\Gretchen\xxxxxx\yyyyyy 2014R1.xls" Excel is closed and I shut down spss and restarted spss and tried again and got the same message. So far as I know there is nothing else accessing that file.
I also restarted the machine and tried again and got the same message. >>Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of
Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. This is a 32 bit machine. I was able to open the source file with excel, edit a line and resave to a new (the current) name. So I assume the source file could
not have been a 64 bit file. This file is one of a set of files and all the others read in ok.
Let me ask this. There is a text field in all the files that is long. I set a 1000 character field length in the OBDC. If that field were, say, 5000 characters
for some records would that cause a/the problem? Why ODBC? I frankly don’t know when ODBC is required for an excel file and when it is not required.
Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Rick Oliver First, why are you trying to use ODBC to read an Excel file? Why not use File>Open>Data and select Excel from the list of file types? (Or GET DATA /TYPE=XLS in syntax).
===================== 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
|
Re: excel (xls) open database problem
|
|
If you are using File>Open>Data and
selecting Excel from the list, I don't think ODBC should ever enter the
issue in any way. Statistics has a native method for reading Excel files
directly. It's possible to read Excel files as database tables using the
Database Wizard (or GET DATA /TYPE=ODBC), and it might be useful if you
wanted to use ODBC filtering and merging capabilities, if you are just
opening a single sheet in a single Excel file, that is not necessary.
Do you know what version the Excel file is? The dialog UI should be smart about detecting this, but if it isn't behaving correctly, the syntax for any Excel file prior to release 5 should be GET TRANSLATE /TYPE=XLS. For anything after Excel 5, (Excel 97 or later), the syntax should be GET DATA /TYPE=XLS. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Maguin, Eugene" <[hidden email]> To: [hidden email] Date: 10/05/2015 02:43 PM Subject: Re: excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> Rick, thanks for your really quick reply. I did File>Open>Data and select Excel from the list of file types and selected the file name. The return message was (2052) Error accessing the Excel file. The file may be open by another application. If so, close it and retry. * File: "U:\Gretchen\xxxxxx\yyyyyy 2014R1.xls" Excel is closed and I shut down spss and restarted spss and tried again and got the same message. So far as I know there is nothing else accessing that file. I also restarted the machine and tried again and got the same message. >>Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. This is a 32 bit machine. I was able to open the source file with excel, edit a line and resave to a new (the current) name. So I assume the source file could not have been a 64 bit file. This file is one of a set of files and all the others read in ok. Let me ask this. There is a text field in all the files that is long. I set a 1000 character field length in the OBDC. If that field were, say, 5000 characters for some records would that cause a/the problem? Why ODBC? I frankly don’t know when ODBC is required for an excel file and when it is not required. Gene Maguin From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Rick Oliver Sent: Monday, October 05, 2015 3:16 PM To: [hidden email] Subject: Re: excel (xls) open database problem First, why are you trying to use ODBC to read an Excel file? Why not use File>Open>Data and select Excel from the list of file types? (Or GET DATA /TYPE=XLS in syntax). Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: oliverr@... From: "Maguin, Eugene" <emaguin@...> To: [hidden email] Date: 10/05/2015 02:03 PM Subject: excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> Can someone give me some insight into this problem. I am attempting to read in an excel (.xls) file and after having browsed and highlighted and opened the file so that the name appears in the ODBC driver login box and then clicked OK, I get a box (labeled “ODBC”) saying SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] External table is not in the expected format. This problem file is one of a set of files that all have the same structure and those other files open ok. Thanks, Gene Maguin ===================== 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 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
|
Re: excel (xls) open database problem
|
|
It seems that the obvious and maybe even
correct inference is that U is a shared drive, and someone else has the
file open in Excel, which would deny access to Statistics.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: Rick Oliver/Chicago/IBM@IBMUS To: [hidden email] Date: 10/05/2015 02:01 PM Subject: Re: [SPSSX-L] excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> If you are using File>Open>Data and selecting Excel from the list, I don't think ODBC should ever enter the issue in any way. Statistics has a native method for reading Excel files directly. It's possible to read Excel files as database tables using the Database Wizard (or GET DATA /TYPE=ODBC), and it might be useful if you wanted to use ODBC filtering and merging capabilities, if you are just opening a single sheet in a single Excel file, that is not necessary. Do you know what version the Excel file is? The dialog UI should be smart about detecting this, but if it isn't behaving correctly, the syntax for any Excel file prior to release 5 should be GET TRANSLATE /TYPE=XLS. For anything after Excel 5, (Excel 97 or later), the syntax should be GET DATA /TYPE=XLS. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Maguin, Eugene" <[hidden email]> To: [hidden email] Date: 10/05/2015 02:43 PM Subject: Re: excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> Rick, thanks for your really quick reply. I did File>Open>Data and select Excel from the list of file types and selected the file name. The return message was (2052) Error accessing the Excel file. The file may be open by another application. If so, close it and retry. * File: "U:\Gretchen\xxxxxx\yyyyyy 2014R1.xls" Excel is closed and I shut down spss and restarted spss and tried again and got the same message. So far as I know there is nothing else accessing that file. I also restarted the machine and tried again and got the same message. >>Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. This is a 32 bit machine. I was able to open the source file with excel, edit a line and resave to a new (the current) name. So I assume the source file could not have been a 64 bit file. This file is one of a set of files and all the others read in ok. Let me ask this. There is a text field in all the files that is long. I set a 1000 character field length in the OBDC. If that field were, say, 5000 characters for some records would that cause a/the problem? Why ODBC? I frankly don’t know when ODBC is required for an excel file and when it is not required. Gene Maguin From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Rick Oliver Sent: Monday, October 05, 2015 3:16 PM To: [hidden email] Subject: Re: excel (xls) open database problem First, why are you trying to use ODBC to read an Excel file? Why not use File>Open>Data and select Excel from the list of file types? (Or GET DATA /TYPE=XLS in syntax). Second, if you are using the 64-bit version of Statistics and the 32-bit version of Excel (or Office), you cannot use the 32-bit Excel ODBC driver with the 64-bit version of Statistics. I don't know of any workaround for this other than to either get the 64-bit version of Excel or switch to the 32-bit version of Statistics. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: oliverr@... From: "Maguin, Eugene" <emaguin@...> To: [hidden email] Date: 10/05/2015 02:03 PM Subject: excel (xls) open database problem Sent by: "SPSSX(r) Discussion" <[hidden email]> Can someone give me some insight into this problem. I am attempting to read in an excel (.xls) file and after having browsed and highlighted and opened the file so that the name appears in the ODBC driver login box and then clicked OK, I get a box (labeled “ODBC”) saying SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] External table is not in the expected format. This problem file is one of a set of files that all have the same structure and those other files open ok. Thanks, Gene Maguin ===================== 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 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
|
Re: excel (xls) open database problem
|
|
Jon, I understand your point. However, U drive is a private to me drive on the server. I also just copied the file to my C drive, tried to open it from there
and got the same message. Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Jon K Peck It seems that the obvious and maybe even correct inference is that U is a shared drive, and someone else has the file open in Excel, which would deny access to Statistics.
===================== 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
|
Re: excel (xls) open database problem
|
|
In reply to this post by Rick Oliver-3
The file type is 97-2003, which is what all the other files are as well. Would a corrupted file have caused this problem and be able to opened, edited and saved
to a new name? Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of Rick Oliver If you are using File>Open>Data and selecting Excel from the list, I don't think ODBC should ever enter the issue in any way. Statistics has a native method for reading Excel
files directly. It's possible to read Excel files as database tables using the Database Wizard (or GET DATA /TYPE=ODBC), and it might be useful if you wanted to use ODBC filtering and merging capabilities, if you are just opening a single sheet in a single
Excel file, that is not necessary.
===================== 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
|
Re: excel (xls) open database problem
Administrator
|
Gene, rather than save to a new name (i.e., Save-As, and change the name), I think I would copy the data, paste to a new workbook, and save that to the new name. (My fear is that Save-As with a name change doesn't get rid of any junk in the file that might be causing the problem.)
Alternatively, I might save it as .CSV. Interesting problem. Please keep us posted when you eventually crack it.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: excel (xls) open database problem
|
|
Here's the *&*)&*%*&) PROBLEM.
(or, at least, one of them). There are embedded carriage returns in the text string for one or more variables. Now that I exported the file into a tab separated text file and opened the file using multi-edit, I see that. I'd guess that the person who put this together copied text from a word processing program, which used a carriage return to separate paragraphs. Actually I'm surprised this showed up on only this file given the purpose of the specific field. In word, I can search and replace a limited number of format characters, like ^p for a carriage return. Is there any way to do the same in excel. Nothing is obvious but I 'never' use excel so I basically don't know anything about it. Thanks, Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: Monday, October 05, 2015 5:53 PM To: [hidden email] Subject: Re: excel (xls) open database problem Gene, rather than save to a new name (i.e., Save-As, and change the name), I think I would copy the data, paste to a new workbook, and save that to the new name. (My fear is that Save-As with a name change doesn't get rid of any junk in the file that might be causing the problem.) Alternatively, I might save it as .CSV. Interesting problem. Please keep us posted when you eventually crack it. Maguin, Eugene wrote > The file type is 97-2003, which is what all the other files are as well. > Would a corrupted file have caused this problem and be able to opened, > edited and saved to a new name? > Gene Maguin > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Rick Oliver > Sent: Monday, October 05, 2015 4:00 PM > To: > SPSSX-L@.UGA > Subject: Re: excel (xls) open database problem > > If you are using File>Open>Data and selecting Excel from the list, I > don't think ODBC should ever enter the issue in any way. Statistics > has a native method for reading Excel files directly. It's possible to > read Excel files as database tables using the Database Wizard (or GET > DATA /TYPE=ODBC), and it might be useful if you wanted to use ODBC > filtering and merging capabilities, if you are just opening a single > sheet in a single Excel file, that is not necessary. > > Do you know what version the Excel file is? The dialog UI should be > smart about detecting this, but if it isn't behaving correctly, the > syntax for any Excel file prior to release 5 should be GET TRANSLATE > /TYPE=XLS. For anything after Excel 5, (Excel 97 or later), the syntax > should be GET DATA /TYPE=XLS. > > > Rick Oliver > Senior Information Developer > IBM Business Analytics (SPSS) > E-mail: > oliverr@.ibm > <mailto: > oliverr@.ibm > > > > > > From: "Maguin, Eugene" < > emaguin@ > <mailto: > emaguin@ > >> > To: > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > > > Date: 10/05/2015 02:43 PM > Subject: Re: excel (xls) open database problem > Sent by: "SPSSX(r) Discussion" < > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > >> > ________________________________ > > > > Rick, thanks for your really quick reply. > > I did File>Open>Data and select Excel from the list of file types and > selected the file name. The return message was > > (2052) Error accessing the Excel file. The file may be open by > another application. If so, close it and retry. > * File: "U:\Gretchen\xxxxxx\yyyyyy 2014R1.xls" > > Excel is closed and I shut down spss and restarted spss and tried > again and got the same message. So far as I know there is nothing > else accessing that file. I also restarted the machine and tried again > and got the same message. > >>>Second, if you are using the 64-bit version of Statistics and the >>>32-bit > > This is a 32 bit machine. I was able to open the source file with > excel, edit a line and resave to a new (the current) name. So I assume > the source file could not have been a 64 bit file. This file is one of > a set of files and all the others read in ok. > > Let me ask this. There is a text field in all the files that is long. > I set a 1000 character field length in the OBDC. If that field were, > say, > 5000 characters for some records would that cause a/the problem? > > Why ODBC? I frankly don’t know when ODBC is required for an excel file > and when it is not required. > > Gene Maguin > > > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Rick Oliver > Sent: Monday, October 05, 2015 3:16 PM > To: > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > > > Subject: Re: excel (xls) open database problem > > First, why are you trying to use ODBC to read an Excel file? Why not > use > File>Open>Data and select Excel from the list of file types? (Or GET > File>Open>DATA > /TYPE=XLS in syntax). > > Second, if you are using the 64-bit version of Statistics and the > 32-bit version of Excel (or Office), you cannot use the 32-bit Excel > ODBC driver with the 64-bit version of Statistics. I don't know of any > workaround for this other than to either get the 64-bit version of > Excel or switch to the 32-bit version of Statistics. > > Rick Oliver > Senior Information Developer > IBM Business Analytics (SPSS) > E-mail: > oliverr@.ibm > <mailto: > oliverr@.ibm > > > > > > From: "Maguin, Eugene" < > emaguin@ > <mailto: > emaguin@ > >> > To: > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > > > Date: 10/05/2015 02:03 PM > Subject: excel (xls) open database problem > Sent by: "SPSSX(r) Discussion" < > SPSSX-L@.UGA > <mailto: > SPSSX-L@.UGA > >> > ________________________________ > > > > > Can someone give me some insight into this problem. I am attempting to > read in an excel (.xls) file and after having browsed and highlighted > and opened the file so that the name appears in the ODBC driver login > box and then clicked OK, I get a box (labeled “ODBC”) saying > SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] External table > is not in the expected format. > > This problem file is one of a set of files that all have the same > structure and those other files open ok. > > Thanks, Gene Maguin > > ===================== To manage your subscription to SPSSX-L, send a > message to > LISTSERV@.UGA > <mailto: > 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 > <mailto: > 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 > <mailto: > 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 > <mailto: > 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/excel-xls-open-database-problem-tp5730726p5730735.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 |
Re: excel (xls) open database problem
|
Administrator
|
Some results from a Google search on <replace carriage return excel>:
http://howtouseexcel.net/how-to-remove-line-breaks-and-carriage-returns-in-cells-in-excel http://stackoverflow.com/questions/3860184/excel-how-do-i-remove-all-carriage-returns-from-a-cell http://datapigtechnologies.com/blog/index.php/find-and-replace-carriage-returns/ HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: excel (xls) open database problem
|
|
Ok, got it solved. A co-worker found a vba macro and got it to work and it pulled all the problem stuff out and we can now read the file with spss.
Jon, thank you. Bruce, thank you also. Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: Tuesday, October 06, 2015 4:49 PM To: [hidden email] Subject: Re: excel (xls) open database problem Some results from a Google search on <replace carriage return excel>: http://howtouseexcel.net/how-to-remove-line-breaks-and-carriage-returns-in-cells-in-excel http://stackoverflow.com/questions/3860184/excel-how-do-i-remove-all-carriage-returns-from-a-cell http://datapigtechnologies.com/blog/index.php/find-and-replace-carriage-returns/ HTH. Maguin, Eugene wrote > Here's the *&*)&*%*&) PROBLEM. > (or, at least, one of them). > There are embedded carriage returns in the text string for one or more > variables. Now that I exported the file into a tab separated text file > and opened the file using multi-edit, I see that. > I'd guess that the person who put this together copied text from a > word processing program, which used a carriage return to separate paragraphs. > Actually I'm surprised this showed up on only this file given the > purpose of the specific field. > > In word, I can search and replace a limited number of format > characters, like ^p for a carriage return. Is there any way to do the same in excel. > Nothing is obvious but I 'never' use excel so I basically don't know > anything about it. > Thanks, Gene Maguin > > > > > -----Original Message----- > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Bruce Weaver > Sent: Monday, October 05, 2015 5:53 PM > To: > SPSSX-L@.UGA > Subject: Re: excel (xls) open database problem > > Gene, rather than save to a new name (i.e., Save-As, and change the > name), I think I would copy the data, paste to a new workbook, and > save that to the new name. (My fear is that Save-As with a name > change doesn't get rid of any junk in the file that might be causing > the problem.) > > Alternatively, I might save it as .CSV. > > Interesting problem. Please keep us posted when you eventually crack it. > > > > Maguin, Eugene wrote >> The file type is 97-2003, which is what all the other files are as well. >> Would a corrupted file have caused this problem and be able to >> opened, edited and saved to a new name? >> Gene Maguin >> >> From: SPSSX(r) Discussion [mailto: > >> SPSSX-L@.UGA > >> ] On Behalf Of Rick Oliver >> Sent: Monday, October 05, 2015 4:00 PM >> To: > >> SPSSX-L@.UGA > >> Subject: Re: excel (xls) open database problem >> >> If you are using File>Open>Data and selecting Excel from the list, I >> don't think ODBC should ever enter the issue in any way. Statistics >> has a native method for reading Excel files directly. It's possible >> to read Excel files as database tables using the Database Wizard (or >> GET DATA /TYPE=ODBC), and it might be useful if you wanted to use >> ODBC filtering and merging capabilities, if you are just opening a >> single sheet in a single Excel file, that is not necessary. >> >> Do you know what version the Excel file is? The dialog UI should be >> smart about detecting this, but if it isn't behaving correctly, the >> syntax for any Excel file prior to release 5 should be GET TRANSLATE >> /TYPE=XLS. For anything after Excel 5, (Excel 97 or later), the >> syntax should be GET DATA /TYPE=XLS. >> >> >> Rick Oliver >> Senior Information Developer >> IBM Business Analytics (SPSS) >> E-mail: > >> oliverr@.ibm > >> <mailto: > >> oliverr@.ibm > >> > >> >> >> >> From: "Maguin, Eugene" < > >> emaguin@ > >> <mailto: > >> emaguin@ > >> >> >> To: > >> SPSSX-L@.UGA > >> <mailto: > >> SPSSX-L@.UGA > >> > >> Date: 10/05/2015 02:43 PM >> Subject: Re: excel (xls) open database problem >> Sent by: "SPSSX(r) Discussion" < > >> SPSSX-L@.UGA > >> <mailto: > >> SPSSX-L@.UGA > >> >> >> ________________________________ >> >> >> >> Rick, thanks for your really quick reply. >> >> I did File>Open>Data and select Excel from the list of file types and >> selected the file name. The return message was >> >> (2052) Error accessing the Excel file. The file may be open by >> another application. If so, close it and retry. >> * File: "U:\Gretchen\xxxxxx\yyyyyy 2014R1.xls" >> >> Excel is closed and I shut down spss and restarted spss and tried >> again and got the same message. So far as I know there is nothing >> else accessing that file. I also restarted the machine and tried >> again and got the same message. >> >>>>Second, if you are using the 64-bit version of Statistics and the >>>>32-bit > version of Excel (or Office), you cannot use the 32-bit Excel ODBC > driver with the 64-bit version of Statistics. I don't know of any > workaround for this other than to either get the 64-bit version of > Excel or switch to the 32-bit version of Statistics. >> >> This is a 32 bit machine. I was able to open the source file with >> excel, edit a line and resave to a new (the current) name. So I >> assume the source file could not have been a 64 bit file. This file >> is one of a set of files and all the others read in ok. >> >> Let me ask this. There is a text field in all the files that is long. >> I set a 1000 character field length in the OBDC. If that field were, >> say, >> 5000 characters for some records would that cause a/the problem? >> >> Why ODBC? I frankly don’t know when ODBC is required for an excel >> file and when it is not required. >> >> Gene Maguin >> >> >> >> From: SPSSX(r) Discussion [mailto: > >> SPSSX-L@.UGA > >> ] On Behalf Of Rick Oliver >> Sent: Monday, October 05, 2015 3:16 PM >> To: > >> SPSSX-L@.UGA > >> <mailto: > >> SPSSX-L@.UGA > >> > >> Subject: Re: excel (xls) open database problem >> >> First, why are you trying to use ODBC to read an Excel file? Why not >> use >> File>Open>Data and select Excel from the list of file types? (Or GET >> File>Open>DATA >> /TYPE=XLS in syntax). >> >> Second, if you are using the 64-bit version of Statistics and the >> 32-bit version of Excel (or Office), you cannot use the 32-bit Excel >> ODBC driver with the 64-bit version of Statistics. I don't know of >> any workaround for this other than to either get the 64-bit version >> of Excel or switch to the 32-bit version of Statistics. >> >> Rick Oliver >> Senior Information Developer >> IBM Business Analytics (SPSS) >> E-mail: > >> oliverr@.ibm > >> <mailto: > >> oliverr@.ibm > >> > >> >> >> >> From: "Maguin, Eugene" < > >> emaguin@ > >> <mailto: > >> emaguin@ > >> >> >> To: > >> SPSSX-L@.UGA > >> <mailto: > >> SPSSX-L@.UGA > >> > >> Date: 10/05/2015 02:03 PM >> Subject: excel (xls) open database problem >> Sent by: "SPSSX(r) Discussion" < > >> SPSSX-L@.UGA > >> <mailto: > >> SPSSX-L@.UGA > >> >> >> ________________________________ >> >> >> >> >> Can someone give me some insight into this problem. I am attempting >> to read in an excel (.xls) file and after having browsed and >> highlighted and opened the file so that the name appears in the ODBC >> driver login box and then clicked OK, I get a box (labeled “ODBC”) >> saying SQLDriverConnect failed :[Microsoft][ODBC Excel Driver] >> External table is not in the expected format. >> >> This problem file is one of a set of files that all have the same >> structure and those other files open ok. >> >> Thanks, Gene Maguin >> >> ===================== To manage your subscription to SPSSX-L, send a >> message to > >> LISTSERV@.UGA > >> <mailto: > >> 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 > >> <mailto: > >> 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 > >> <mailto: > >> 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 > >> <mailto: > >> 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/excel-xls-open-database- > problem-tp5730726p5730735.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 [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/excel-xls-open-database-problem-tp5730726p5730741.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 |
| Free forum by Nabble | Edit this page |