CODEWITHSUNDEEP
batch-fileexport-to-csvsqlcmd
Lacey Bisnett
Lacey Bisnett

Reputation: 11

sqlcmd or .bat file to pull from file until blank line

I have the below command: 

sqlcmd -S CHLWBIDB204 -d PayrollReports -U finance_lookup -P lawson -i"L:\Lawson\EEIMPORT.sql" -W -o "Z:\EmployeeImport.csv" -h-1 -s","

It works wonderfully except the receiver of this file cannot accept it with a blank line and the totals it provides.

Something like: 

           ...
(line 10)  1, 2292, KPR, 7.94
(line 11)
(line 12)  (10 rows affected)

Line 11 and 12 cannot be in the file so I am thinking some command is needed to stop at blank line.

Upvotes: 1

Views: 949

Answers (2)

Andriy M
Andriy M

Reputation: 77657

If you can edit the .sql file, add SET NOCOUNT ON; before the SELECT statement. (Alternatively, save the modified version of the script under a different name and use it instead.)

That will suppress the … rows affected message (together with the preceding empty line).

Upvotes: 0

dbenham
dbenham

Reputation: 130819

All you need to do is pipe the output to FINDSTR and filter out the unwanted lines.

The /V option throws away lines that match. The command below looks for empty lines and your count line. The search terms may have to be refined - I don't have sqlcmd to test with.

sqlcmd .... | findstr /v /c:"^$" /c:"([0-9]* rows affected)"

Instead of searching for lines to throw away, you may be able to search for lines to keep. As long as the desired output always has at least 2 comma delimited columns, then you can simply search for any line that contains a comma.

sqlcmd .... | find ","

Upvotes: 1

Related Questions