Solved

HELP 4 SQL tables, Into 1 Excel Flatfile

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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