SPSSX Discussion

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

Classic

List

Threaded

6 messages Options

Justin Black

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

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

Albert-Jan Roskam

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

Oliver, Richard

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

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:

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

Justin Black

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

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

Justin Black

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

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
>

Peck, Jon

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

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: