CSV – row values concatenation (FOR XML PATH) and exporting column values to a MS Excel file (Microsoft.Jet.OLEDB)

Posted: November 20, 2011 in MSSQL
Tags:

Task: to create row values concatenation, i.e. CSV (Comma Separated Values), by means of the FOR XML PATH construction, and to export database table column values to the MS Excel file using the Microsoft.Jet.OLEDB ad hoc method of connecting and accessing remote data by using OLE DB
Solution: see Source code 1 – 4



EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'ad hoc distributed queries', 1;
GO
RECONFIGURE;
GO

Source code 1: system configuration



USE [AdventureWorks]
GO

DECLARE @InputData        AS TABLE
(
        [ManagerCounter]  INT,
        [ManagerLoginID]  NVARCHAR(256),
        [EmployeeLoginID] NVARCHAR(256)
)
;WITH [Employees]
(
      [ManagerCounter],
      [ManagerID],
      [LoginID]
)
AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [ManagerID] ORDER BY [ManagerID]),
           [ManagerID],
           [LoginID]
    FROM   [HumanResources].[Employee]
)
INSERT INTO @InputData
(
            [ManagerCounter],
            [ManagerLoginID],
            [EmployeeLoginID]
)
SELECT   [ManagerCounter],
         [Managers].[LoginID],
         [Employees].[LoginID]
FROM     [HumanResources].[Employee] AS [Managers] INNER JOIN [Employees] ON
         [Managers].[EmployeeID] = [Employees].[ManagerID]
ORDER BY [Managers].[LoginID]

/* temporary test */
SELECT    [ManagerCounter]  AS 'ManagersCounter',
          [ManagerLoginID]  AS 'Managers',
          [EmployeeLoginID] AS 'Employees'
FROM      @InputData
ORDER BY  [ManagerLoginID]

Source code 2: input data preparation (ManagersCounter – number of employees per manager)


MS SSMS result of input data preparation (ManagersCounter - number of employees per manager)

Figure 1: MS SSMS result of input data preparation (ManagersCounter - number of employees per manager)



SELECT       [Managers].[ManagerLoginID] AS 'Manager',
(
    SELECT
        CASE
	     WHEN [Employees].[ManagerCounter] = 1
                  THEN [Employees].[EmployeeLoginID]
	     ELSE ',' + [Employees].[EmployeeLoginID]
	END
    FROM     @InputData AS [Employees]
    WHERE    [Employees].[ManagerLoginID] = [Managers].[ManagerLoginID]
    ORDER BY [Employees].[ManagerLoginID]
    FOR XML  PATH('')
)            AS 'Employees'
FROM         @InputData AS [Managers]
GROUP BY     [Managers].[ManagerLoginID]

Source code 3: row values concatenation (converting to the CSV format) by means of FOR XML PATH


MS SSMS result of row values concatenation (converting to the CSV format) by means of FOR XML PATH

Figure 2: MS SSMS result of row values concatenation (converting to the CSV format) by means of FOR XML PATH



INSERT INTO OPENROWSET
(
            'Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\Managers.xls;',
                -- NOTE: the MS Excel file must already exist on the disk 
                --       together with the specified path and name!
            'SELECT [Managers] FROM [Managers$]'
                -- NOTE: the above-mentioned MS Excel file must have one 
                --       sheet named Managers, and the value in cell [0, 0], 
                --       i.e. the first row and column, must be 
                --       set to Managers (see Figure 5)!
)
SELECT	    [Managers].[ManagerLoginID]
FROM	    @InputData AS [Managers]
GROUP BY    [Managers].[ManagerLoginID]

GO

Source code 4: exporting data (the column Managers) to the MS Excel file on the disk


MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - error

Figure 3: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - error


MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - solution for the above error

Figure 4: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - solution for the above error


MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk

Figure 5: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk


Additional references:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s