findstr is not returning what I would expect.
For context, I am looking to generate a .csv file of an SQL Server view. The .csv file should be generated in a T-SQL stored procedure and must include the view’s headers. It also has to support ANSI Latin 1 characters (æøå, specifically) and I cannot convert all columns to varchar. I have a working solution using xp_cmdshell and the bcp utility, but it’s rather elaborate and I’d prefer something simple. I looked into the sqlcmd utility and ended up with this:
sqlcmd -s";" -W -Q "SET NOCOUNT ON; SELECT * FROM [dbo].[source_table]" | findstr /v /c:"-" /b > data.csv
It creates a csv file with the proper contents, headers and removes the dashed line underneath the headers, but unfortunately doesn’t support the necessary special characters. I rewrote it to the following:
sqlcmd -s";" -u -W -Q "SET NOCOUNT ON; SELECT * FROM [dbo].[source_table]" -o temp.csv -f 1252 & findstr /v /c:"-" /b temp.csv > data.csv
Which doesn’t remove the dashed line underneath the headers. I’ve tested multiple constellations of searches after findstr and the results don’t line up with what I’m expecting. temp.csv looks something like this (simplified):
Column 1;Column 2;Column 3;Column 4
--------;--------;--------;--------
2024-07-16 09:15:16.970;;some name here;12345;
2024-07-16 09:15:16.970;;some other name;67890;
2024-07-16 09:15:16.970;;name with a, comma;13579;
2024-07-16 09:15:16.970;;stuff here;24680;
"-" and - result in an empty file, but should be a file identical to temp.csv
/c:--, /c:"--", "--", /r "--", /r /c:"--" all result in an empty file, but should return the line of dashes
/c:"-", /c:-, /r /c:-, /r /c:";" all result in a file identical to temp.csv, which makes sense
/r /c:"-;-" results in in a file identical to temp.csv, but should return the line of dashes
I also tried findstr /r "[a-zA-Z]", which returns all but the line of dashes, as expected, and findstr 2 which also returns everything but the line of dashes, but rather should return everything but the headers and the line of dashes. findstr 20 results in an empty file.
Anyone have any ideas as to what I’m missing? I’m thinking it’s an escape character or some such, but I can’t figure it out.
7
Thanks for the input in the comments! I played around with codepages without much luck. I did finally identify the issue, though:
I inadvertently left the -u flag in the sqlcmd command during previous testing, which resulted in a Unicode output file, regardless of the specified codepage. As findstr cannot search for the null bytes, odd behaviour makes sense.
I ended up with this command, which works perfectly:
sqlcmd -s";" -W -Q "SET NOCOUNT ON; SELECT * FROM [dbo].[source_table]" -o temp.csv & findstr /v /r /c:"^--*;--*" temp.csv > data.csv
It turned out that specifying the codepage is unnecessary for my use case.