Creating multiple CSV files using SQL

Question

I have a Table1 below in SQL.

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
1  | Asgard   | John Smith    | A1           |
2  | Asgard   | Andrew Bailey | A2           |
3  | BT       | Tony Saw      | B1           |
4  | BT       | Greg Lee      | B2           |
5  | BT       | Jenny Main    | B3           |
6  | Zurich   | Beth Bond     | Z1           |
7  | Zurich   | Fang Li       | Z2           |
8  | Zurich   | Garry Low     | Z3           |

I need to export this table to .CSV (Microsoft Excel) and split between each provider.

Agard.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
1  | Asgard   | John Smith    | A1           |
2  | Asgard   | Andrew Bailey | A2           |

BT.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
3  | BT       | Tony Saw      | B1           |
4  | BT       | Greg Lee      | B2           |
5  | BT       | Jenny Main    | B3           |

Zurich.csv

ID | Provider | AdviserName   | PolicyNumber |
---|----------|---------------|--------------|
6  | Zurich   | Beth Bond     | Z1           |
7  | Zurich   | Fang Li       | Z2           |
8  | Zurich   | Garry Low     | Z3           |

I have tried using Foreach Loop Container, however it doesn't distinguish between provider. (btw, I have more than 50 providers to be split into different .csv files).

I'm using SQL Server 2012. Thanks for the help.

SELECT DISTINCT * FROM Table1 A LEFT JOIN (SELECT DISTINCT Provider FROM Table1) AS B ON A.Provider = B.Provider ORDER BY A.Provider — Adhitya Sanusi
– Adhitya Sanusi, Commented Mar 1, 2018 at 1:13
I put above query into SSIS package (inside Execute SQL Task). — Adhitya Sanusi
– Adhitya Sanusi, Commented Mar 1, 2018 at 1:14

Kevin · Accepted Answer · 2018-03-01 08:36:28Z

2

You can do this in SQL, you don't need to use SSIS, it will work also, but here is how you can do it in SQL.

First make sure that the 'xp_cmdshell' is enabled.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Using a cursor and the bcp.exe utility you can get the result you want

DECLARE @provider VARCHAR(255), @cmd VARCHAR(512)
DECLARE curs CURSOR FOR
    SELECT DISTINCT Provider FROM Test.dbo.ExportProvider          

OPEN curs
FETCH NEXT FROM curs INTO @provider

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'bcp.exe "SELECT Id, Provider, AdviserName, PolicyNumber FROM Test.dbo.ExportProvider WHERE Provider = ''' + @provider + '''" queryout "C:\Temp\' + @provider + '.csv" -c -t, -C RAW -T -S ' + @@SERVERNAME
    EXECUTE xp_cmdShell @cmd
    FETCH NEXT FROM curs INTO @provider
END

CLOSE curs 
DEALLOCATE curs

This will result in 3 .csv files in the temp folder. Hope this helps.

answered Mar 1, 2018 at 8:36

Kevin

7816 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adhitya Sanusi Over a year ago

Great !!! it's working Kevin. Thanks for your help. I don't need to use SSIS package and its script anymore. Thanks.

ASH · Accepted Answer · 2018-03-04 14:32:34Z

See the sample(s) below. That will do what you want.

-- DROP TABLE Reporting_Table 
CREATE TABLE Reporting_Table (
       ID    varchar(10),
       IssueDate     Date,
       ExpirationDate    Date,
       Principal     Money,
       Interest  Money,
       Qtrs      Integer,
       Amount     Money)

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('1232949523', '01/01/2017', '12/31/2019', 1000000, .02, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('1232949523', '03/05/2017', '12/31/2018', 5200000, .01, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('1232949523', '03/31/2017', '06/01/2020', 4900000, .015, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9567323294', '06/30/2017', '05/22/2019', 3500000, .02, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9567323294', '09/17/2017', '11/21/2022', 2000000, .02, Null, Null);

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9567323294', '01/01/2017', '12/31/2019', 1000000, .02, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9967949523', '03/05/2017', '12/31/2018', 5200000, .01, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9967949523', '03/31/2017', '06/01/2020', 4900000, .015, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9967949523', '06/30/2017', '05/22/2019', 3500000, .02, Null, Null); 

INSERT INTO Reporting_Table (ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount)
VALUES ('9967949523', '09/17/2017', '11/21/2022', 2000000, .02, Null, Null);

SELECT ID, IssueDate, ExpirationDate, Principal, Interest, (Principal * Interest) As Amount
FROM Reporting_Table



select *, ntile(5) over(order by Principal) as tile_nr from Reporting_Table

Select *
From Reporting_Table


ALTER FUNCTION dbo.fxnExample (@Parameter1 NVARCHAR(255))
RETURNS TABLE
AS
RETURN
(
    SELECT ID, IssueDate, ExpirationDate, Principal, Interest, Qtrs, Amount
    FROM Reporting_Table
    WHERE id = @Parameter1
)


-- Usage Example
SELECT * FROM dbo.fxnExample('1232949523')   -- only data from '1'
SELECT * FROM dbo.fxnExample('9567323294')   -- only data from '2'
SELECT * FROM dbo.fxnExample('9967949523')   -- only data from '3'


-- SPLIT A TABLE INTO EQUAL HALFS


select *, ntile(2) over(order by ID) as tile_nr from Reporting_Table

Collectives™ on Stack Overflow

Creating multiple CSV files using SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related