October 14, 2003 at 8:04 am
I'm sure this has been covered before, but does anyone have a stored procedure for exporting SQL text fields into a text file.
October 14, 2003 at 8:16 am
Would bcp work for you? You can use a query string with bcp to get just the information you desire.
Guarddata-
October 14, 2003 at 8:32 am
I would rather do it via a stored procedure.
October 14, 2003 at 3:30 pm
I don't know how directly - unless you have the stored procedure call xp_cmdShell with a bcp argument.
Guarddata-
October 15, 2003 at 5:30 am
I have ussed the following. You do realize that DTS makes this very easy.
-- To BCP the table out to a text file
DECLARE @SQL varchar(255)
BEGIN
SELECT @SQL = "master.dbo.xp_cmdshell " + '"bcp dbName.dbo.tableName out F:\BackUp\ExportTableName.txt -c -yourWORKSTATION -Usa -P"'
EXEC (@SQL)
END
October 15, 2003 at 5:43 am
Slight variation using a query and tab as field separator:
DECLARE @cmd varchar(1000)
, @sql varchar(1000)
SET @sql = 'SELECT Field1, Field2'
SET @sql = @sql + ' FROM dbo.table'
SET @cmd = 'bcp "' + @sql + '" queryout \\server\docs\test.csv -c -t, -T'
EXEC master..xp_cmdshell @cmd, no_output
October 15, 2003 at 7:07 am
How can this method be used to initialize an excel worksheet rather than using Excel's menu option Get External Data?
Thanks
October 15, 2003 at 7:21 am
Here's a snippet of some code from a stored proc that extracts contact data to a file. I first insert and format my output within a staging table.
CREATE PROCEDURE p_extract (@file_nm varchar(255)) AS
BEGIN
DECLARE @cmd varchar(255)
-- e.g., @file_nm = 'C:\output.txt'
...
-- prepare the extract to file statement
SELECT @cmd = 'osql -Sservername -Uloginname -P -Q' + CHAR(34) + 'SELECT line_output_txt FROM t_extract' + CHAR(34) + ' -ddatabasename -s' + CHAR(34) + CHAR(34) + ' -w3000' + ' -o' + @file_nm
-- extract the records to the specified file
exec master..xp_cmdshell @cmd
END
October 15, 2003 at 11:38 am
Gary,
If you want to create/get data from Excel use OPENDATASOURCE. It is by far the easier way to go. The only problems I have had is that I have to isolate the distributed transactions I have going across multiple servers from the ones I do going against the OPENDATASOURCE query.
INSERT INTO #TempTable(<FieldList>)
SELECT <FieldList>
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'
,'Data Source="<FilePath>.xls";User ID=Admin;Password=;Extended properties=Excel 8.0'
)...Sheet1$
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 16, 2003 at 6:53 am
Gary,
Looks like you are opening a text file. I think Guarddata was looking for outputting to a file. Can OpenDataSource be used for outputting?
October 16, 2003 at 7:40 pm
lambje,
What "Gary" are you refering to? 🙂
I was responding to Gary Billins question on Excel files only. For text files I would use BCP as stated above. But for Excel I actually prefer to use OPENDATASOURCE as I never have to shell out to do it. The problem with OPENDATASOURCE against Excel is that you must use an XLS file. Unfortunately OPENDATASOURCE doesn't support writing directly to a text file (at least as far as I have found in my use of it).
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 17, 2003 at 7:33 am
Yes, you are the Gary I was referring to. I apologize as I didn't follow the whole flow. I did learn something new though. I'll put OpenDataSource in my toolkit as I seem to be doing more file-related activities these days.
Thanks, Jeff
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply