0

How do i remove the header underline from a file generated by SQLCMD in a batch file? I am trying to automate a process that puts a file in a directory to be processed by another app and it chokes on the dashed line under the header Example:

1. Header1 Header2 header3 header4
2. ------  ------  ------  ------ 
3. Data1   Data2   Data3   Data4 
4. Data1   Data2   Data3   Data4 
5. Data1   Data2   Data3   Data4 
6. Data1   Data2   Data3   Data4

I need to remove line 2. so the other app does not choke on it. The app needs the Headers but dies on the dashed line below it.

JerryG
  • 3
  • 1
  • 3

3 Answers3

1

You could use find or findstr to filter out unwanted lines, eg.:
findstr /v /c:"------ ------ ------ ------" your_filename

and then redirect the output to file or feed/pipe it directly to your app (if it accepts input from stdin)

wmz
  • 3,645
  • 1
  • 14
  • 22
  • is this a line in the Batch file after the file is created that edits the file or is this somehow part of the SQLCMD? also the app it is going to is a vendor app and they pick up the file we place in a staging directory so i don't think feed/pipe is an option – JerryG Feb 28 '14 at 22:58
  • wmz offered a good answer. If you want to know exactly what to do with that answer you should post your code. – RGuggisberg Feb 28 '14 at 23:09
  • This may work however The file name is dynamically generated with a Date and time appended to it is there a way to give a filename with a wild card in it so i can edit all files that match the first part of the file name? – JerryG Feb 28 '14 at 23:11
  • it would accept a wildcard but it won't give the result you want. Try it to see what happens. You'd need to run it separately for each file (hint: search for `for`). Please also note it's a typical filter - it does not edit the file in place, it just reads from one stream, filters and writes out modified to another stream. Your original file is not changed in any way. – wmz Feb 28 '14 at 23:32
  • I found an answer on another site that worked. I used an -h-1 in the SQLCMD call to turn off Headers and then in the sql before the Select that built my file i put a Print 'Header1 Header2 etc.' with the HeaderX being the actual Column name and the tab between each header as the file output is Tab delimited. It worked and the file uploaded Thanks – JerryG Feb 28 '14 at 23:33
1

JerryG posted:

I found an answer on another site that worked. I used an -h-1 in the SQLCMD call to turn off Headers and then in the sql before the Select that built my file i put a Print 'Header1 Header2 etc.' with the HeaderX being the actual Column name and the tab between each header as the file output is Tab delimited. It worked and the file uploaded Thanks – JerryG

foxidrive
  • 40,353
  • 10
  • 53
  • 68
  • In other SQL implementations there are commands to turn the dashes off, like "SET UNDERLINE OFF". I'm really missing this in SQL Server. – cskwg Dec 21 '19 at 05:12
1

Using sqlcmd, you can simply add right after the sqlcmd command, the addition:

|findstr /v /c:"-"

Like:

>sqlcmd -E -S <server> -Q "set nocount on;select Header1,Header2,header3,header4 from myTable"|findstr /v /c:"-"
Shai Alon
  • 969
  • 13
  • 21