Solved

HELP 4 SQL tables, Into 1 Excel Flatfile

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 29
[SQL server / powershell] bulk delete table from CSV 8 33
syntax sql error 2 14
Proper Case SQL Command 2 10
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

803 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