As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
SQL query help
SiliconStew
Registered User regular
Registered User regular
Due to a bug in our accounting software, I need a way to create a flat text file from a sql view. But the file must have header and footer rows around each set of detail rows. I normally just use a BCP query to export the view rows as is to a text file, but due to the bug I need to export them to this other format. I've tried sql reporting services and can easily add the headers and footers there, but there is no export renderer for plain text as it's displayed in the report. At best, you'd have to do a bunch of text manipulation after the fact through a custom app/script to make that work.
Here's the normal view that's used for export:
TranNumber, TranTotal, Item, Qty, Price, Location
1111, 5.00, ItemA, 1.00, 5.00, A
2222, 7.00, ItemB, 2.00, 2.00, A
2222, 7.00, ItemC, 1.00, 1.00, A
2222, 7.00, ItemD, 4.00, 0.50, A
3333, 11.00, ItemC, 0.25, 4.00, A
3333, 11.00, ItemE, 1.00, 10.00, A
The file needs to list the transactions as simple fixed width strings with header and footer rows like so:
"00" & TranNumber & TranTotal
"01" & Item & Qty & Price <-this detail line repeats for as many items as are in the transaction
"02" & Location
So the final output file for the view above would be (all numbers padded 2 leading digits, 2 trailing digits for example):
00111105.00
01ItemA01.0005.00
02A
00222207.00
01ItemB02.0002.00
01ItemC01.0001.00
01ItemD04.0000.50
02A
00333311.00
01ItemC00.2504.00
01ItemE01.0010.00
02A
What query, stored procedure, SQL Integration Services, or otherwise could I use to do this? If it can be done with only a single (however complex) query, which I doubt, that would be best. Temporary tables can be used if needed, stored procedures created, the SaleHeader and SaleDetail tables that the view was built from can be used. Ideas, solutions? Assume I know little about SQL here.
Here's the normal view that's used for export:
TranNumber, TranTotal, Item, Qty, Price, Location
1111, 5.00, ItemA, 1.00, 5.00, A
2222, 7.00, ItemB, 2.00, 2.00, A
2222, 7.00, ItemC, 1.00, 1.00, A
2222, 7.00, ItemD, 4.00, 0.50, A
3333, 11.00, ItemC, 0.25, 4.00, A
3333, 11.00, ItemE, 1.00, 10.00, A
The file needs to list the transactions as simple fixed width strings with header and footer rows like so:
"00" & TranNumber & TranTotal
"01" & Item & Qty & Price <-this detail line repeats for as many items as are in the transaction
"02" & Location
So the final output file for the view above would be (all numbers padded 2 leading digits, 2 trailing digits for example):
00111105.00
01ItemA01.0005.00
02A
00222207.00
01ItemB02.0002.00
01ItemC01.0001.00
01ItemD04.0000.50
02A
00333311.00
01ItemC00.2504.00
01ItemE01.0010.00
02A
What query, stored procedure, SQL Integration Services, or otherwise could I use to do this? If it can be done with only a single (however complex) query, which I doubt, that would be best. Temporary tables can be used if needed, stored procedures created, the SaleHeader and SaleDetail tables that the view was built from can be used. Ideas, solutions? Assume I know little about SQL here.
Just remember that half the people you meet are below average intelligence.
SiliconStew on
0
Posts
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Something like this? Course, this is for XP, not sure what/if anything changes for 7 and up.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'bcp "SELECT * FROM SYS.DATABASES" queryout "C:\bcp.txt" -T -c -t,'
The view is denormalized and TranNumber, TranTotal and Location are coming from some header table or a combination of a SUM and header? As the same data is repeated for each line item.
I could be way off the mark here but maybe something like this?
http://pastebin.com/U3DTZ919
This is based solely on the example of the view table data shown, and I am calling that view "SalesView" in the example code.
If this is along the right track, it could be made more efficient by probably using the underlying tables; SalesHeader and SalesDetail
A CTE could probably be used to get the Header and Footer information in one query, rather than the two I am using.
This is all written from memory so it could be completely bust and have typos, as I don't have SQL Server installed.
Edit: That won't actually format your numbers correctly either as I'm just doing a straight conversion to string
ROW_NUMBER() OVER(ORDER BY TranNumber) --> ROW_NUMBER() OVER(PARTITION BY TranNumber ORDER BY TranNumber)
otherwise it would only put a header and footer row around the first transaction in the query. Not going to claim victory yet, lots more work and testing to do and I've been cockblocked on every seemingly minor issue on this project, but this gives some hope that I won't be doing everything manually. Thanks.
You might just need to sort out of the numeric formatting after that.
a STUFF() or RIGHT() with some CASTS might get you there. SQL isn't great with built-in functions for some of those kind of things.