Creating Text File From Table 1

[webuser]

I need to create text file from a table within a Stored procedure. What is the easiest way to do this?

Thanks in advance!

 

[tlbroadbent]

Check the SQL FAQ at the following link.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14379

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[webuser]

Thank you for the link. It was helpful, but I need a way to format the outputted text file. For example, if I have a table called 'Employees', I want to be able to output the ID, LastName, FirstName, Street etc. fields into a text file with the following format:

00001 Bush George 555 West 10th Street
00002 Reagan Ronald 1600 Pennsylvania Avenue
00003 Clinton Bill 123 Sesame Street

I want the fields to be fixed length, not
delimitted by any character...

Thanks again!

 

[tlbroadbent]

You can create a query or view that formats the rows as desired.

Create View vFormattedOutput As

Select OutLine=
Convert(char(5), ID) + ' ' +
Convert(char(12), LastName) +
Convert(char(12), FirstName) +
Convert(cahr(20), Address)
From Table

You could use this view in BCP, OSQL or even in DTS. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[webuser]

Great! This works. One last thing though...
Here is the code I have:
declare @str varchar(255)

select @str = 'osql -Q"Select Convert(char(5), [ID]), Convert(char(12), LastName),Convert(char(12), FirstName) From Project.dbo.Patients" -E -oe:\file.txt'
exec master..xp_cmdshell @str

The output is something like this:

----- ------------ ------------
2 Bush George
3 Reagan Ronald
4 Clinton Bill

(3 rows affected)

I need a way to create this file w/o the ----- header and without the footer (3 Rows Affected). Is there a way to prevent any error messages from being outputted at all too?

Thanks again!!!

 

[tlbroadbent]

Use the option "-h-1" to suppress headers. Add "Set Nocount On" to the query string to eliminate the row count information.

select @str = 'osql -Q"Set Nocount On ; Select Convert(char(5), [ID]), Convert(char(12), LastName),Convert(char(12), FirstName) From Project.dbo.Patients" -h-1 -E -oe:\file.txt'
exec master..xp_cmdshell @str Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[webuser]

Ok. Everything works, ALMOST perfectly. For some reason, I am getting 1 blank character string at the beginning of each line in my text file. It is either a blank character string or a carriage return/line feed. Here is my code:

Set @str = 'osql -Q"' + @SQL1 + '" -h-1 -E -w1000 -o' + @MySchedFile
exec master..xp_cmdshell @str

I checked the SQL and it looks fine...The output to the text file is something like this:
1,2,,1031,VT,20011210,09:00,20011210,13:00,0,20020107202043
1,2,,1031,VT,20011210,09:00,20011210,13:00,0,20020107202043
1,2,,1031,VT,20011210,09:00,20011210,13:00,0,20020107202043
1,2,,1031,VT,20011210,09:00,20011210,13:00,0,20020107202043
1,2,,1031,VT,20011210,09:00,20011210,13:00,0,20020107202043

but there is always an extra space before the 1st 1 of each line...

 

[tlbroadbent]

What is the character type of the first column? Is it posssible to trim the output - either the first column of the entire output string?

Select ltrim(<selection list>)
From table... Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[webuser]

I tried the LTrim on the whole select statement and on the 1st field. Also tried putting RTrim on the last field and the entire select statement. I am still getting the blank space. I checked the file in a Hex Editor and it appears that the first character is a space and not a CR or LF.

One other thing I'm confused about, and maybe it's related is th -w option of the OSQL statement. It seems to be a max character option, b/c when I make it too small, the lines wrap, but I don't know if it's affecting this in any way.

Thanks again.

 

[tlbroadbent]

In the past, I had problems with the space in the first column. Until today, I hadn't found a way to avoid the extra space. However, I've discovered that the -s option controls the column separator. If set to an empty string, the first column will contain an empty string. The downside is that there will be no separators automatically inserted between columns. You will need to provide the separators yourself.

Example: Use char(9) to insert a TAB character between columns.

osql /SServerName /E /w256 /h-1 /s&quot;&quot;
/Q&quot;Select EmpID + char(9) +
EmpName + char(9) + EmpOrgn
From Employees&quot;
/o c:\temp\emplist.tab Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[webuser]

Amazing! Thank you so much. Works like a charm.