Exporting to Excel - Unable to Append Tables (Error # 6492)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Exporting to Excel - Unable to Append Tables (Error # 6492)

Justin Black
SPSSers,

Many thanks in advance for any advice on the following.

I am attempting to export multiple datasets to different sheets in the same
Excel workbook, using the following steps:
1) Export dataset1 to Excel
2) Append dataset2 as a new sheet in the workbook created in Step 1

For Step 1, I'm using a simple SAVE TRANSLATE command.

For Step 2, I pasted syntax generated using "Export to Database" through the
GUI (which is also a SAVE TRANSLATE command, but more complicated - to me,
at least...).

However, Step 2 fails and gives Error # 6492: "[Microsoft][ODBC Excel
Driver] Cannot update.  Database or object is read-only."  It seems that
SPSS is holding the Excel file open after Step 1, which doesn't allow it to
be accessed in Step 2.  If I close SPSS, then reopen SPSS and run the append
syntax (Step 2), it works fine.  Likewise, if I create a blank Excel file,
and run the append syntax without running Step 1, it works fine.

Is there a way to have SPSS terminate its access to the Excel file, so that
the file can be accessed in a subsequent step?

Let me know if any further information is required to comment on this
problem.

Thanks again,

--Justin

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Albert-Jan Roskam
Hi,

Why don't you just use ADD FILES to add file 2 to file 1, and then use SAVE TRANSLATE to export to xls? That should work.. Not sure if there is a 'append' option when exporting to xls; only 'replace' (afaik).

Cheers!!
Albert-Jan


--- On Thu, 6/26/08, Justin Black <[hidden email]> wrote:

> From: Justin Black <[hidden email]>
> Subject: Exporting to Excel - Unable to Append Tables (Error # 6492)
> To: [hidden email]
> Date: Thursday, June 26, 2008, 11:18 PM
> SPSSers,
>
> Many thanks in advance for any advice on the following.
>
> I am attempting to export multiple datasets to different
> sheets in the same
> Excel workbook, using the following steps:
> 1) Export dataset1 to Excel
> 2) Append dataset2 as a new sheet in the workbook created
> in Step 1
>
> For Step 1, I'm using a simple SAVE TRANSLATE command.
>
> For Step 2, I pasted syntax generated using "Export to
> Database" through the
> GUI (which is also a SAVE TRANSLATE command, but more
> complicated - to me,
> at least...).
>
> However, Step 2 fails and gives Error # 6492:
> "[Microsoft][ODBC Excel
> Driver] Cannot update.  Database or object is
> read-only."  It seems that
> SPSS is holding the Excel file open after Step 1, which
> doesn't allow it to
> be accessed in Step 2.  If I close SPSS, then reopen SPSS
> and run the append
> syntax (Step 2), it works fine.  Likewise, if I create a
> blank Excel file,
> and run the append syntax without running Step 1, it works
> fine.
>
> Is there a way to have SPSS terminate its access to the
> Excel file, so that
> the file can be accessed in a subsequent step?
>
> Let me know if any further information is required to
> comment on this
> problem.
>
> Thanks again,
>
> --Justin
>
> =====================
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Oliver, Richard
Well, you've identified the problem fairly well. In SPSS 16, I think there was an issue with the Export to Database wizard not properly closing the target data source under some circumstances. The workaround, as you've also discovered, is to use command syntax to write back to the data source. After using the UI to generate the syntax, you need to shut down and restart SPSS, but if you just use the generated syntax in subsequent sessions, you shouldn't have a problem.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Albert-jan Roskam
Sent: Sunday, June 29, 2008 12:49 PM
To: [hidden email]
Subject: Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Hi,

Why don't you just use ADD FILES to add file 2 to file 1, and then use SAVE TRANSLATE to export to xls? That should work.. Not sure if there is a 'append' option when exporting to xls; only 'replace' (afaik).

Cheers!!
Albert-Jan


--- On Thu, 6/26/08, Justin Black <[hidden email]> wrote:

> From: Justin Black <[hidden email]>
> Subject: Exporting to Excel - Unable to Append Tables (Error # 6492)
> To: [hidden email]
> Date: Thursday, June 26, 2008, 11:18 PM
> SPSSers,
>
> Many thanks in advance for any advice on the following.
>
> I am attempting to export multiple datasets to different
> sheets in the same
> Excel workbook, using the following steps:
> 1) Export dataset1 to Excel
> 2) Append dataset2 as a new sheet in the workbook created
> in Step 1
>
> For Step 1, I'm using a simple SAVE TRANSLATE command.
>
> For Step 2, I pasted syntax generated using "Export to
> Database" through the
> GUI (which is also a SAVE TRANSLATE command, but more
> complicated - to me,
> at least...).
>
> However, Step 2 fails and gives Error # 6492:
> "[Microsoft][ODBC Excel
> Driver] Cannot update.  Database or object is
> read-only."  It seems that
> SPSS is holding the Excel file open after Step 1, which
> doesn't allow it to
> be accessed in Step 2.  If I close SPSS, then reopen SPSS
> and run the append
> syntax (Step 2), it works fine.  Likewise, if I create a
> blank Excel file,
> and run the append syntax without running Step 1, it works
> fine.
>
> Is there a way to have SPSS terminate its access to the
> Excel file, so that
> the file can be accessed in a subsequent step?
>
> Let me know if any further information is required to
> comment on this
> problem.
>
> Thanks again,
>
> --Justin
>
> =====================
> 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

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Justin Black
In reply to this post by Albert-Jan Roskam
Thank you, Albert-Jan.

