Solved

HELP 4 SQL tables, Into 1 Excel Flatfile

Posted on 2011-03-21
4
329 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
  • 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now