Remove Dashes from Row 2 on file-CodePudding

I have a bat file that uses SQLCMD to call an SQL Statement

My SQL statement is fine

However, under the header row, in Row 2, I get a load of dashes like so,

SKU,Name,Description
---,----,-----------
123,Apple.,Fruit
124,Orange,Fruit
125, Broccoli, Veg

Example Code:

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD" -S TEST-SQL02 -E -Q "SET nocount ON; select * from fruitveg;" -s "," -W -o "E:\Paddy\Test\test.txt"

Should look like:

SKU,Name,Description
123,Apple.,Fruit
124,Orange,Fruit
125, Broccoli, Veg

I have tried including -h-1 and -h -1 but removes header and dashes

Also, I have tried writing PS script to remove the dashes

Get-ChildItem "E:\Paddy\Test\test.txt" | ForEach-Object {Set-Content (Get-Content $_ | Select-Object -Skip 1) -Path $_}

but removes header and dashes

CodePudding user response：

I've no idea what the sqlcmd switches do, but presuming that omitting -o "E:\Paddy\Test\test.txt" will deliver the report to the console, then

"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD" -S TEST-SQL02 -E -Q "SET nocount ON; select * from fruitveg;" -s "," -W |findstr /v /b "-">"E:\Paddy\Test\test.txt"

should remove that troublesome line.

findstr: Find all lines that /v do NOT /b begin with "-"

CodePudding user response：

I'd advise that you just pipe the StdOut from SQLCmd.exe directly to FindStr.exe, matching all lines which contain any character which is not a dash or a comma.

"%ProgramFiles%\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.exe" -S "TEST-SQL02" -E -Q "SET nocount ON; SELECT * FROM fruitveg;" -s "," -W 2>NUL | %SystemRoot%\System32\findstr.exe /R "[^\-,]" 1>"E:\Paddy\Test\test.txt"

This methodology is more robust than assuming that no wanted line begins with one, (or more), dashes, and excluding those from the results.

CodePudding user response：

Continuing from my comment.

If what you show us in the post on screen actually looks like this:

SKU Name     Description
--- ----     -----------
123 Apple.   Fruit      
124 Orange   Fruit      
125 Broccoli Veg

so, without those commas, then this is the default Format-Table output for an array of objects in PowerShell.

If however that is the output of your SQL statement , then with PowerShell you can clean-up the file using

(Get-Content -Path "E:\Paddy\Test\test.txt" -Raw) -replace '(?m)^[-,] \r?\n' | 
Set-Content -Path "E:\Paddy\Test\test.txt"

This will remove the dashed line leaving you with a workable CSV file.

P.S. why is there a dot after Apple ?