Solved

HELP 4 SQL tables, Into 1 Excel Flatfile

Posted on 2011-03-21
4
335 Views
Last Modified: 2013-11-10
Hi Experts,

What im trying to do is extract data from 4 databases and insert it into 1 Excel spredsheet flatfile,

So to summarise i have 4 Tables, Need data extracting into 1 Excel Flat file,

Can someone help me please?

Thanks
0
Comment
Question by:NeoAshura
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
KGNickl earned 500 total points
ID: 35180953
Most likely the easiest way to do it is 1 table at a time. If your going to do it more frequently based on the way you choose you could write a script to run the command lines one after another.

From: http://www.spatialkey.com/support/how-to/export-your-data-to-csv-from-a-database/

MS SQL Server:SQL Server is a commercially available database from Microsoft (there is a smaller, less featured version called SQL Server Express as well that is available for download for free). SQL Server is an enterprise ready database used by many companies around the world. Over the years there have been many management tools both from third parties and from Microsoft (Enterprise Manager, Management Studio, and through MS Developer Studio, for example). Two options for exporting as CSV from a MS SQL Server are use of the BPC (Bulk Copy Program) command line tool or through Data Transformation Services.

BCP:
Bulk Copy Program can be executed from a command line or through SQL.
Command line:
BCP <table name> out <filename.csv> -c -t, -U <user name> -P <password> -S<server name>
Switches:
      -c : export as ASCII with a tab delimiter and carriage return/line feed line terminator
      -t, : override the tab delimiter with a comma
      -U : username
      -P : password
      -S : server to connect to

SQL:
MS SQL Server has a Transact-SQL command called xp_cmdshell, this command allow you to execute command line executables from in SQL
xp_cmdshell BCP <table name> out <filename.csv> -c -t, -T -S<server name>
Note that I changed the -U and - P to a -T which uses a trusted connection as we are probably authenticating to the SQL server machine already to run the SQL command.

DTS:
DTS packages can be created using SQL scripts or through a MS user interface.
1. From Enterprise Manager choose > Wizards > Data Transformation > Export Data
2. Choose your source database
3. For the destination select Text File and specify the destination file and filename with a .csv extension
4. Set the table or query to pull the data from
5. Set the option that first row contains column information
6. On the screen to select the delimiter, be sure the comma is selected
7. Run the package
0
 
LVL 5

Expert Comment

by:KGNickl
ID: 35181020
Also, since I didn't mention it above since the table will most likely not match why not just export a file per table? Then handle them as needed? For example if its code write a function per file. Will give you more flexibility.

But if you really need 1 file you could also export 4 then write code to merge the four files into one for example using VBA, perl, etc...
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35181368
Hi nick thats not the reply,

It is 4 tables stored in SQL SERVER to be wirrten to 1 CSV Flat file, Ill have a look at that link and get back to you.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35181506
cheers, just did it with one file then put it in a loop in the package using SSIS.
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question