Unfortunately, that won't work because the datasets have different
structures, and each needs to have a unique header row.

--Justin

On Sun, Jun 29, 2008 at 1:49 PM, Albert-jan Roskam <[hidden email]> wrote:

> Hi,
>
> Why don't you just use ADD FILES to add file 2 to file 1, and then use SAVE
> TRANSLATE to export to xls? That should work.. Not sure if there is a
> 'append' option when exporting to xls; only 'replace' (afaik).
>
> Cheers!!
> Albert-Jan
>
>
> --- On Thu, 6/26/08, Justin Black <[hidden email]> wrote:
>
> > From: Justin Black <[hidden email]>
> > Subject: Exporting to Excel - Unable to Append Tables (Error # 6492)
> > To: [hidden email]
> > Date: Thursday, June 26, 2008, 11:18 PM
> > SPSSers,
> >
> > Many thanks in advance for any advice on the following.
> >
> > I am attempting to export multiple datasets to different
> > sheets in the same
> > Excel workbook, using the following steps:
> > 1) Export dataset1 to Excel
> > 2) Append dataset2 as a new sheet in the workbook created
> > in Step 1
> >
> > For Step 1, I'm using a simple SAVE TRANSLATE command.
> >
> > For Step 2, I pasted syntax generated using "Export to
> > Database" through the
> > GUI (which is also a SAVE TRANSLATE command, but more
> > complicated - to me,
> > at least...).
> >
> > However, Step 2 fails and gives Error # 6492:
> > "[Microsoft][ODBC Excel
> > Driver] Cannot update.  Database or object is
> > read-only."  It seems that
> > SPSS is holding the Excel file open after Step 1, which
> > doesn't allow it to
> > be accessed in Step 2.  If I close SPSS, then reopen SPSS
> > and run the append
> > syntax (Step 2), it works fine.  Likewise, if I create a
> > blank Excel file,
> > and run the append syntax without running Step 1, it works
> > fine.
> >
> > Is there a way to have SPSS terminate its access to the
> > Excel file, so that
> > the file can be accessed in a subsequent step?
> >
> > Let me know if any further information is required to
> > comment on this
> > problem.
> >
> > Thanks again,
> >
> > --Justin
> >
> > =====================
> > 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Justin Black
In reply to this post by Oliver, Richard
Thanks, Richard.  I was hoping to create 42 Excel files, each with the same
13 sheets (same in structure, but with different data values).  I can't seem
to get this to work efficiently enough to make it worthwhile - it requires
too much closing and re-opening of SPSS.  I'm left with 546 Excel files,
which I'll either combine manually or by using a .VBS file.

--Justin

On Sun, Jun 29, 2008 at 5:23 PM, Oliver, Richard <[hidden email]> wrote:

