SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Returns csv-String from Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gpc44
Starting Member

35 Posts

Posted - 08/14/2013 :  03:54:34  Show Profile  Reply with Quote
Hi,
I am looking for a Function/Procedure, which "TableName" as a parameter, and returns each row of the table as a string with ";" as separator and "\ n" is obtained as RowDelimiter (like csv file - 1 Column for each tablerow)

Function getCsvFormatFromTable (@ TableName)
(
@Csv_value RETURNS TABLE (Value NVARCHAR (MAX))
Return @ csv_Value
)

it also can written in a #Table etc.....

Thanks

Regards ;-)
Nicole

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/14/2013 :  05:03:08  Show Profile  Reply with Quote
[code]-- Enable xp_cmdshell feature
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

DECLARE @tableName VARCHAR(50) = 'study.dbo.departments'
-- copy one file to another location using xp_cmdshell feature
DECLARE @SQL VARCHAR(120)
--COPY A NEW ONE IN
SET @SQL = 'xp_cmdshell ' + CHAR(39) + 'bcp '+ @TableName +' out "D:\temp\dept.txt" -c -t; -r \n -T' + CHAR(39)
EXEC (@SQL)

This is the main logic
'xp_cmdshell ' + CHAR(39) + 'bcp '+ @TableName +' out "D:\temp\dept.txt" -c -t; -r \n -T' + CHAR(39)
-c = Character Data
-t; = Column separator is ;
-r \n = row separator is New line character
-T = windows authentication to the SQL Server

For SQL server authentication use -U UserName -P password instead of -T

--
Chandu
Go to Top of Page

gpc44
Starting Member

35 Posts

Posted - 08/14/2013 :  07:31:25  Show Profile  Reply with Quote
Hi Bandi,
thats real fine !
Is there a option, to write the Output to a #table - not to FileSystem?

REGARDS
Nicole

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/14/2013 :  07:50:56  Show Profile  Reply with Quote
Is the following result you want?
-- Create resultant table structure
CREATE TABLE TestCSV (Row VARCHAR(MAX));
-- Insert data of original table with comma separated columns data and row end with ;
INSERT TestCSV
SELECT CAST(DEPARTMENT_ID AS VARCHAR(10))+ ','+DEPARTMENT_NAME + ',' + CAST(LOCATION_ID AS VARCHAR(10)) +';'
FROM DEPARTMENTS
-- Verify result
SELECT * FROM TestCSV

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000