Exporting data to csv like file in MSSQL

Question

Let's say that csv-like file is storing invoices and it looks like: (two schemas of csv file merged together)

InvID |Seller    |Buyer    |Date     |Sum
1     |SomeSeller|SomeBuyer|2018-10-1|123.00
PositionNumber|ItemName|Quantity|Price
1             |Item1   |2       |10.00
2             |Item2   |1       |90.00
3             |Item3   |4       |23.00
InvID|Seller    |Buyer    |Date     |Sum
2    |SomeSeller|SomeBuyer|2018-10-1|123.00
PositionNumber|ItemName|Quantity|Price
1             |Item1   |2       |10.00
2             |Item2   |1       |90.00
3             |Item3   |4       |23.00
InvID|Seller    |Buyer    |Date     |Sum
3    |SomeSeller|SomeBuyer|2018-10-1|123.00
PositionNumber|ItemName|Quantity|Price
1             |Item1   |2       |10.00
2             |Item2   |1       |90.00
3             |Item3   |4       |23.00

What is the easiest way to achieve this? I was thinking about using cursor and sticking together the result for invoice header and invoice items and repeat it for every invoice but maybe there is some easier way to do this.

What is the correct way to get such a thing?

It has to be runned everyday. So there has to be possibility to attach it to sql server job

Possible duplicate of [Export query result to .csv file in SQL Server 2008](https://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008) — pim, Oct 01 '18 at 13:15
Ye, but all of them are doing it just for single csv, as you see in "my" csv file are merged two types (headers and positions) and both of them have to have that header part (eg. positionNumber, ItemName) that the tricky part and I dont have idea how to merge it like that. Also that invoice and items are linked so it always should looks like under invoice are linked items. — Wolkowsky, Oct 01 '18 at 13:21
My apologies. It's hard to decipher exactly what's going on from your CSV sample due to it's formatting. — pim, Oct 01 '18 at 13:24
No problem. I have no idea how to describe it in more readable way. — Wolkowsky, Oct 01 '18 at 13:26
Do you need the columns padded as above? Assumably not... Does it have to be such a format? Don't you want to use string marks like `1|"Item2"|2|10.00` (you must be sure, that the pipe `|` will never occur in your text! Why not XML, JSON or any other format easier to consume? — Shnugo, Oct 01 '18 at 13:33
It depends on third party software. I cannot do anything about this. So the answer is - yes, it have to be done as it is shown above. Summary: Delimiter have to be pipe ( | ) It have to be string without quotes and so on :) Maybe apart those empty spaces ;) — Wolkowsky, Oct 01 '18 at 13:37

score 1 · Accepted Answer · answered Oct 02 '18 at 06:52

Well, this is a bit tricky...

Try this query:

I'm mocking up a stand-alone test scenario (for future questions: please try to provide such an MCVE in your question)

DECLARE @tblInv TABLE(InvID INT,Seller VARCHAR(100),Buyer VARCHAR(100),[Date] DATE);
INSERT INTO @tblInv VALUES(1,'Seller 1','Buyer 1','20180101')
                         ,(2,'Seller 2','Buyer 2','20180202');

DECLARE @tblPos TABLE(PosID INT, InvID INT, PositionNumber INT, ItemName VARCHAR(100),Quantity INT,Price DECIMAL(14,4));
INSERT INTO @tblPos VALUES(1,1,1,'Item 1 in 1',11,1.1)
                         ,(2,1,2,'Item 2 in 1',12,1.2) 
                         ,(3,2,1,'Item 1 in 2',21,2.1) 
                         ,(4,2,2,'Item 2 in 2',22,2.2) 
                         ,(5,2,3,'Item 3 in 2',23,2.3);

--The query will assign the result to the variable @Result

DECLARE @Result NVARCHAR(MAX)=
(                       
    SELECT CONCAT('InvID|Seller|Buyer|Date|Sum',CHAR(13) + CHAR(10)
                 ,i.InvID,'|'
                 ,i.Seller,'|'
                 ,i.Buyer,'|'
                 ,i.[Date],'|'
                 ,(SELECT SUM(Price) FROM @tblPos x WHERE x.InvID=i.InvID),CHAR(13) + CHAR(10)
                 ,'PositionNumber|ItemName|Quantity|Price',CHAR(13) + CHAR(10)
                 ,(
                    SELECT CONCAT(p.PositionNumber,'|'
                                 ,p.ItemName,'|'
                                 ,p.Quantity,'|'
                                 ,p.Price,CHAR(13) + CHAR(10))
                    FROM @tblPos p
                    WHERE p.InvID=i.InvID
                    ORDER BY p.PositionNumber
                    FOR XML PATH(''),TYPE).value('.','nvarchar(max)')
                  )
    FROM @tblInv i
    FOR XML PATH(''),TYPE
).value('.','nvarchar(max)');

--This will print the result out (use PRINT or chose results to text)

PRINT @Result;

The result

InvID|Seller|Buyer|Date|Sum
1|Seller 1|Buyer 1|2018-01-01|2.3000
PositionNumber|ItemName|Quantity|Price
1|Item 1 in 1|11|1.1000
2|Item 2 in 1|12|1.2000
InvID|Seller|Buyer|Date|Sum
2|Seller 2|Buyer 2|2018-02-02|6.6000
PositionNumber|ItemName|Quantity|Price
1|Item 1 in 2|21|2.1000
2|Item 2 in 2|22|2.2000
3|Item 3 in 2|23|2.3000

You can use BCP to write this out to a file. There are many examples around (one is here)

This is brilliant! Thanks for that! For sure next time I'll provide everything. Sorry for that! — Wolkowsky, Oct 02 '18 at 09:09

score 0 · Answer 2 · answered Oct 01 '18 at 13:16

0

I posted an answer on how to do this with PowerShell (my preferred method) a while back.

But the meat and potatoes would be something like:

$dbname = "**YOUR_DB_NAME_WITHOUT_STARS**"
$AttachmentPath = "c:\\export.csv"
$QueryFmt= @"
**YOUR_QUERY_WITHOUT_STARS**
"@

Invoke-Sqlcmd -ServerInstance **SERVER_NAME_WITHOUT_STARS** -Database  $dbname -Query $QueryFmt | Export-CSV $AttachmentPath -NoTypeInformation

answered Oct 01 '18 at 13:16

pim

12,019
6
66
69

1

hi mitch! sure, why don't you come by my place, say round 6pm and I'll fry up some steaks and bakers! – pim Oct 02 '18 at 09:30

Exporting data to csv like file in MSSQL

2 Answers2