Return csv-String from Table

  • 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

  • You'd need to use dynamic SQL for this. You can query the INFORMATION_SCHEMA.columns view to find out which columns a table has.

    Using this info, you can construct a SQL query of the following form:

    SELECT CONVERT(NVARCHAR(100),col1) + ';' + CONVERT(NVARCHAR(100),col2) + ... + ';' + CONVERT(NVARCHAR(100),coln)

    FROM myTable

    You can execute this SQL statement using sp_executesql.

    If you make the dynamic query more complex, you can take NULL values, data types et cetera into account.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This is something you should do client-side, not in SQL Server. SQL Server serves the data. Formatting is best done elsewhere. I assume that this for some export purpose. There is SSIS, there is BCP, and you can write your own C# for custom format. But T-SQL is not your guy.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (8/14/2013)


    This is something you should do client-side, not in SQL Server. SQL Server serves the data. Formatting is best done elsewhere. I assume that this for some export purpose. There is SSIS, there is BCP, and you can write your own C# for custom format. But T-SQL is not your guy.

    Tsk tsk, don't you know everything is possible with TSQL? 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • If the idea is to simply dump the output to a csv file, the following powershell script will do that for you.

    You'll have to parameterize the Query.

    invoke-sqlcmd -Query "select name, create_date, collation_name from sys.databases;" | export-csv -Delimiter `; -path test.csv

  • Not a proposed solution, but an example of using system views to create a dynamic SQL SELECT statement that is only executed once to generate the output.

    /*

    * Simple example of generating the output as a set without having to dynamically loop through all the records in the table.

    *It does loop through sys.columns once to get each column in the table.

    *Certainly not a "use anywhere" solution, for instance, you may need to return a certain date format for date columns.

    * You still have to get it into a text file.

    AdventureWorks has the table used in the example:

    SELECT [ContactTypeID]

    ,[Name]

    ,[ModifiedDate]

    FROM [AdventureWorks].[Person].[ContactType]

    */

    --use AdventureWorks

    DECLARE @TableName AS NVARCHAR(128)

    DECLARE @MaxColumnValueLength AS NVARCHAR(4)

    SET @TableName = 'ContactType'

    SET @MaxColumnValueLength = '256'

    SET NOCOUNT ON

    --setup table to hold column list

    DECLARE @junk22 TABLE (

    yID INT IDENTITY(1,1) NOT NULL

    ,zID INT NULL

    ,zSQL NVARCHAR(2000) NULL

    )

    DECLARE @sql AS NVARCHAR(max) --select statement to be produced

    , @row AS INT --row counter

    , @rows AS INT -- number of columns in table + 2

    , @TableSchema AS NVARCHAR(128) -- in case table belongs to non-default schema

    SET @row = 1

    SET @sql = ''

    -- get schema

    SELECT @TableSchema = sys.schemas.[name]

    FROM sys.schemas

    JOIN sys.objects

    ON sys.schemas.schema_id = sys.objects.schema_id

    WHERE sys.objects.name = @TableName

    --cte of columns in table converted to string

    ;WITH cols

    AS (

    SELECT column_ID, CASE WHEN column_id = 1 THEN '' ELSE ' + ' + char(39) + ',' + CHAR(39) + ' + ' END + CHAR(39) + '"' + CHAR(39) + ' + Convert(varchar(' + @MaxColumnValueLength + '),[' + sys.columns.Name + ']) + ' + CHAR(39) + '" ' + CHAR(39) AS csv

    FROM sys.columns

    JOIN sys.objects

    ON sys.columns.object_id = sys.objects.object_id

    WHERE sys.objects.name = @TableName

    --ORDER BY column_id

    )

    --cte of columns names prefixed by 'SELECT ' and suffixed by 'FROM...'

    , cte_sql (zID, zSQL)

    AS (

    SELECT 0 AS zID, 'SELECT ' AS strSQL

    UNION ALL

    SELECT column_id, csv FROM cols

    UNION ALL

    SELECT 1000, 'FROM ' + @TableSchema + '.' + @TableName

    )

    --insert results into table

    INSERT INTO @junk22

    ( zID, zSQL )

    SELECT

    zID, zSQL

    FROM cte_sql

    ORDER BY zID

    --count rows from column list to process

    SELECT @rows = COUNT(*) FROM @junk22

    -- the while only loops through the column list for the table, not the table itself

    -- to build up SQL SELECT statement.

    WHILE @row <= @Rows

    BEGIN

    SELECT

    @sql = @sql + zSQL + ' ' + CHAR(13)

    FROM @junk22

    WHERE yID = @row

    SET @Row = @Row + 1

    END

    -- shows the select statement you have generated

    print @sql

    --execute select statement

    exec (@SQL)

  • Gee, this sure sound like using BCP in character based mode would do the trick.

    BCP DatabaseName.schema.table OUT FileName.csv /m 100 /b 100 /e TableName.err /c /t\, /r /S ServerName /T /E

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply