January 13, 2017 at 4:17 am
I need to export files that are in this format. So, the header row is 01, next row is 05, data is 10, and the trailer is 99. I know how to get the data, rows that begin with 10, but not sure how to format the header and trailer. If it is not possible then, I'll use VB or something to do the formating. Any thoughts?
0120151028GL TRANSACTIONS STNDR JRNL
05BATCH Group6 Gross Charges 2015102820150930STNDR JRNL
1080-457-003 92000
1080-462-003 2000
1080-463-003 2000
1080-464-003 2000
1080-466-003 4000
1080-476-003 3000
1080-489-003 2000
1080-547-003 2000
1080-552-003 3000
1080-564-003 1500
1080-566-003 3000
1080-588-003 1500
1080-594-003 1500
1080-599-003 1500
1080-615-003 4500
1080-941-003 50260
1080-760-074 0
1080-112-285 175760
99
January 13, 2017 at 5:33 am
PowerShell might be a good way to go here. You can use the Invoke-SQL (I think that's what it's called) cmdlet and then pipe the results to the Format-Table cmdlet, which has various options for column width.
John
January 13, 2017 at 6:31 am
John Mitchell-245523 - Friday, January 13, 2017 5:33 AMPowerShell might be a good way to go here. You can use the Invoke-SQL (I think that's what it's called) cmdlet and then pipe the results to the Format-Table cmdlet, which has various options for column width.John
Thanx.
January 13, 2017 at 6:52 am
NineIron - Friday, January 13, 2017 4:17 AMI need to export files that are in this format. So, the header row is 01, next row is 05, data is 10, and the trailer is 99. I know how to get the data, rows that begin with 10, but not sure how to format the header and trailer. If it is not possible then, I'll use VB or something to do the formating. Any thoughts?0120151028GL TRANSACTIONS STNDR JRNL
05BATCH Group6 Gross Charges 2015102820150930STNDR JRNL
1080-457-003 92000
1080-462-003 2000
1080-463-003 2000
1080-464-003 2000
1080-466-003 4000
1080-476-003 3000
1080-489-003 2000
1080-547-003 2000
1080-552-003 3000
1080-564-003 1500
1080-566-003 3000
1080-588-003 1500
1080-594-003 1500
1080-599-003 1500
1080-615-003 4500
1080-941-003 50260
1080-760-074 0
1080-112-285 175760
99
It looks like you're formatting a text file for COBOL. My approach would be to create a table variable and insert all the formatting and extra header and footer rows into it. Then it is an easy final SELECT for the whole file. It makes it easy to debug too.
Another way to handle it would be to PRINT every formatted line to an output file and avoid doing a SELECT.
I guess we really need to know the big picture, if you can use isql, SSIS, or other technology to accomplish this. Will it be a once-and-done or a repeated job?
January 13, 2017 at 7:11 am
I tried slapping a full example together, but i find it pretty annoying that Format-Table is leaving extra whitespace.
there a line in the beginning where a header would be if it were not suppressed, a trailing CrLf after, and int he part that was appended where the data would have a header and a bunch of dashes to separate header from data, there's a pair of empty lines.
I would have expected -HideTableHeaders to just pipe out data.
here was my test:
on my local dev box with SQL2016,
i ran this command:
Invoke-Sqlcmd -Query "SELECT 'MyHeaderText Is The First Line' AS txt" -ServerInstance "TSFC066" |Format-Table -HideTableHeaders| Out-File -FilePath C:\Data\myexport.txt
Invoke-Sqlcmd -Query "SELECT * from SandBox.sys.tables;" -ServerInstance "TSFC066" |Format-Table | Out-File -FilePath C:\Data\myexport.txt -Append
Invoke-Sqlcmd -Query "SELECT 'MyFooter Text Is The Last Line' AS txt" -ServerInstance "TSFC066" |Format-Table -HideTableHeaders| Out-File -FilePath C:\Data\myexport.txt -Append
Lowell
January 13, 2017 at 8:08 am
Bill Talada - Friday, January 13, 2017 6:52 AMNineIron - Friday, January 13, 2017 4:17 AMI need to export files that are in this format. So, the header row is 01, next row is 05, data is 10, and the trailer is 99. I know how to get the data, rows that begin with 10, but not sure how to format the header and trailer. If it is not possible then, I'll use VB or something to do the formating. Any thoughts?0120151028GL TRANSACTIONS STNDR JRNL
05BATCH Group6 Gross Charges 2015102820150930STNDR JRNL
1080-457-003 92000
1080-462-003 2000
1080-463-003 2000
1080-464-003 2000
1080-466-003 4000
1080-476-003 3000
1080-489-003 2000
1080-547-003 2000
1080-552-003 3000
1080-564-003 1500
1080-566-003 3000
1080-588-003 1500
1080-594-003 1500
1080-599-003 1500
1080-615-003 4500
1080-941-003 50260
1080-760-074 0
1080-112-285 175760
99It looks like you're formatting a text file for COBOL. My approach would be to create a table variable and insert all the formatting and extra header and footer rows into it. Then it is an easy final SELECT for the whole file. It makes it easy to debug too.
Another way to handle it would be to PRINT every formatted line to an output file and avoid doing a SELECT.
I guess we really need to know the big picture, if you can use isql, SSIS, or other technology to accomplish this. Will it be a once-and-done or a repeated job?
Currently, I am using an ACCESS db to manipulate the data and then using PRINT and output to a text file. General ledger data comes from a system called IDX but, it has to be reformatted in order to be uploaded into another system called MEDITECH. I thought there might be a way to do it in SQL, maybe using BCP. I'm sure it can be done using SSIS but, that isn't available right now. I only have SSMS Express.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply