Solved

HELP 4 SQL tables, Into 1 Excel Flatfile

Posted on 2011-03-21
4
328 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
cheers, just did it with one file then put it in a loop in the package using SSIS.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 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

10 Experts available now in Live!

Get 1:1 Help Now