RE: How to export whole SQL database to MS Excel Workbook

Subject: RE: How to export whole SQL database to MS Excel Workbook
From: "Ronald Schwarz" <RSchwarz -at- cosmocom -dot- com>
To: "Ned Bedinger" <doc -at- edwordsmith -dot- com>
Date: Mon, 21 Jan 2008 11:40:50 -0500

Ned,

I have to export about 40 tables.

Right now I just need this information for the future because the
database programmer did it for me.

Thanks anyway,

Ronald Schwarz

Senior Technical Writer

CosmoCom, Inc.
121 Broad Hollow Road
Melville, NY 11747 USA
Phone: +1 (631) 940-4320
Fax: +1 (631) 930-3252
EMAIL: RSchwarz -at- cosmocom -dot- com
URL: http://www.cosmocom.com


-----Original Message-----
From: Ned Bedinger [mailto:doc -at- edwordsmith -dot- com]
Sent: Saturday, December 29, 2007 2:09 PM
To: Ronald Schwarz
Cc: techwr-l -at- lists -dot- techwr-l -dot- com
Subject: Re: How to export whole SQL database to MS Excel Workbook

Ronald Schwarz wrote:
> I have to document how 50% of the SQL tables in an application relate
> to the user interface configuration menus so that the user can trace
> through a configuration audit trail.

How many tables are we talking about?

>
> Can someone explain how to export the entire SQL database to a MS
> Excel Workbook?

Read a high-level description at:

http://www.mssqltips.com/tip.asp?tip=1202

Then read about the SQL Server Import Export Wizard online at MSDN.

I can only export one table at a time to a spreadsheet in
> order to capture the column headings of the tables.

This sounds like the Bulk Copy method using bcp.exe (for a list of all
the bcp parameters and options you don't need, and a few you might need,
search MSDN or SQL Server Books Online for 'bcp export.' If you let bcp
use its defaults (export the table as tab-delimited character data with
line-end character for row-ends), then Excel ought to be able to suck it
in. Not sure if bcp can write your data to Excel with the table name and
everything, but if the only other choice you had was to go a-reading
bushels of SQL Server documentation that may or may not lead to an
updated procedure for exporting an entire database to Excel, I believe
I'd go the default bcp route and make the necessary edits to get the
worksheet names (and column data types, where useful) right.

For a few tables, I
> also need the records in order to view the values of various settings
> in the application.

My comment about data types is with respect to this requirement only.

>
> The MS SQL Server Management Studio online help does not have an
> answer and I cannot write SQL or VBS scripts to automate the process.

I think you could pick that stuff up in a weekend. The hard part, the
biggest time sink when ramping up on MS data acces code, at least when I
learned it, was finding a source of good examples to copy -- the syntax
for identifying and connecting to the database, and then exporting the
data in a given format, was always wrong in the manuals, for years it
seems but maybe that perception is due to how much of a drag it is to be
misled by putative examples FROM THE VENDOR.


Anyway, actually, the tool for $29 that Jan suggested looks like a great
value compared to the hour or two you'd spend wrangling with the Export
Wizard, bcp, or any of the many other circuituous routes by which one
might recreate an application configuration database in Excel after the
application developers went to all the trouble of putting it in SQL
Server.


Good luck.

Ned Bedinger
doc -at- edwordsmith -dot- com


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Create HTML or Microsoft Word content and convert to Help file formats or
printed documentation. Features include support for Windows Vista & 2007
Microsoft Office, team authoring, plus more.
http://www.DocToHelp.com/TechwrlList

True single source, conditional content, PDF export, modular help.
Help & Manual is the most powerful authoring tool for technical
documentation. Boost your productivity! http://www.helpandmanual.com

---
You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot-

To unsubscribe send a blank email to
techwr-l-unsubscribe -at- lists -dot- techwr-l -dot- com
or visit http://lists.techwr-l.com/mailman/options/techwr-l/archive%40web.techwr-l.com


To subscribe, send a blank email to techwr-l-join -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot- Visit
http://www.techwr-l.com/ for more resources and info.


Previous by Author: Book review of: Microsoft Word for Medical and Technical Writers
Next by Author: RE: How to export whole SQL database to MS Excel Workbook
Previous by Thread: RE: ESOL tutoring/Craig
Next by Thread: RE: How to export whole SQL database to MS Excel Workbook


What this post helpful? Share it with friends and colleagues:


Sponsored Ads