> Well, you've identified the problem fairly well. In SPSS 16, I think there
> was an issue with the Export to Database wizard not properly closing the
> target data source under some circumstances. The workaround, as you've also
> discovered, is to use command syntax to write back to the data source. After
> using the UI to generate the syntax, you need to shut down and restart SPSS,
> but if you just use the generated syntax in subsequent sessions, you
> shouldn't have a problem.
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Albert-jan Roskam
> Sent: Sunday, June 29, 2008 12:49 PM
> To: [hidden email]
> Subject: Re: Exporting to Excel - Unable to Append Tables (Error # 6492)
>
> Hi,
>
> Why don't you just use ADD FILES to add file 2 to file 1, and then use SAVE
> TRANSLATE to export to xls? That should work.. Not sure if there is a
> 'append' option when exporting to xls; only 'replace' (afaik).
>
> Cheers!!
> Albert-Jan
>
>
> --- On Thu, 6/26/08, Justin Black <[hidden email]> wrote:
>
> > From: Justin Black <[hidden email]>
> > Subject: Exporting to Excel - Unable to Append Tables (Error # 6492)
> > To: [hidden email]
> > Date: Thursday, June 26, 2008, 11:18 PM
> > SPSSers,
> >
> > Many thanks in advance for any advice on the following.
> >
> > I am attempting to export multiple datasets to different
> > sheets in the same
> > Excel workbook, using the following steps:
> > 1) Export dataset1 to Excel
> > 2) Append dataset2 as a new sheet in the workbook created
> > in Step 1
> >
> > For Step 1, I'm using a simple SAVE TRANSLATE command.
> >
> > For Step 2, I pasted syntax generated using "Export to
> > Database" through the
> > GUI (which is also a SAVE TRANSLATE command, but more
> > complicated - to me,
> > at least...).
> >
> > However, Step 2 fails and gives Error # 6492:
> > "[Microsoft][ODBC Excel
> > Driver] Cannot update.  Database or object is
> > read-only."  It seems that
> > SPSS is holding the Excel file open after Step 1, which
> > doesn't allow it to
> > be accessed in Step 2.  If I close SPSS, then reopen SPSS
> > and run the append
> > syntax (Step 2), it works fine.  Likewise, if I create a
> > blank Excel file,
> > and run the append syntax without running Step 1, it works
> > fine.
> >
> > Is there a way to have SPSS terminate its access to the
> > Excel file, so that
> > the file can be accessed in a subsequent step?
> >
> > Let me know if any further information is required to
> > comment on this
> > problem.
> >
> > Thanks again,
> >
> > --Justin
> >
> > =====================
> > 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
>
> =====================
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting to Excel - Unable to Append Tables (Error # 6492)

Peck, Jon
There is a SaxBasic script, Export Excel, downloadable from SPSS Developer Central (www.spss.com) that exports pivot tables to Excel and then combines them as separate tabs in a single Excel file.  Although this script works with output pivot tables, you might find it useful as a starting point for doing something similar with data files.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Justin Black
Sent: Monday, June 30, 2008 9:03 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Exporting to Excel - Unable to Append Tables (Error # 6492)

Thanks, Richard.  I was hoping to create 42 Excel files, each with the same
13 sheets (same in structure, but with different data values).  I can't seem
to get this to work efficiently enough to make it worthwhile - it requires
too much closing and re-opening of SPSS.  I'm left with 546 Excel files,
which I'll either combine manually or by using a .VBS file.

--Justin

On Sun, Jun 29, 2008 at 5:23 PM, Oliver, Richard <[hidden email]> wrote:

> Well, you've identified the problem fairly well. In SPSS 16, I think there
> was an issue with the Export to Database wizard not properly closing the
> target data source under some circumstances. The workaround, as you've also
> discovered, is to use command syntax to write back to the data source. After
> using the UI to generate the syntax, you need to shut down and restart SPSS,
> but if you just use the generated syntax in subsequent sessions, you
> shouldn't have a problem.
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Albert-jan Roskam
> Sent: Sunday, June 29, 2008 12:49 PM
> To: [hidden email]
> Subject: Re: Exporting to Excel - Unable to Append Tables (Error # 6492)
>
> Hi,
>
> Why don't you just use ADD FILES to add file 2 to file 1, and then use SAVE
> TRANSLATE to export to xls? That should work.. Not sure if there is a
> 'append' option when exporting to xls; only 'replace' (afaik).
>
> Cheers!!
> Albert-Jan
>
>
> --- On Thu, 6/26/08, Justin Black <[hidden email]> wrote:
>
> > From: Justin Black <[hidden email]>
> > Subject: Exporting to Excel - Unable to Append Tables (Error # 6492)
> > To: [hidden email]
> > Date: Thursday, June 26, 2008, 11:18 PM
> > SPSSers,
> >
> > Many thanks in advance for any advice on the following.
> >
> > I am attempting to export multiple datasets to different
> > sheets in the same
> > Excel workbook, using the following steps:
> > 1) Export dataset1 to Excel
> > 2) Append dataset2 as a new sheet in the workbook created
> > in Step 1
> >
> > For Step 1, I'm using a simple SAVE TRANSLATE command.
> >
> > For Step 2, I pasted syntax generated using "Export to
> > Database" through the
> > GUI (which is also a SAVE TRANSLATE command, but more
> > complicated - to me,
> > at least...).
> >
> > However, Step 2 fails and gives Error # 6492:
> > "[Microsoft][ODBC Excel
> > Driver] Cannot update.  Database or object is
> > read-only."  It seems that
> > SPSS is holding the Excel file open after Step 1, which
> > doesn't allow it to
> > be accessed in Step 2.  If I close SPSS, then reopen SPSS
> > and run the append
> > syntax (Step 2), it works fine.  Likewise, if I create a
> > blank Excel file,
> > and run the append syntax without running Step 1, it works
> > fine.
> >
> > Is there a way to have SPSS terminate its access to the
> > Excel file, so that
> > the file can be accessed in a subsequent step?
> >
> > Let me know if any further information is required to
> > comment on this
> > problem.
> >
> > Thanks again,
> >
> > --Justin
> >
> > =====================
> > 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
>
> =====================
> 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

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