How do I create a flat text file from various fields within a table...

Joel_Sisko
Joel_Sisko used Ask the Experts™
on
Newbie question:

How do I create a flat text file from various fields within a table from a single database and adding some static information to the result as well? To clarify:

Let’s say the database name is User, which has a table called Properties. The table has three columns: Name, EmployeIDnumber, Status.

I would like to grab the data from each row (and continue to the end) and string the result into a flat text file while adding some static information.

Name  |  EmployeeIDnumber |  Status

Joel    | 3456                           | Vacation
Bob    | 4566                           |  Paid Leave
Amy   | 7896                           |  Active Duty


The resulting text file needs to look like:

Joel, 3456, is currently on Vacation.
Bob, 4566, is currently on Paid Leave.
Amy, 7896, is currently on Active Duty.
...
Last row of table.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
you can use OSQL utility to do this
Database Consultant
Top Expert 2009
Commented:

DECLARE @cmd VARCHAR(5000)
 
SET @cmd = 'OSQL -S servername -d Pubs '
    + ' -U username -P password '
    + ' -Q "select ''AuID = ''+Au_id +Char(13)+char(10)+''au_Lname = ''+ au_Lname+Char(13)+char(10)+Char(13)+char(10)+''-----------''+Char(13)+char(10) from authors"'
    + ' -o c:\test.txt'
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

Author

Commented:
aneesshttingal,

Where do I add the static content, or more specific, is it in what you posted?

Joel
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
you need to replace the ServerName with ur servername
 Pubs - with your database name
Username - with ur sql server username
password - with ur password
urTable     - with the tableName

DECLARE @cmd VARCHAR(5000)
 
SET @cmd = 'OSQL -S servername -d Pubs '
    + ' -U username -P password '
    + ' -Q "select Name, EmployeeIDnumber, Status from urTable"'
    + ' -o c:\test.txt'
 
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
Assuming your using .net c# ,  create a function wich returns a datable containing the
result of the records from the database
Then loop for each row and concatenate the fields into a string ( comma demilimted )
eg.   string myrow =  drow["field1"].ToString() + "," + ...  (you get the jist )  and then on that row at your designated  filed add in the static text you want  and Output this string  to a text file  (using a streamwriter for example ) myStreamWriter.WriteLine( myrow );






Well you would retrieve a resultset from the database in your application ( example c#)

I'm looking at this question and wondering if you are trying to create this file in .NET or in SQL. The best, and fastest way to create a text file from a table is to run the old school bcp command. You will need to create a cursor to go through each one of the records. Then you can run this command in your stored proc:

bcp "[database]..[table_name]" out "[file path name]" -c -q -U"[user name for database]" -P"[user name for database]"

This will create a batch file where ever you want. The file can be tab or comma delimited. For more help, search "-bcp" in SQLServer Books Online.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial