Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Returns csv-String from Table

Author  Topic 

gpc44
Starting Member

35 Posts

Posted - 2013-08-14 : 03:54:34
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 05:03:08
[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 - 2013-08-14 : 07:31:25
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 07:50:56
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
   

- Advertisement -